Using LIMIT & WHERE clauses with MySQL & PHP

Now that you have a grasp on how basic MySQL queries work, learning how to use the LIMIT & WHERE clauses are the next step. Unlike using a query that retrieves all the data, sometimes the user just needs to pull the first few rows, or be able to sort them as well. Using LIMIT & WHERE clauses in MySQL is a more efficient way of retrieving data.

Let’s start by using LIMIT

<code>

$sql = "SELECT * FROM `friends` LIMIT 0,1";
 $result = mysql_query($sql);
 while($r = mysql_fetch_array($result)) {
  $id = $r['id'];
  $first = $r['first_name'];
  $game = $r['video_game'];

   echo "$id - $first - $game";
}

</code>

The output of this code will be: 1 – Joe – Skate 2. This code will display the first row listed in the database, as 0,1 represents the first entry. If you wanted to display the first 2 entries, you could adjust the query to LIMIT 0,2.

Next we’ll go over how to implement the WHERE clause into your MySQL query.

<code>
$sql = "SELECT * FROM `friends` WHERE video_game = 'Skate 2'";
 $result = mysql_query($sql);
  while($r = mysql_fetch_array($result)) {
    $id = $r['id'];
    $first = $r['first_name'];
    $game = $r['video_game'];

   echo "$id - $first - $game";
}

</code>

The output of this code will be: 1 – Joe – Skate 2. Yes, the same result as the last query, but retrieved using WHERE. You can use the WHERE clause when you are trying to exclude data from your result as well. For example, if we wanted to display all of the rows except for the ones where the first name is Joe, we’d use:

<code>
$sql = "SELECT * FROM `friends` WHERE first_name != 'Joe'";
 $result = mysql_query($sql);
  while($r = mysql_fetch_array($result)) {
   $id = $r['id'];
   $first = $r['first_name'];
   $game = $r['video_game'];

   echo "$id - $first - $game";
}

</code>

The output of this code will be: 2 – Erin – NBA2k11. The query tells the database to retrieve all of the records where the first_name is not equal to Joe. Simple enough isn’t it?

Leave a Comment

Your email address will not be published. Required fields are marked *