[RESOLVED] Want data from single column/row - SELECT returns resourse

jimlongo

Hi, again a NEWB question.

If I do a query that is going to return a single value

$result = mysql_query( " SELECT products_quantity FROM products WHERE products_model = '40'  ") ;

I would expect that it will return the number of products left of that item.

But if I echo the $result I get "Resource ID 3" which I guess is the identifier for the column in that row.

So I've come across this approach (amongst a few others) that get an array from the query and then selects a value from the array.

while ($row = mysql_fetch_assoc ($result))
echo   $row ['products_quantity']  ;

My question is - "Is there a simpler way (considering I'm just after 1 value) of stating the original query so that the result is the value or string I'm after, and not have to go through the second step"?

Thanks,
jim

Weedpacket

If you're only retrieving one row then you certainly don't need the loop.

$row = mysql_fetch_assoc ($result);
echo   $row ['products_quantity']  ;

Derokorian

You could even use chaining:

echo mysql_fetch_object($result)->products_quantity;

bradgrafelman

jimlongo;11001706 wrote:
I would expect that it will return the number of products left of that item.

Why would you expect that? All you've done is execute a query - you haven't told PHP that you want to start retrieving any possible results yet. Likewise, PHP doesn't automatically assume that you're going to do so after every query you execute (for a number of reasons... one being that it wouldn't make sense to do this for some queries).

jimlongo;11001706 wrote:
But if I echo the $result I get "Resource ID 3" which I guess is the identifier for the column in that row.

It's not an "identifier" for any column or row. It's just a [man]resource[/man] that was treated as if it were a string (which PHP is nice enough to output a string anyway rather than crashing because you told it to do something that doesn't make any sense, such as echo a resource :p).

More specifically, it's a resource that points to the (possibly empty) result set that MySQL may or may not have generated as result of the last successfully executed query.

jimlongo;11001706 wrote:
My question is - "Is there a simpler way (considering I'm just after 1 value) of stating the original query so that the result is the value or string I'm after, and not have to go through the second step"?

No, and hopefully the first paragraph in this reply sheds some light on why that is the case. Again, you're going to have to do something after you execute the query if you expect to retrieve any results that query produced.

As Weedpacket points out, it's a bit silly to use a while() loop when you only expect at most one row. Furthermore, there's also no reason to retrieve an array that is only ever going to contain a single item (a group of one is a rather lonely group 😉). Thus, I would say [man]mysql_result/man would be more appropriate here.

Finally, note that the entire [man]mysql[/man] extension is quite outdated and has been deprecated in favor of its successor, [man]MySQLi[/man] (or other alternatives such as [man]PDO[/man] - see [man]mysqlinfo.api.choosing[/man] for more info on the MySQL APIs).

jimlongo

Thanks, that's all very enlightening.