X
    Categories: Database Tutorials, Tips and GuidesLearn PHP Tutorials, Tips And Guides
Reading Time: 6 minutes

Hi all! In the previous installment of this MySQL series, I described a simple PHP and MySQL powered CRUD application.

 

In this tutorial, we will learn different clauses which can help us select only that data which verifies our conditions from database. We will be learning the following MySQL clauses in this tutorial.

  1. Where Clause
  2. AND Clause
  3. OR Clause
  4. LIKE Clause

Related: Introduction To MySQL Data Types

We will be using data in the picture below, on which we will be applying our clauses. So you can insert the same data on MyGuest table which we have created previously till then I can wait for you.

 

All set! Let’s start.

For this tutorial, we will be using the same selectdata() function that we have created on our CRUD.php file with some modification. It will take $sql query with select and perform it.

function selectdata($sql)
{

$conn = OpenCon();
$result = $conn->query($sql);
if($result)
{
if($result->num_rows > 0)
{
return $result;
}
else
{
return "Zero Result";
}
}
else
{
return $result->error;
}

}

WHERE Clause:

The WHERE clause of MySQL works like an IF condition in any programming language. If the condition in WHERE clause succeeds, it will show the data, else it will give empty result. The basic structure of WHERE clause is:

SELECT field1, field2,…fieldN table_name1, table_name2…
[WHERE condition1 [AND [OR] [LIKE] [IN]] condition2…..

We will be using WHERE clause to find the users having the last name of Qureshi. Now open your index.php file and write the following code in it:

<table>
<tr>
<td>ID</td>
<td>   First Name</td>
<td>   Last Name</td>
<td>   Email</td>
<td>   Date</td>
</tr>
<?php
include 'crud.php';
$sql = "SELECT * FROM `myguests` WHERE `lastname` = 'qureshi'";
$result = selectdata($sql);
if($result !== "zero")
{
while($row = $result->fetch_assoc())
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "<td>" . $row['reg_date'] . "</td>";
echo "</tr>";
}
}
else
{
echo $result;
}
?>
</table>

When you run this code, the result will contain all the rows having last name as Qureshi. You will get the following result:

AND Clause

The AND clause only returns a result when both conditions are true, otherwise it will return empty result. The basic structure of AND clause is:

SELECT * FROM Table

WHERE Condition1

AND condition2;

We will be using AND clause to find the users having first name of ahmed and last name of khan. Now open your index.php file and write the following code in it:

<table>
<tr>
<td>ID</td>
<td>   First Name</td>
<td>   Last Name</td>
<td>   Email</td>
<td>   Date</td>
</tr>
<?php
include 'crud.php';
$sql = "SELECT * FROM `myguests` WHERE `lastname` = 'khan' AND `firstname` = 'ahmed'";
$result = selectdata($sql);
if($result !== "zero")
{
while($row = $result->fetch_assoc())
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "<td>" . $row['reg_date'] . "</td>";
echo "</tr>";
}
}
else
{
echo $result;
}
?>
</table>

When you run this code the result will contain one row since we have only one row which macthes this condition. The result will be as follow

OR Clause

The OR clause returns a result if even one condition is true, otherwise it will return empty result if both are false. The basic structure of OR clause is:

SELECT * FROM Table

WHERE Condition1

OR condition2;

We will be using OR clause to find the users having first name of ahmed and last name of khan. Now open your index.php file and write the following code in it:

<table>
<tr>
<td>ID</td>
<td>   First Name</td>
<td>   Last Name</td>
<td>   Email</td>
<td>   Date</td>
</tr>
<?php
include 'crud.php';
$sql = "SELECT * FROM `myguests` WHERE `lastname` = 'khan' OR `firstname` = 'ahmed'";
$result = selectdata($sql);
if($result !== "zero")
{
while($row = $result->fetch_assoc())
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "<td>" . $row['reg_date'] . "</td>";
echo "</tr>";
}
}
else
{
echo $result;
}
?>
</table>

When you run this code, the result will contain all the rows having last name as Khan. You will get the following result:

IN Keyword

The WHERE clause when used together with the IN keyword only affects the rows whose values matches the list of values provided in the IN keyword. IN helps reduces number of OR clauses you may have to use

The following query gives rows where membership_number is either 1 , 2 or 3

SELECT * FROM `myguests` WHERE `id` IN (1,2,3);

COMPARISON OPERATORS

The less than (), equal to (=), not equal to () comparison operators can be  used with the Where clause

= Equal To

The following script gets all the female members from the members table using the equal to comparison operator.

SELECT * FROM `myguests` WHERE `firstname` = 'ahmed';

< > Not Equal To

The following script gets all the movies whose  id is not 1.

SELECT * FROM `myguests` WHERE `id`<> 1;

Limiting Your Results

You can use the LIMIT clause to return only a certain number of records in your SELECT query result. There are two requirements when using the LIMIT clause: offset and number of rows. The offset is the starting position, and the number of rows should be self-explanatory.

SELECT * FROM  'myguests'  ORDER BY lastname LIMIT 0, 5;

LIKE Clause

The LIKE is used to search columns in a database with some patterns. The patterns are called WILDCARDS which work along with LIKE to search specified column in database. The basic structure of LIKE is:

SELECT column_name(s)

FROM table_name

WHERE column_name LIKE pattern;

Description for WILDCARDS operators are:

  1. % used to find characters between words
  2. _ Single works like the fill in the blank
  3. [ ] used to find words with matching character
  4. [!] used to find words which does not match these characters

We will be using LIKE clause to find the users having a first name that starts with “a”. Now open your index.php file and write the following code in it:

<table>
<tr>
<td>ID</td>
<td>   First Name</td>
<td>   Last Name</td>
<td>   Email</td>
<td>   Date</td>
</tr>
<?php
include 'crud.php';
$sql = "SELECT * FROM `myguests` WHERE `firstname` LIKE 'a%' ";
$result = selectdata($sql);
if($result !== "zero")
{
while($row = $result->fetch_assoc())
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "<td>" . $row['reg_date'] . "</td>";
echo "</tr>";
}
}
else
{
echo $result;
}
?>
</table>

When you run this code, the result will contain all the rows having first name started with a. You will get the following result:

Aggregate Functions with SELECT

MySQL has many built-in functions that allow you to perform all sorts of operations on integers, strings, and dates.

COUNT() returns only one row in the result set. This result is equal to the actual number of rows in the table, which is what your query was asking. The column header for the result set is COUNT(id), the expression used in the SELECT query.:

SELECT COUNT(id) AS row_count FROM myguests;

MIN() returns only Min records in the result set

SELECT MIN(name_dateadded) FROM myguests;

Max() returns only Min records in the result set

SELECT Max(name_dateadded) FROM myguests;

Conclusion:

In this tutorial, we have studied different operators which help us to find data from our database which is more specified and fast, rather than getting all the data. In the next installment of this MySQL series, I will highlight how to join tables in MySQL. Here is a short introduction and list of major topics in this MySQL series.

Ahmed Khan :Ahmed was a PHP community expert at Cloudways - A Managed PHP Hosting Cloud Platform. He is a software engineer with extensive knowledge in PHP and SEO. He loves watching Game of Thrones is his free time. Follow Ahmed on Twitter to stay updated with his works. You can email him at ahmed.khan@cloudways.com