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, Aug 25 '15 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, Aug 25 '15 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, Aug 26 '15 at 04:50

score 1 · Answer 1 · edited May 23 '17 at 12:29

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 '17 at 12:29

Community

1
1

answered Aug 25 '15 at 20:47

workabyte

3,496
2
27
35

Sorry for my ignorance, but what do you mean PK? – Becky Aug 25 '15 at 20:49
@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 – workabyte Aug 25 '15 at 20:50
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. – Becky Aug 25 '15 at 20:57
@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 – workabyte Aug 25 '15 at 20:59
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 – Becky Aug 25 '15 at 21:00
@Becky, see my link for UPSERT (update / insert) also please mark this answered if this helps – workabyte Aug 25 '15 at 21:06

Steve Chambers · Answer 2 · 2015-08-25T21:07:59.783

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 '15 at 21:07

answered Aug 25 '15 at 20:51

Steve Chambers

37,270
24
156
208

user id is a FK not a PK best i can tell – workabyte Aug 25 '15 at 20:52
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.) – Steve Chambers Aug 25 '15 at 20:53
I updated my question to show where this is coming from to make more sense. – Becky Aug 25 '15 at 20:57
Thanks, have edited my answer accordingly - please let me know if it works. – Steve Chambers Aug 25 '15 at 21:00
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. – Becky Aug 25 '15 at 21:05
Just noticed you are binding `user_id` first so have moved it to the start. Please try that (and if it doesn't work, please post the error message that is reported). – Steve Chambers Aug 25 '15 at 21:09
Hmmm. Are you able to verify that the insert is being hit? Perhaps just add `var_dump($stmt2)`; immediately before `$stmt2->execute();`. – Steve Chambers Aug 27 '15 at 13:22

Query not inserting data with no errors

2 Answers2