Re: [PHP] MySQL resource From: DL Neil (PHPml <email protected>)
Date: 10/15/01

> Toke Herkild:
> If:
> mysql_query($Result) returns either '1' or 'Resource #ID?' and as far as
> I've found oout if it does not find data at all it returns '2' then the
> correct if clause would be :
> if ((mysql_query()>0) and (mysql_query() != 2)){ do stuff }
>
> correct my if I'm wrong...

Toke, May I have (another) turn at this one? (please accept a cut-and-paste from teaching notes -
sorry, no diagrams)

The MySQL query function can (broadly speaking) be used in one of two 'modes': to push data into a
db or to pull it out. Borrowing David Otton's notation: mysql_query("INSERT ....") and
mysql_query("SELECT ...."). In PHP:

$result = mysql_query( $query, $dbLinkId );

Once the query is run, you want to check if the data was successfully 'written' in the first mode;
but will also need to view the extracted data in the second. Big difference! Accordingly with
PHP/MySQL there is NO one 'rule' for both situations (and this is the trap in our thinking that many
of us walk smack into). The return 'value' from the query function will vary according to the 'mode'
of use.

Let's start with the logical/boolean stuff - did the query work or not:-

Referring to the manual (http://www.php.net/manual/en/function.mysql-query.php) we read:
<<mysql_query() returns TRUE (non-zero) or FALSE to indicate whether or not the query succeeded. A
return value of TRUE means that the query was legal and could be executed by the server. It does not
indicate anything about the number of rows affected or returned. It is perfectly possible for a
query to succeed but affect no rows or return no rows.>> It then goes on to give two examples of
queries that will return FALSE on the grounds of syntax or semantic error. Perhaps the most
important 'other' reason why a query might fail at this point is the matter of
privileges/permissions.

We're error checking, so I find it helpful to be a pessimist (one who expects the worst) - I ignore
the 'TRUE' bit totally (***) and concentrate on the negative:

if ( FALSE == $result ) { ...something went wrong, better deal with it... }
or even if ( FALSE === $result ) - yes I can be more than pedantic!

Alternatively on the query statement make use of the ternary operator:

$result = mysql_query( ...etc... ) or die( ... ) ;

(***) Just quickly: why do I ignore 'TRUE'? It is after all, something of an abuse of the definition
of logical typing. As the manual hints: "returns TRUE (non-zero)" - emphasis on the parenthesised
words. ie if ( TRUE === $result ) will ALWAYS fail - in this case the opposite of FALSE is not
necessarily TRUE!

At this stage, assuming the answer was not FALSE, we know that MySQL was happy with us, the
interface between PHP and MySQL worked, and the SQL query was understandable (if only to MySQL). Now
it's worth taking a look at what actually happened:

In the 'first mode' we want to store some data in the database. Because the PHP script has spent
some effort prior to this moment assembling the 'parcel' of data being INSERTed, UPDATEed, REPLACEd,
or DELETEd (think of the latter as a 'minus-addition' for the sake of my story!), you should have a
fair idea of how many tuples/records/rows of data are in the 'parcel'. Accordingly the check is:

$CheckCount = mysql_affected_rows( $dbLinkId );

Thus if you intended to INSERT five new rows into the db, $CheckCount had better be five!
NB There are a few 'tricks and traps' on this when UPDATE-ing etc, but those are discussed on the
other/appropriate manual page!

Another little 'hang up' that catches some, is that the query may have worked - and therefore not
have returned FALSE (above), and yet the number of rows affected could still be zero, eg attempting
to UPDATE rows that do not exist in the db. There is a difference between being semantically correct
(according to MySQL) and what happens when the command is executed against the live dataset!

To be sure, one of the 'tricks' is that if there was something wrong with the earlier query
statement, $CheckCount will be set to -1, and so some might argue that this renders the TRUE/FALSE
check (first stage, above) slightly unnecessary (if only in certain situations!). I disagree (but
then we've already established that I can be pedantic!) particularly if the query statement has been
assembled dynamically within the PHP script - and 'in spades' if the script assembles it in part
from user data. There's always room for error, even for super-coders like us...

Notice that I haven't mentioned $result here!? This is because it is 'officially' irrelevant (for
reasons discussed in a moment). However if you take a look at it, it seems to be an integer value -
and thus the observations you made in your most recent post. [I've never looked, but is it an
abbreviated form of the "Resource..." format, or is it the answer to the affected_rows question, or
something else?]

A final warning: MySQL gives you this information as a 'one time only offer'. It must be
inspected/used BEFORE any other operation is conducted against that database (because it uses the db
link)!

Now let's get excited! In the 'second mode' we're retrieving data from the db. Accordingly the
return 'value' from mysql_query() must somehow contain far more information than 'did it work?' and
'how many rows were affected?' - we want all of that AND the retrieved data itself (recordset). OK,
at this point we know that the syntax and meaning of the query were 'understood' by MySQL. So how
many rows are in this kind of recordset?

$RowCount = mysql_num_rows( $result )

NB mysql_num_rows looks at the recordset ($result) whereas mysql_affected_rows looked at the db
link!
Unfortunately the manual refers to both of our $result and $dbLinkId using the terminology of "link"
or "resource link" which is another potential point of confusion on these topics (although the
manual's terminology is quite correct).

The PHP script will now go on to use $RowCount to control a loop that manages the processing of each
tuple/record/row in the resultset/recordset, through the data retrieval functions, eg
mysql_fetch_assoc, mysql_fetch_array, etc. The various fetch functions all require the
resultset/recordset ($result) as an argument. This is NOT because it contains the data, as such. It
is but a 'label' for the data. It is used to locate the data, and the fetch functions maintain
internal pointers to keep track of how many of the records/fields have been processed thus far, and
whether there are any more left (eof/end of file). These 'internal pointers' are analogous to those
used in array processing.

NB each of the fetch functions refers to $result to find the next (if any) row value(s) - not the db
link! This is logical, because if you think about it, you could have multiple mysql_query("SELECT
....")-s, on the go, at any one time. Indeed the second may be a query on a result from the first
(sub-query structure), eg an order record retrieved from the ORDERS table likely points to a
particular CUSTOMER table record.

Again referring back to your recent post, in this 'mode' the $result field's literal contents are
"Resource #IDn", and as such of little use to us, but very much required by the fetch functions as a
'label' for the recordset.

Hope it helps (sorry if some of it is more introductory than you rate),
(invitation: I'm always keen to get feedback on my teaching notes!)
=dn 

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: php-general-unsubscribe <email protected>
For additional commands, e-mail: php-general-help <email protected>
To contact the list administrators, e-mail: php-list-admin <email protected>