2

How do I select from a database table a user id that is linked to the minimum value of the table

for example

User ID    Pending
-------    --------
0          5
1          4
2          7

'$resultSet = $mysqli->query("SELECT MIN(write_pending) FROM writerdata_tb");
            if ($temp = $resultSet->fetch_assoc()) {

            }'

in this case, I would want the data that is returned to have the value of the user with the least pending, in this case being user 1. How do I set up a MySqli query to handle this? And what would be the best way to save the result as a variable for use in the rest of my php?

trecoolable
  • 187
  • 1
  • 3
  • 12

1 Answers1

3

Probably something like this, but it depends heavily on what your table structure is like. I've assumed you've got a generic users table with some IDs and this pending value you've mentioned.

SELECT userID FROM users WHERE pending = ( SELECT MIN(pending) FROM users );

The nested Select statement gets the smallest pending value in that column of your table, then you can refine your select userID statement by forcing pending to be the value returned from that nested select call.

UPDATE

To clarify your followup question of processing results, here's a modification of the code you provided:

$resultSet = $mysqli->query("SELECT userID FROM users WHERE pending = ( SELECT MIN(pending) FROM users )");

if($resultSet->num_rows > 0) {
    //We use a while loop here, in the event that there are multiple rows
    while($row = $resultSet->fetch_assoc()) {
        echo $row["userID"]; //Accessing the rows index at "userID"
    }
}
James
  • 903
  • 7
  • 22
  • so the query looks for the minimum value in pending first and then uses that as a pointer to the userID. I'm implementing it now, thank you – trecoolable Nov 03 '15 at 01:55
  • Yes, the subquery returns the minimum of all pending values, and you can search for the userID that is associated with that value. Please accept the answer if this solved it for you. Thanks! – James Nov 03 '15 at 01:56
  • Yes, I shall, but also, how do i catch the result and store it as a variable? I've been using a $resultset method but I'm sure there's a more efficient way to do it – trecoolable Nov 03 '15 at 02:01
  • $resultset is simply a variable name, not a method. To process the results of a table query you store them in a variable (like you have) then process every row inside that set of results. Your sample only has one row in the result, because you have unique values for pending (I don't know if this is always true) but look at the update to my answer above to see an example. – James Nov 03 '15 at 02:04
  • Yes that's what I was wondering. While I'm still here, do you know if you can use mysql real escape strings on cookies? – trecoolable Nov 03 '15 at 02:17
  • Please create a new question, or better yet Google it first. This comment thread is not the place for alternate discussions. – James Nov 03 '15 at 02:20