PHP + MySQL + Queries that depend on each other?

[=?iso-8859-1?B?Sm/jbyBNb3JhaXM=?=]

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.

[Jerry Stuckle]

João Morais wrote:

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?

Never assume a MySQL call (or a call to any other external resource) is
successful. And you shouldn't display the error message itself - it
exposes internals of your site to the user. Rather, give them a generic
message and log the real message.

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?

Use Innodb and transactions.

$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.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Gordon Burditt]

>

        
                >$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?

It is always possible for a MySQL query to fail due to network
errors (even if the DB is on the local host), disk errors, permission
problems, etc. Also, it's not only SYNTAX that can cause errors
in a sql query. If the table no longer matches the query (table
doesn't exist, field doesn't exist, etc.) you'll get an error.

Note that returning 0 rows in response to a select (or affecting
0 rows for a delete) is *NOT* an error.

>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?

You can't. It is always possible for someone to unplug a network cable
or take down the server between queries. But you can use transactions
to be sure that all or none of the queries are committed. Check your
queries and use ROLLBACK if something fails.

>

$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.