PHP MySQL Logical Operators

This tutorial explains how to select data from a database using WHERE clause and SELECT query with MySQL logical operators such as BETWEEN, IN, AND, OR etc., in PHP. We are going to use WHERE clause and SELECT query with the operators to filter data from a MySQL database table.

MySQL Logical Operators

MySQL logical operators are used in conjunction with the WHERE clause in a SELECT statement to filter and retrieve specific data from a database table. Some of the commonly used logical operators in MySQL are given below:

In this guide, we will discuss the usage of four operators (i.e. BETWEEN, IN, AND, OR) only.

For demonstration purpose, I have created a table called "sales" in a MySQL database called "my_company" with following records.

Heads Up: In XAMPP, the database and table names are case insensitive. It will consider upper/lower case as lowercase only.

If you haven't created a database and table, refer the following guide.

• Create MySQL Database And Table Using PHP In XAMPP

Steps To Write A PHP Code

1. Specify servername, username, password and database name in your PHP code.

2. Create a connection using mysqli_connect() function.

Code:

$connection = mysqli_connect($server_name, $user_name, $password, $database_name);

3. Specify the SQL Query to select particular or all columns records using BETWEEN, IN AND and OR operators from the table by a condition.

4. Store the selected results into a variable named "final" using mysqli_query() function. It will take connection and query as parameters.

Code:

mysqli_query($connection, $query);

5. Get the rows one by one from the "final" variable using mysqli_num_rows() function.

6. Close the connection using the mysqli_close() function.

Code:

mysqli_close($connection);

Let us go ahead and see how to select and filter data from a MySQL database table using BETWEEN, IN, AND, and OR operators.

PHP Code To Select Data From MySQL Database Table Using BETWEEN Operator

The BETWEEN operator is used to return the rows present in between the two given values. It will take the first value followed by AND operator and second value.

BETWEEN operator will return all the values present in the column that are present in the given range (first value - second value).

SQL Query Syntax:

SELECT column1,column2,.,column n from table_name WHERE column_name BETWEEN value1 AND value2

Example Code:

In this example, we will select the values,

• from id column between 1 and 4,
• from count column between 34 and 100.

Create a plain text file named select.php under /htdocs folder with the following contents in it.

Heads Up: If you use Linux, the htdocs folder will be under /opt/lampp/ directory. If you're on Windows, the htdocs will be usually in C:\xampp\ folder.

<?php
//specify the server name
$server_name = "localhost";

//specify the username - here it is root
$user_name = "root";

//specify the password - it is empty
$password = "";

//specify the database name
$database_name = "my_company";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password, $database_name);

//select all columns such that id was in the range of 1 and 4
$query = "SELECT * from sales where id  BETWEEN 1 AND 4";

#get the result
$final = mysqli_query($connection, $query);

if (mysqli_num_rows($final) > 0) {
 //get the output of each row
  while($i = mysqli_fetch_assoc($final)) {
      //get all columns
    echo "id: " . $i["id"]. "  ----> name: " . $i["name"]."  ----> count: " . $i["count"]. "<br>";
  }
} else {
  echo "No results";
}

//new line
echo "<br>";
echo "<br>";

//select all columns such that count was in the range of 34 and 100
$query1 = "SELECT * from sales where count  BETWEEN 34 AND 100";

#get the result
$final1 = mysqli_query($connection, $query1);

if (mysqli_num_rows($final1) > 0) {
 //get the output of each row
  while($j = mysqli_fetch_assoc($final1)) {
      //get all columns
    echo "id: " . $j["id"]. "  ----> name: " . $j["name"]."  ----> count: " . $j["count"]. "<br>";
  }
} else {
  echo "No results";
}

//close the connection
mysqli_close($connection);
?>

Open your web browser and navigate to to http://localhost/select.php URL. You will see all the values from id column between 1 and 4, and from count column between 34 and 100.

PHP Code To Select Data From MySQL Database Table Using IN Operator

The IN operator is used to get the values present in the given list of values. It will take a list of values separated by comma in brackets (). So It will return all the values present in the list-().

SQL Query Syntax:

SELECT column1,column2,.,column n from table_name WHERE column_name IN (value1,value2,…,value n)

Example Code:

In this example, we will get the values,

• from id column where values can be in (1,3,6),
• from name column where values can be in ("Chocos","Eggs").

<?php
//specify the server name
$server_name = "localhost";

//specify the username - here it is root
$user_name = "root";

//specify the password - it is empty
$password = "";

//specify the database name
$database_name = "my_company";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password, $database_name);

//select all columns such that id was in 1,3,6
$query = "SELECT * from sales where id  IN (1,3,6)";

#get the result
$final = mysqli_query($connection, $query);

if (mysqli_num_rows($final) > 0) {
 //get the output of each row
  while($i = mysqli_fetch_assoc($final)) {
      //get all columns
    echo "id: " . $i["id"]. "  ----> name: " . $i["name"]."  ----> count: " . $i["count"]. "<br>";
  }
} else {
  echo "No results";
}

