Home PHP MySQL PHP MySQL Logical Operators

PHP MySQL Logical Operators

Select Data From A Database Using MySQL Logical Operators In PHP In XAMPP

By Sravan Kumar
Published: Last Updated on 549 views

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 supports the following logical operators:

ALLTRUE if all of the subquery values meet the condition
ANDTRUE if all the conditions separated by AND is TRUE
ANYTRUE if any of the subquery values meet the condition
BETWEENTRUE if the operand is within the range of comparisons
EXISTSTRUE if the subquery returns one or more records
INTRUE if the operand is equal to one of a list of expressions
LIKETRUE if the operand matches a pattern
NOTDisplays a record if the condition(s) is NOT TRUE
ORTRUE if any of the conditions separated by OR is TRUE
SOMETRUE if any of the subquery values meet the condition
MySQL Operators (Source - w3schools)

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.

idnamecount
5Shampoo10
2Milk20
3Books14
4Chocos45
6Eggs12
1Cooking-Gas40
Records From sales table
View Table Records In MySQL Database
View Table Records In MySQL Database

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.

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.

Select Data From MySQL Database Table Using BETWEEN Operator
Select Data From MySQL Database Table Using BETWEEN Operator

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

Select Data From MySQL Database Table Using IN Operator
Select Data From MySQL Database Table Using IN Operator

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.

Select Data From MySQL Database Table Using AND Operator
Select Data From MySQL Database Table Using AND Operator

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.

Select Data From MySQL Database Table Using OR Operator
Select Data From MySQL Database Table Using OR Operator

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.

You May Also Like

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

This website uses cookies to improve your experience. By using this site, we will assume that you're OK with it. Accept Read More