how to fetch only a row from a mysql query

Question

i allways end up doing this

$q = "select whatIwant FROM table where id = 'myId'";
$r = mysql_query($q);
while($i = mysql_fetch_array($r){
   /* iterate just one withem */
   $j = $i['whatIwant'];
}
echo $j;

how is this usually done? (i just want to avoid the unecessary loop)

Answer 1

select whatIwant FROM table where id = 'myId' limit 1

Answer 2

In addition to the correct answers, there are multiple ways to handle this:

• If you add LIMIT 1, the result set will only contain one row and the while loop will terminate after one iteration:

  $q = "select whatIwant FROM table where id = 'myId' LIMIT 1";
  $r = mysql_query($q);
  while($i = mysql_fetch_array($r)) {
     $j = $i['whatIwant'];
  }
  echo $j;
  

• If you call mysql_fetch_array without a loop, you will get the first row of the result set:

  $q = "select whatIwant FROM table where id = 'myId'";
  $r = mysql_query($q);
  $i = mysql_fetch_array($r);
  $j = $i['whatIwant'];
  echo $j;
  

• If you add break to the loop body, the loop will terminate after one iteration.

  $q = "select whatIwant FROM table where id = 'myId'";
  $r = mysql_query($q);
  while($i = mysql_fetch_array($r)) {
     $j = $i['whatIwant'];
     break;
  }
  echo $j;
  

You can also combine these approaches (although using break is not really elegant in this case).

The best approach is using LIMIT and omitting the while loop, as @zaf shows in his answer. This makes the code clearer and avoids unnecessary operations in the database.

Answer 3

You can specify the number of rows in the sql query using the 'LIMIT' syntax.

Also, you can just remove the while loop and get the first row returned - if thats all you want.

For example (without returned value checking):

$q = "select whatIwant FROM table where id = 'myId' LIMIT 1";
$r = mysql_query($q);
$i = mysql_fetch_array($r);
print_r($i);