How To Use SQL NULL STATEMENT With Example Using PHP

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

SQL NULL Values

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