PHP Prepared Statement not inserting, no errors appearing

Question

I am attempting to insert some data via prepared statements in PHP. I have the following code which is not inserting for me. I also have the following code set already

ini_set('display_errors', 1);

error_reporting(~0);

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $sqlInsert = "INSERT INTO deck_cards (deckid, cardid, qty) VALUES (?,?,?)";
    $stmtInsert = $conn->prepare($sqlInsert);
    
    if ($stmtInsert)
    {
        $stmtInsert->bind_param("sss", $deckid, $cardid, $cardcount) or trigger_error($stmtInsert->error, E_USER_ERROR);
        $stmtInsert->execute() or trigger_error($stmtInsert->error, E_USER_ERROR);
        echo "Check if this ran";
    }
    else {
        echo "Error: " . $sql . "<br>" . $conn->error;
        $conn->close();
    }

    $stmtInsert->close();
}
catch (Exception $ex)
{
    echo 'Exception occurred '.$ex->getTraceAsString();
}

When this executes, I do see the text "Check if this ran" but no other messages. The data is not inserted into the database. Additionally, I echo'd the SQL string and variables and ran it against the MySQL directly and it inserted OK. Not sure what is happening in my very straightforward code above.

Additionally, inserts are happening OK in other pages within my app, but this page for some reason isn't working.

I know it's overkill on my error reporting, but I threw everything I could think of to see if there is an error somewhere, but this one is a head scratcher for me. Thanks for any assistance!

Are you sure all three parameters are strings? The variable names look more like "iii", because you have two IDs and a counter. What's the datatype of your DB fields? — Aranxo, Jan 11 '23 at 16:11
This overkill error handling might actually be hiding the proper error from you. You should remove all of it and only keep 3 lines: prepare, bind and execute. — Dharman, Jan 11 '23 at 16:15
@Aranxo All are ints, in the example I am trying to run its 2, 56, 4 — Wil, Jan 11 '23 at 16:16
There are no errors in your code. It looks correct, aside from all this extraneous error checking. — Dharman, Jan 11 '23 at 16:16
@Dharman, ya that's what I figured too, I've been banging my head on this all morning, seems straightforward, maybe I will try without prepared statement as a test and see what happens — Wil, Jan 11 '23 at 16:18
No, the prepared statement is not an issue. You must use PS and you are doing it correctly. If there's an error, it's not in this piece of code. — Dharman, Jan 11 '23 at 16:18
So why don't you try `$stmtInsert->bind_param("iii", $deckid, $cardid, $cardcount)`? — Aranxo, Jan 11 '23 at 16:25
@Aranxo All strings can be ints. It's highly unlikely that sending ints as string would cause any issue. Sending data as strings is the safest option. — Dharman, Jan 11 '23 at 16:30
I just tried this, it echo'd the success message, nothing saved, I copied the string and pasted into phpmyadmin and it executed ok....SIGH $sql = "INSERT INTO deck_cards (deckid, cardid, qty) VALUES (" . $deckid . "," . $cardid . "," . $cardcount . ")"; if ($conn->query($sql) === TRUE) { echo "New record created successfully"; } else { echo "Error: " . $sql . "
" . $conn->error; } — Wil, Jan 11 '23 at 16:31
I also added echo $stmtInsert->affected_rows; It is showing 1, but nothing in DB argh — Wil, Jan 11 '23 at 16:40
I just wanted to give the hint $stmtInsert->affected_rows, but you already did it. Is there an option, that prevents prepared statements in the DB? Is there some caching mechanism, so that the new record is hanging in the cache? Just brainstorming... — Aranxo, Jan 11 '23 at 16:47
It's more likely that your PHP is connected to a different mysql/mariadb instance than your phpmyadmin. This is the case more often than not that I've seen in questions where the PHP code appears to work, but nothing is visible in the admin tool. Have you tried writing some code in PHP to SELECT from that table immediately afterwards, and fetch all the rows and display them? Does that produce the data you're expecting? — ADyson, Jan 11 '23 at 16:51

score 1 · Answer 1 · edited Aug 21 '23 at 21:30

In case your query seemingly doesn't work, it can be caused by following reasons:

The SQL didn't run at all due to incorrect program logic. Add temporary debugging output to make sure that the code reached the point where the query executes (it should be added right before the query execution, not after).
There was an error during query execution. Check these answers for the detailed explanation on how to configure proper error reporting for mysqli or PDO.

In case the query was actually executed, and there was no errors, it means that query was successful. It you still cannot observe the desired result:

For a SELECT query that just doesn't return expected values, it's definitely input data doesn't match that in the database. Here are some recommendations on how to debug it
in case it's INSERT (or other data modification query), then it's possible that the result is viewed in the wrong database. To get a proof, print out the result of insert_id/affected_rows and/or result of mysqli_info(). You can also select the inserted data and print it out right here, immediately below the INSERT. Then double check database credentials for the program which you are using to see the results.
There is an open transaction or autocommit set to 0 somewhere in the code, but no explicit commit. Check your code and either remove begin or add commit.

PHP Prepared Statement not inserting, no errors appearing

1 Answers1