I've run into a problem related to MySQL's lack of support for transactions (bundling a group of SQL
statements together that must either succeed or fail as a single unit). The answer, of course, is to use
MySQL's LOCK TABLES function. The example provided in MySQL's documentation is as follows:
LOCK TABLES trans READ, customer WRITE;
select sum(value) from trans where customer_id=some_id;
update customer set total_value=sum_from_previous_statement where customer_id=some_id;
UNLOCK TABLES;
That works fine if you don't need to manipulate the data returned from the select statement above.
In my case, however, I need to first update a record in a table, select that same record, manipulate
the data returned from the select statement, then insert the data into another table. To add another
twist, I include a file (queryDB.inc -- see below) to execute the SQL statements.
I've tried the approach you'll see in the code below but I have a feeling that my LOCK TABLES function
isn't accomplishing anything. For one thing, adding an "UNLOCK TABLES" statement after the final insert
statement triggers an error. That suggests that "LOCK TABLES" didn't work in the first place.
In a nutshell, any insights into the use of "LOCK TABLES" would be useful.
$lock_sql = "LOCK TABLES Customers WRITE, Applications WRITE;\n";
$sql_function = "dml_statement_update";
$sql = $lock_sql . "UPDATE Customers SET $update_pairs WHERE SSN = '$SSN'";
include("includes/queryDB.inc");
$sql_function = "select";
$sql = "SELECT * FROM Customers WHERE SSN = '$SSN'";
$err_msg = "Failed to $sql in " . FILE . " at line " . LINE . ".";
include("includes/queryDB.inc");
$row = MYSQL_FETCH_ARRAY($result);
$num_fields = MYSQL_NUM_FIELDS($result);
$i = 0;
while ($i < $num_fields)
{
$val = $row[$i];
if (MYSQL_FIELD_NAME ($result, $i) == "RowID") $val = "";
$val = addslashes($val);
$temp_table_vals .= "'$val', ";
$i++;
}
$temp_table_vals = ereg_replace(", $", "", $temp_table_vals);
MYSQL_FREE_RESULT($result);
$sql_function = "dml_statement_insert";
$sql = "INSERT INTO Applications VALUES ($temp_table_vals)";
$err_msg = "Failed to $sql in " . FILE . " at line " . LINE . ".";
include("includes/queryDB.inc");
CONTENTS OF queryDB.inc
<?php
$test_db = MYSQL_PCONNECT($host, $dB_username);
if (!$test_db)
{
$fatal_error = "yes";
$err_msg = "The database is temporarily unavailable.<p>Please try connecting again.";
include("includes/errorMsg.inc");
}
if ($sql_function == "select")
{
if (!($result = MYSQL_DB_QUERY($database, $sql)) || MYSQL_NUM_ROWS($result) < 1)
{
$fatal_error = "yes";
include("includes/errorMsg.inc");
}
}
elseif (ereg("select", $sql_function) && $sql_function != "select")
{
$result = @MYSQL_DB_QUERY($database, $sql);
}
elseif (ereg("dml_statement", $sql_function))
{
MYSQL_DB_QUERY($database, $sql);
if (MYSQL_AFFECTED_ROWS() < 1 && $sql_function != "dml_statement_update")
{
$fatal_error = "yes";
include("includes/errorMsg.inc");
}
}
?>
