0

I've just come across this problem when making multiple insert queries using prepared statements.

I have a loop which runs through 102 iterations and each time it triggers an insert query:

$sql = "INSERT INTO xxx SET town=?, city=?, ...";
$stmt = $conn->prepare($sql);

while(... <= 102)
{
    $stmt->bind_param("sss", $town, $city, ...);
    $stmt->execute();
}

No errors are picked up during execution but only 98 entries are recorded in the database. I have run this several times and always 98 entries are inserted. 4 are missing somewhere.

If I replace the prepared statement with a standard query, leaving everything else the same, i.e.

while(... <= 102)
{
    $sql = "INSERT INTO xxx SET town='$town', city='$city', ...";
    mysqli_query($conn, $sql)
}

...then it works fine and all 102 items are inserted into the database.

Since the prepared statements queries do not report any errors, I have no idea why it is missing 4 queries.

How could I go about investigating this?

Since the data is from a trusted source I can just use mysqli_query but I'd rather know what's going on in case I come across this in the future.

Jason
  • 29
  • 1
  • 4
  • Have you checked your error logs? – Jay Blanchard Apr 13 '17 at 12:54
  • prepared statement is as good with reporting errors as a regular query – Your Common Sense Apr 13 '17 at 12:59
  • I would also try wrapping the code in a `try / catch`, see if there's an error. And, see what you get when outputting `$stmt->affected_rows` – Just Rudy Apr 13 '17 at 13:01
  • Add `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to the top of your script. This will force any `mysqli_` errors to generate an Exception that you can see on the browser and other errors will also be visible on your browser . – RiggsFolly Apr 13 '17 at 13:01
  • Yes, that did the trick. It threw an exception when trying to insert null data into a not null field. Via regular mysqli it still does the insert, but via prepared statements it does not, hence the missing data. – Jason Apr 13 '17 at 13:39

0 Answers0