0

I am experiencing an issue where my PHP code for inserting into a database table is not reflecting changes in the table. I have looked through existing posts and have not found a solution. The previous posts with the same issue usually had glaring issues in the code.

$stmt->execute() is returning True. I am committing the transaction as well as closing the statement.

This is happening on MariaDB, version 5.5.5.

The table is simple and only contains 2 columns:

CREATE TABLE gallery (image_type varchar(255), url varchar(800));

My code is:

$conn = new mysqli($servername, $username, $password, $dbname);

$type = 'foo';
$url = 'bar';

$stmt = $conn->prepare("INSERT INTO gallery (image_type, url) VALUES (?, ?)");
$stmt->bind_param('ss', $type, $url);

if ($stmt->execute()) {
    $conn->commit();
    $stmt->close();
    echo "Success!";
} else {
   echo "Failure!";
}

If I connect to the database in command line and run an insert statement it works fine, so I know this is an code issue.

Dharman
  • 30,962
  • 25
  • 85
  • 135
SW Williams
  • 559
  • 1
  • 5
  • 18
  • Do you have MySQLi exceptions switched on? – Dharman Oct 08 '19 at 08:12
  • Just added `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to my code. Tried again - its not throwing any exceptions. – SW Williams Oct 08 '19 at 08:18
  • What does $stmt->affected_rows() return after execute? – Georg Richter Oct 08 '19 at 08:47
  • @GeorgRichter so `$stmt->affected_rows()` didn't exist but I tried `$conn->affected_rows` and now all of a sudden it is working lol. – SW Williams Oct 08 '19 at 09:06
  • 1
    I guess its some issue with the server. If I run `$conn->affected_rows` after an insert statement it works. If I don't it does not. – SW Williams Oct 08 '19 at 09:08
  • 1
    How do you know that "Insert statement not updating database"? – Your Common Sense Oct 08 '19 at 09:29
  • 5.5.5 is not a real version, it's a compatibility prefix. You must have some 10.x server, is it 10.4? There have been several reports about mysteriously missing data after INSERTs, but none has been reproduced so far. If you have any useful information, please add a comment to https://jira.mariadb.org/browse/MDEV-20354 or https://jira.mariadb.org/browse/MDEV-20683 . – elenst Oct 08 '19 at 12:13
  • As elenst pointed out, the reason could be one of the two bugs in MariaDB's bug tracking system. `affected_rows()` doesn't do any interaction with server, it just retrieves the value which was returned in the OK packet of last `execute()` call. – Georg Richter Oct 08 '19 at 16:31

0 Answers0