How to execute all queries in full or none at all

[hadoob024]

Like in my PHP script suppose I have to perform a SELECT, then an INSERT, then a DELETE, then maybe another SELECT or DELETE. Now suppose this script somehow fails on the DELETE or INSERT or the last SELECT. How do I get it to disregard the other queries unless everything goes through? Do I need to use something like transactions, commits, and rollbacks?

[bradgrafelman]

Sounds good. I'm sure you've already visited the MySQL manual page for the syntax (link)... so have you tried this yet?

[hadoob024]

I'm in the middle of the reading and putting the code together right now. I just wanted to check that I was looking in the right direction first. I'll post my code when I'm done. Thanks!

[hadoob024]

OK. I can't test it out right now cuz I'm getting my web hosting company to upgrade the version of MySQL that's available, but here's what I have. Does it look right?

$connresult = @mysql_connect("myDatabase","myUser","myPassword");
if (!$connresult)
{
	$problemtext = 'Error opening a db connection.\r\n'.mysql_error($connresult);
	trigger_error($problemtext, E_USER_ERROR);
}

$db_selected = @mysql_select_db('ownersfinancingrealestate_com_-_listingsdb', $connresult);
if (!db_selected)
{
	$problemtext = 'Error selecting MySQL db.\r\n'.mysql_error($connresult);
	trigger_error($problemtext, E_USER_ERROR);
}

//For MySQL 4.0.11 and higher
START TRANSACTION;

//For MySQL 3.23.19 
BEGIN WORK;

$insert_query = "INSERT INTO `ofrearchive`
     SELECT * FROM `ofrelistings` LEFT JOIN `ofrearchive` USING(uniqueid)
     WHERE `ofrearchive.uniqueid` IS NULL AND DATEDIFF(`ofrelistings.dateadded`, now())>30";
$insert_result = mysql_query($insert_query);
if (!insert_result)
{
	ROLLBACK;
	$problemtext = 'Error INSERT-ing information into archive table.\r\n'.mysql_error($connresult);
	trigger_error($problemtext, E_USER_ERROR);
}

$delete_query = "DELETE `ofrelistings.*` FROM `ofrelistings` INNER JOIN `ofrearchive` USING(uniqueid)";
$delete_result = mysql_query($delete_query);
if (!$delete_result)
{
	ROLLBACK;
	$problemtext = 'Error DELETE-ing information from main table.\r\n'.mysql_error($connresult);
	trigger_error($problemtext, E_USER_ERROR);
}

$select_query = "SELECT * FROM `ofrelistings` INNER JOIN `ofrearchive` USING(uniqueid)";
$select_result = mysql_query($select_query);
if (!select_result)
{
        echo 'Backup worked successfully';
}
else
{
	ROLLBACK;
	$problemtext = 'Error completing the backing up of information from main table to archive table.\r\n'.mysql_error($connresult);
	trigger_error($problemtext, E_USER_ERROR);
}

$searchstring = "SELECT * FROM `ofrelistings` WHERE blahblahblahblah";
$searchresults = mysql_query($searchstring);
if (!$searchresults)
{
	ROLLBACK;
	$problemtext = 'Error searching OFRE listings db.\r\n'.mysql_error($connresult);
	trigger_error($problemtext, E_USER_ERROR);
}


//If no errors occurred, then commit transactions
COMMIT;


$numrows = mysql_num_rows($searchresults);
if ($numrows == 0)
{
	echo "No results matched your search criteria";
	require('/home/virtual/ofre.com/var/www/includes/copyright.php');
	require('/home/virtual/ofre.com/var/www/includes/closetables.php');
	exit;
}


//Retrieve, check, and print results
while ($row = mysql_fetch_assoc($searchresults))
{
	//echo out results in a table including handling pics
}


//free up result memory
mysql_free_result($searchresults);

[bradgrafelman]

No... you can't simply put the SQL commands in PHP and expect PHP to know what to do with it...

//For MySQL 4.0.11 and higher 
START TRANSACTION; 

//For MySQL 3.23.19 
BEGIN WORK; 

The above should be sent along with your query. Same with ROLLBACK, etc.

[hadoob024]

oh ok. so something more like:

$start_transaction = "START TRANSACTION";
$start_transaction_result = mysql_query($start_transaction);
if (!$start_transaction_result)
{
    $problemtext = 'Error Starting MySQL Transaction.\r\n'.mysql_error($connresult);
    trigger_error($problemtext, E_USER_ERROR); 
}

and:

$rollback_transaction = "ROLLBACK";
$rollback_result = mysql_query($rollback_transaction);
if (!$rollback_result)
{
    $problemtext = 'Error with Rollback.\r\n'.mysql_error($connresult);
    trigger_error($problemtext, E_USER_ERROR); 
}

and also:

$commit_transaction = "COMMIT";
$commit_result = mysql_query($commit_transaction);
if (!$commit_result)
{
    $problemtext = 'Error with Commit.\r\n'.mysql_error($connresult);
    trigger_error($problemtext, E_USER_ERROR); 
}

Is that more like it?

[hadoob024]

Here's a follow-up. Do I also need to use LOCK TABLES and UNLOCK TABLES?