Query not inserting data with no errors

Question

I was told my query was wrong, but I am not receiving any errors. Nothing is being inserted into my database, so I do not question it is wrong, though I do not see what is wrong in the syntax.

$stmt2 = $con->prepare("INSERT INTO user_players (player1, player2, player3, player4, player5, player6, player7, player8, player9, player10, player11, player12, player13) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) WHERE (user_id=?)");

Does anyone see anything I am missing?

UPDATE to show more code to make more sense of this..

<form action="" method="POST">
<?php
$con = mysqli_connect("localhost", "", "", "");
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
$stmt = mysqli_query($con,"SELECT * FROM user_players");

if(isset($_POST['Add Player'])){
//Variables to post in the bind_param
$insert_user_id = $_POST['user_id'];
$insert_player1 = $_POST['player1'];
$insert_player = $_POST['player2'];
$insert_player = $_POST['player3'];
$insert_player = $_POST['player4'];
$insert_player = $_POST['player5'];
$insert_player = $_POST['player6'];
$insert_player = $_POST['player7'];
$insert_player = $_POST['player8'];
$insert_player = $_POST['player9'];
$insert_player = $_POST['player10'];
$insert_player = $_POST['player11'];
$insert_player = $_POST['player12'];
$insert_player = $_POST['player13'];
$stmt2 = $con->prepare("INSERT INTO user_players (player1, player2, player3, player4, player5, player6, 
player7, player8, player9, player10, player11, player12, player13) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) WHERE (user_id=?)");
if ( false===$stmt2 ) {
    die('Player Insert prepare() failed: ' . htmlspecialchars($con->error));
}
$stmt2->bind_param('isssssssssssss', $insert_user_id, $insert_player1, $insert_player2, $insert_player3, $insert_player4, $insert_player5, $insert_player6, 
$insert_player7, $insert_player8, $insert_player9, $insert_player10, $insert_player11, $insert_player12, $insert_player13);
if ( false===$stmt2 ) {
    die('Player Insert bind_param() failed: ' . htmlspecialchars($stmt2->error));
}
$stmt2->execute();
if ( false===$stmt2 ) {
    die('Player Insert execute() failed: ' . htmlspecialchars($stmt2->error));
}
}
while($row = mysqli_fetch_array($stmt)) {
    $player1 = $row['player1'];
    $player2 = $row['player2'];
    $player3 = $row['player3'];
    $player4 = $row['player4'];
    $player5 = $row['player5'];
    $player6 = $row['player6'];
    $player7 = $row['player7'];
    $player8 = $row['player8'];
    $player9 = $row['player9'];
    $player10 = $row['player10'];
    $player11 = $row['player11'];
    $player12 = $row['player12'];
    $player13 = $row['player13'];
?>
    <div class="draftResultsWrap">
        <div class="inline">
        <?php echo "<div>" . $row['firstname'] . " " . $row['lastname'] . "</div>"; ?>
        </div>
            <input class="draftBorder" value='<?php echo $player1; ?>'/>
            <input class="draftBorder" value='<?php echo $player2; ?>'/>
            <input class="draftBorder" value='<?php echo $player3; ?>'/>
            <input class="draftBorder" value='<?php echo $player4; ?>'/>
            <input class="draftBorder" value='<?php echo $player5; ?>'/>
            <input class="draftBorder" value='<?php echo $player6; ?>'/>
            <input class="draftBorder" value='<?php echo $player7; ?>'/>
            <input class="draftBorder" value='<?php echo $player8; ?>'/>
            <input class="draftBorder" value='<?php echo $player9; ?>'/>
            <input class="draftBorder" value='<?php echo $player10; ?>'/>
            <input class="draftBorder" value='<?php echo $player11; ?>'/>
            <input class="draftBorder" value='<?php echo $player12; ?>'/>
            <input class="draftBorder" value='<?php echo $player13; ?>'/>
        </div>

We need to see the values and user id. as a blind answer, I'd suspect that the values are strings and are not surrounded by single quotes (they need to be) — devlin carnate
– devlin carnate, Commented Aug 25, 2015 at 20:47
Regardless of any design issues, it looks like one big problem is that you're setting $insert_player repeatedly from your $_POST values instead of $insert_player2, $insert_player3, etc. — Don't Panic
– Don't Panic, Commented Aug 25, 2015 at 21:47
You are overiding the variable $insert_player many times, there are no variable from $insert_player2 to $insert_player13 causing the problem. — Amit
– Amit, Commented Aug 26, 2015 at 4:50

Community · Accepted Answer · 2017-05-23 12:29:43Z

1

using a where == AnyKey on an insert, that it could be your issue.

Being that I am not sure if you are trying to do an update or insert I am going to say go with the UPSERT, see this SO (stackoverflow) Q/A

I think......without knowing the values of ?,?,?,?,?,?,?,?,? can't say there is nothing wrong with the values but i really think it's the where clause that is stopping the insert.

The User ID should be in the insert not in the where

should be something like this

insert(user_id,...,...) values(@user_id,...,...,...)

then the user id (FK) you need will be in the table so you can later

select * from users join user_stuff on user_id

perhaps you are trying to update the values, in that case you should have an update statement with the where clause

also noteworthy, you should consider normalising you DB structure, not player1,2,3,4,5

table: UserPlayers [ Id: UserId: Player ]

this will save you so much trouble in the future! i promise

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Aug 25, 2015 at 20:47

workabyte

3,8152 gold badges33 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Becky Over a year ago

Sorry for my ignorance, but what do you mean PK?

workabyte Over a year ago

@Becky, I meant primary key but realised it was not the PK but a FK (foreign key) but either way you shouldn't have that in the where clause. should just have it as one of the values that is inserted in most cases i dont think there should be a WHERE as part of an ISERT

Becky Over a year ago

Okay, makes more sense now. Yes, it is a FK. I updated my question to show where this is coming from to make more sense.

workabyte Over a year ago

@Becky, my answer still stands. Either you want to do an update not an insert or your insert should NOT have a WHERE or you should look into an upsert

Becky Over a year ago

I will look into normalizing, but for right I have no idea how to do it. I will be inserting data (string, values) for the first time and then continually updating each user's player until it gets to player 13

|

Steve Chambers · Accepted Answer · 2015-08-25 21:07:59Z

0

Please try changing the prepared statement to this:

INSERT INTO user_players (user_id, player1, player2, player3, player4, player5, player6, player7, player8, player9, player10, player11, player12, player13) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

edited Aug 25, 2015 at 21:07

answered Aug 25, 2015 at 20:51

Steve Chambers

39.8k29 gold badges178 silver badges222 bronze badges

7 Comments

workabyte Over a year ago

user id is a FK not a PK best i can tell

Steve Chambers Over a year ago

In that case you will need to specify its value for the record you wish to reference. (Might consider using the INSERT INTO...SELECT syntax if you need to find the ID by searching for another unique value in another field in the referenced table.)

Becky Over a year ago

I updated my question to show where this is coming from to make more sense.

Steve Chambers Over a year ago

Thanks, have edited my answer accordingly - please let me know if it works.

Becky Over a year ago

Still nothing inserts into my db. I have the players listed on my page as column headers basically and then inputs for the different players, so each user has 13 inputs under their name.

|

Collectives™ on Stack Overflow

Query not inserting data with no errors

2 Answers 2

6 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related