Re: PHP + MySQL + Queries that depend on each other?

[code=php]
$result = mysql_query('sql query string');
if (!$result) echo mysql_error();
[/code]

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.

[code=php]
$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);
}
[/code]

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.



.

Relevant Pages

  • Re: PDO: Debugging prepared statements
    ... Being able to echo back SQL statements as they are being ... to eliminate redundant queries and the like. ... Prepared Statements don't work the way doing queries the traditional ... I can't echo the query being ...
    (comp.lang.php)
  • Re: Decimal fieldss precision is too small to accept the numeric you attempted to add
    ... Your test query, as is, works fine. ... Can't understand why the same expression does not work in MY query. ... Tried putting the Cdbl) around both of those, still fails. ... If they are queries, you may need to perform the typecasting in the lower queries. ...
    (microsoft.public.access.queries)
  • Re: Can someone explain the behavior of this query?
    ... My first "guess" is this function sometimes fails: ... This query contains LOTS of Right ... It uses ODBC pass through queries because ... BTW -- I created this query slowly, adding one subquery at a time. ...
    (microsoft.public.access.queries)
  • Re: Max SQL length for Passthrough Query
    ... So far, I have found a query length of 76,944 fails, but length 52,537 ... The help file says that the maximum number of characters in a SQL statement is approximately 64,000. ... It doesn't mention any difference between pass through queries and other queries, so in the absence of any specific documentation I'm assuming that this also applies to pass through queries. ...
    (microsoft.public.access.queries)
  • Error: Not enough space on temoporary disk
    ... I have two Access 97 queries which have been running ... Still get the error message! ... Strange thing is Query A runs ok on my PC but query B fails with the ...
    (microsoft.public.access.queries)