How To Use SQL NULL STATEMENT With Example Using PHP

Posted By Bajarangi soft , Posted On 18-09-2020

SQL NULL Values is a field with a NULL value is a specified as no value or that field will be empty. SQL NULL Values represent missing unknown data. While inserting data into table if user don't wanted to store data for some field that time default Null value holds in that field .

Syntax SQL NULL Values.

1.IS NULL

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

2.IS NOT NULL

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Lets Learn.
1.Create database and table.
Student table.

StudentID	StudentName	Address	Country	CourseID	DOB
1	Liam	23 Andrew road	USA	5	1995-08-21
2	Andrew	2415 Anthony market str	Germany	6	NULL
4	Ryan	18 Christopher sq.	INDIA	2	NULL
5	Charles	878 Eli Berguvsvägen 8	UK	1	1995-01-11
8	Anikth	23 Andrew road	AFRICA	3	NULL
9	Andrew	2415 Anthony market str	Germany	3	1996-04-18
10	Bhavya	18 Christopher sq.	INDIA	3	NULL

for this above student table we need to check which fields are NULL and which fields are NOT NULL by SQL queries .

Example(1)

SELECT StudentName,Address,DOB
FROM student
WHERE DOB IS NULL;

above SQL Statement selecting student name ,address and dob from student table where DOB is NULL

Example(2)

SELECT StudentName,Address,DOB
FROM student
WHERE DOB IS NOT NULL

above SQL Statement selecting student name ,address and dob from student table where DOB is NOT NULL

2.Complete code of SQL NULL Values. in PHP script.

<!DOCTYPE html>
<html lang="en">
<head>
    <title>How to use SQL NULL Values.</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
    <div class="text-center">
        <h1>SQL NULL Values</h1>
    </div>
    <div class="well">
        <?php
        $connect = mysqli_connect("localhost", "root", "", "management");

        if ($connect->connect_error) {
            die("Connection failed: " . $connect->connect_error);
        }

        $sql_1 = "SELECT StudentName,Address,DOB FROM student WHERE DOB IS NULL;";
        $sql_2 = "SELECT StudentName,Address,DOB FROM student WHERE DOB IS NOT NULL;"; 

      echo "<h4 class='text-danger'>Example(1)</h4>";
        if($result_1 = mysqli_query($connect, $sql_1)){
            if(mysqli_num_rows($result_1) > 0){
                echo "<table class='table'>
                    <tr>
                     <th>StudentName</th>
                     <th>Address</th>
                     <th>DOB</th>
                    </tr>
                    ";
                while($row = $result_1->fetch_assoc()){
                    echo "<tr>
                   <td>".$row['StudentName'] /*student table*/ ."</td> 
                   <td>".$row['Address']  ."</td>
                   <td>".$row['DOB']  ."</td>
                  </tr>";
                }
                echo "</table>";
            } else{
                echo "No records found.";
            }
        }
        echo "<h4 class='text-danger'>Example(2) </h4>";
        if($result_1 = mysqli_query($connect, $sql_2)){
            if(mysqli_num_rows($result_1) > 0){
                echo "<table class='table'>
                    <tr>
                     <th>StudentName</th>
                     <th>Address</th>
                     <th>DOB</th>
                    </tr>
                    ";
                while($row = $result_1->fetch_assoc()){
                    echo "<tr>
                    <td>".$row['StudentName'] /*student table*/ ."</td> 
                   <td>".$row['Address']  ."</td>
                   <td>".$row['DOB']  ."</td>
                  </tr>";
                }
                echo "</table>";
            } else{
                echo "No records found.";
            }
        }
        ?>
    </div>
    <br>
</div>
</body>
</html>

Related Post