Retrieving Mysql Results

aspekt9

For some reason if I'm trying to retrieve information from a column I have to type it like this:

$activatechk = mysql_query("SELECT activated FROM users WHERE username='%s'",mysql_real_escape_string($user));
$activresults = mysql_result($activatechk,0,"activated");

With my mysql_result or else I get something like Reference Id #6, is this normal to have to have mysql_result in there? Why does it return Reference ID #6 if I don't have mysql_result?

greenie2600

I'm not sure exactly what you're asking. What do you mean by "if [you] don't have mysql_result"? Can you post the alternative code that is exhibiting the unexpected behavior?

The return value of mysql_query() is a variable of the type resource (not "reference"). It sounds like you're inadvertently assigning the value of $activatechk to $activresults.

Incidentally, mysql_result() is pretty inefficient, as noted in the manual—for retrieving a single field from a single record, as you're doing in this example, it's probably fine, but you're better off using mysql_fetch_array(), mysql_fetch_assoc(), or mysql_fetch_row() when dealing with any significant amount of data.

aspekt9

If I just use:

$activatechk = mysql_query("SELECT activated FROM users WHERE username='%s'",mysql_real_escape_string($user));

It returns a Resource Id #, it only returns the value that is stored in the column 'activated'. Whereas if I use :

$activresults = mysql_result($activatechk,0,"activated");

I get the value stored in that column for that username.
My question is why is it that I have to do this to retrieve the value I want and not a Resource Id?

Piranha

I can't tell you WHY it is as it is. But how would you want it to be returned otherwise? Remember that a query can return 1 or more columns and 0 or more rows. And remember that it can return several different datatypes (one per column). It may help you to understand why it is like it is if you think about these implications.

By the way, you don't need more than the result in mysql_result:

$activresults = mysql_result($activatechk);

greenie2600

mysql_query() returns a result set, which is basically a list of matching records. The result set could contain any number of records—fifty, fourteen thousand, one, or even none. You can't operate directly on a result set—only on the individual records contained therein.

To look at individual records, you need to pull them from the result set—even if your query only returns one record. That's what mysql_result() and mysql_fetch_*() do—you feed them the result set, and they spit out the first record from that result set. (If your result contains multiple rows, repeated calls to these functions will return the second, third, and fourth result set, and so on.)