[SOLVED] PHP + MySQL + Queries that depend on each other?

[Sld]

Hi there guys,

 

My doubt is related to PHP and MySQL usage, and it's related to check if performed queries are performed with success, since we know that:

 

mysql_query() - Return Values:

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

 

Each time we perform an sql query, we can check if it was performed with success like this:

 

$result = mysql_query('sql query string');
if (!$result) echo mysql_error();

 

If it fails, it will display the succeeded error.

 

Now that I gave an example, here are my doubts:

 

1. Should this procedure be done every time a query is done, or, should we assume that 'SELECT' queries are always done with success if sql syntax is correct?

 

2. If we have a lot of queries that depend on each other (See the next example), imagine that query1 and query2 are made with success, but query3 fails, I will have inconsistent data since, the update is made on query2, but no delete will be made on query3 since it fails.

How can we avoid this?

 

$result1 = mysql_query('SELECT ...');
if (!$result1) {
     echo mysql_error();
     exit(0);
}

$result2 = mysql_query('UPDATE ... with data supplied from $result1');
if (!$result2) {
     echo mysql_error();
     exit(0);
}

$result3 = mysql_query('INSERT / UPDATE / DELETE ... with data supplied from $result1');
if (!$result3) {
     echo mysql_error();
     exit(0);
}

 

Hope you guys can understand my examples, if not, I'll try to explain in a better way.

 

Thanks for your attention and spent time on reading this.

 

[MatMel]

1. Checking on errors is never wrong I suppose

2. That is indeed a problem and I don't think there is an "easy" way to avoid this.

It isn't as tragic if you did a SELECT, SHOW, ... in the first querys because that won't change anything in your database.

If the querys are INSERTS you can save the inserted ids (http://ca.php.net/manual/en/function.mysql-insert-id.php ) into an array and delete all the entries in case of an error.

The biggest problems are DELETES and UPDATES, because you will have to save all values you are changing or deleting, so that you are able to restore the data.

You could SELECT the affected rows before you change or delete them and keep the results. Then you would have to write a function with the saved results as params that restores the values.

The only serious problem (apart from the expenditure of time ) is to keep the old id. Perhaps you would have to manipulate the auto_increment values (but thats not really nice coding...but I can't think of another way)

[Sld]

Been advised to use Innodb and transactions.

 

Think it will solve my problem, thanks for your answer anyway