phpmysql

how to fetch only a row from a mysql query


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)


Solution

  • 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.