//new line
echo "<br>";
echo "<br>";

//select all columns such that name was Chocos,Eggs
$query1 = "SELECT * from sales where name  IN ('Chocos','Eggs')";

#get the result
$final1 = mysqli_query($connection, $query1);

if (mysqli_num_rows($final1) > 0) {
 //get the output of each row
  while($j = mysqli_fetch_assoc($final1)) {
      //get all columns
    echo "id: " . $j["id"]. "  ----> name: " . $j["name"]."  ----> count: " . $j["count"]. "<br>";
  }
} else {
  echo "No results";
}

//close the connection
mysqli_close($connection);
?>

Open your web browser and point it to http://localhost/select.php URL. You will see the values from id column where values are 1,3, and 6 and from name column where values are"Chocos" and "Eggs".

PHP Code To Select Data From MySQL Database Table Using AND Operator

The AND operator is used to get the values when both the conditions are True. It will take two conditions and check the conditions, if both are True, the rows will be returned, otherwise no results.

SQL Query Syntax:

SELECT column1,column2,.,column n from table_name WHERE column_name operator value AND column_name operator value;

Example Code:

In this example, we will select all the columns where the,

• id is greater than 2 AND count less than 30,
• name is Chocos AND id is 4.

<?php
//specify the server name and here it is localhost
$server_name = "localhost";

//specify the username - here it is root
$user_name = "root";

//specify the password - it is empty
$password = "";

//specify the database name
$database_name = "my_company";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password, $database_name);

//select all columns such that id greater than 2 and count less than 30
$query = "SELECT * from sales where id>2 AND count<30";

#get the result
$final = mysqli_query($connection, $query);

if (mysqli_num_rows($final) > 0) {
 //get the output of each row
  while($i = mysqli_fetch_assoc($final)) {
      //get all columns
    echo "id: " . $i["id"]. "  ----> name: " . $i["name"]."  ----> count: " . $i["count"]. "<br>";
  }
} else {
  echo "No results";
}

//new line
echo "<br>";
echo "<br>";

//select all columns such that name is Chocos and id is 4
$query1 = "SELECT * from sales where name='Chocos' AND id=4";

#get the result
$final1 = mysqli_query($connection, $query1);

if (mysqli_num_rows($final1) > 0) {
 //get the output of each row
  while($j = mysqli_fetch_assoc($final1)) {
      //get all columns
    echo "id: " . $j["id"]. "  ----> name: " . $j["name"]."  ----> count: " . $j["count"]. "<br>";
  }
} else {
  echo "No results";
}

//close the connection
mysqli_close($connection);
?>

Open your web browser and point it to http://localhost/select.php URL.

As you can see, the values from the columns where the id is greater than 2 and count less than 30, and name is Chocos and id is 4 are displayed.

PHP Code To Select Data From MySQL Database Table Using OR Operator

The OR operator is used to get the values when any of the conditions is True. It will take two conditions and check the conditions, if any one is True, the rows will be returned, otherwise there will be no results.

SQL Query Syntax:

SELECT column1,column2,.,column n from table_name WHERE column_name operator value OR column_name operator value;

Example Code:

In this example, we will select all the columns where the

• id is greater than 2 OR count less than 30,
• name is Chocos OR id is 4.

<?php
//specify the server name and here it is localhost
$server_name = "localhost";

//specify the username - here it is root
$user_name = "root";

//specify the password - it is empty
$password = "";

//specify the database name
$database_name = "my_company";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password, $database_name);

//select all columns such that id greater than 2 or count less than 30
$query = "SELECT * from sales where id>2 OR count<30";

#get the result
$final = mysqli_query($connection, $query);

if (mysqli_num_rows($final) > 0) {
 //get the output of each row
  while($i = mysqli_fetch_assoc($final)) {
      //get all columns
    echo "id: " . $i["id"]. "  ----> name: " . $i["name"]."  ----> count: " . $i["count"]. "<br>";
  }
} else {
  echo "No results";
}

//new line
echo "<br>";
echo "<br>";

//select all columns such that name is Chocos or id is 4
$query1 = "SELECT * from sales where name='Chocos' OR id=4";

#get the result
$final1 = mysqli_query($connection, $query1);

if (mysqli_num_rows($final1) > 0) {
 //get the output of each row
  while($j = mysqli_fetch_assoc($final1)) {
      //get all columns
    echo "id: " . $j["id"]. "  ----> name: " . $j["name"]."  ----> count: " . $j["count"]. "<br>";
  }
} else {
  echo "No results";
}

//close the connection
mysqli_close($connection);
?>

Open your web browser and point it to http://localhost/select.php URL.

Conclusion

In this tutorial, we discussed how to filter data from a MySQL database table using MySQL logical operators such as BETWEEN, IN, AND, OR etc., along with WHERE clause and SELECT query in PHP. We provided example codes for each operator. You can amend the code as per your requirements and use them in your projects.