By Scott McCabe on

I'm still new to database queries.

The following does not work:

$sql = "SELECT data FROM {uc_orders} WHERE uid=%d AND order_status='%s' AND created=MAX(created)";
$order_status = 'completed';
if ($result = db_result(db_query($sql, $user->uid, $order_status))) {...

This produces the following error:

"user warning: Invalid use of group function query: SELECT..."

Please tell me (and anyone like me) what the proper query is to select the single value I need without using a LIMIT 1.

⋅ Categories: Drupal 6.x

Comments

nevets’s picture

nevets commented

Why don't you want to use limit 1 (or db_query_range)?

Scott McCabe’s picture

Scott McCabe commented

I would love to use LIMIT, but since not all databases agree on the LIMIT syntax, db_query_range() is recommended by the Drupal community instead.

I only need one value from the latest table row, so it seems odd to use db_query_range(), not to mention I don't understand the arguments as explained in the API documentation on that function (http://api.drupal.org/api/function/db_query_range/6). There is one argument in the function signature, but a whole bunch of possible arguments including the variable replacements in my SQL statement.

I was hoping (and still highly appreciate if) someone would provide a quick answer using db_result, since my plate is overflowing on this project (not to complain, but I'm going a little nuts over here), but I guess now is the time to dive into the "args" stuff in the db_query_range() function and then specify a range of, well, one.

nevets’s picture

nevets commented

This

db_query($sql, $user->uid, $order_status)

would become

db_range_query($sql, $user->uid, $order_status, 0, 1)
Scott McCabe’s picture

Scott McCabe commented

Thank you for your responses.

After my last reply, I kicked into 'hacker mode' and dived into db_query_range() (learned func_get_args(), array_pop(), etc.) and basically arrived to the same conclusion (the only difference was I put the query arguments into an array).