non-insertion with multi-query statement
I am a user of the open-source MySQL database on a Linux platform, and have an unusual situation that I would like to share with others of the Community. I am writing code using the PHP language to interface with a MySQL database server, which utilizes the multi-query feature of MySQL. What is unusual is that sometimes with an insertion query the database table involved does not reflect the data that should have been inserted by the query. The Exception-handling code included in the PHP script does not trigger, as if nothing had happened.
Another situation that occurs is that if the strip-tags, and string-replace functions are used, and this part of the code is placed before the query statement, the problem becomes worse. Without these added functions the code works most of the time, so I know that the code itself is syntactically correct.
A generic example of the insertion query follows:
<?php
class dBase {
// -------------------------------------------------------------------------
function db_conx($database)
{
$host = 'localhost';
$user = 'user';
$password = 'password';
// $database = 'database';
$conx = mysqli_connect($host, $user, $password, $database);
// check that the connection to the database server was made
if (empty($conx))
{
die("mysqli_connect failed: ".mysqli_connect_error());
}
return $conx;
}
// -------------------------------------------------------------------------
function insert_query($title, $author, $body)
{
$database = 'database';
$conn_db = new dBase;
$conn = $conn_db -> db_conx($database);
// -------------------------------------------------------------------------
// using the mutli-query features of mysqli
// create and issue the first query
$add_query = "INSERT INTO topic VALUES ('', '$title', now(), '$author');";
// retrieve the id of the last query
$add_query .= "SELECT MAX(num) AS last_id FROM subject";
// issue queries
if (mysqli_multi_query($conn, $add_query))
{
$i = 0;
do {
// store first result set
if ($result = mysqli_store_result($conn))
{
while ($row = mysqli_fetch_row($result))
{
if ($i == 0)
{
// assign maximum id value to variable $id
$num = $row[0];
++$i;
++$num;
}
}
mysqli_free_result($result);
}
// print divider
}
while (mysqli_next_result($conn));
}
// -------------------------------------------------------------------------
// create and issue the second query
$add_message = "INSERT INTO messages VALUES ('','$num', '$body', now(), '$author')";
$post_message = $conn -> query($add_message);
// check for errors in the database updates
// close the connection to the database
$conn -> close();
return $message_added;
}
}
// -------------------------------------------------------------------------------
// In the remaining code an object is created from the dBase class and is used
// in the application script in a separate file
// Start application script
try
{
// Create the object $start_message from the 'dBase' class
$start_message = new dBase;
$message_reply = $start_message -> insert_query($title, $author, $body);
// check for errors in the database updates
if (!$message_reply)
{
throw new exceptHandler("An error occured in inserting data into the MESSAGES table.<br />message_reply value: $message_reply");
}
}
// execute throw block of code if triggered
catch(exceptHandler $e)
{
// Display custom message
echo $e -> forumPostError();
}
?>
Of course the class code displayed above is in a separate file, in the original PHP programming script, with the code at the bottom creating an object as an instance of the dBase class that will insert textual data into the database table. The only problem, as stated previously, is that on a few occasions the database table does not reflect inserted data, as if nothing had happened, and it does not trigger the try-catch block of code, and I know this Exception-handling block of code is syntactically correct as I have tested it separately. The method forumPostError is part of another class that is included in the file calling the method.