How to Use MySQL Clauses To Fetch Data

by Ahmed Khan  February 2, 2016

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

mysql clauses to fetch data

 

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.

data

 

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.

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:

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

where

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:

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

and

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:

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

OR

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:

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

like

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.

Start Creating Web Apps on Managed Cloud Servers Now!

Easy Web App Deployment for Agencies, Developers and E-Commerce Industry

About 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

Stay Connected:

You Might Also Like...