Skip to content

Spoke Right

Education for everyone

https://spokeright.com
Primary Menu
  • Home
  • Our Services
    • Translation
    • Interpretation
    • Transcription
    • Voice-Over
    • Proofreading
    • Content writing
  • Free Education
    • Learn Android Studio
    • Learn Python
    • Learn MongoDB
    • Learn MySql
    • Learn React
    • Montessori Education
  • Spoke Right News
  • Earn Online
  • Classic Games by Spoke Right
    • TicTac
    • Checkers
    • Foosball
    • Billiards
    • Master Chess
    • Logic Game
  • Contact Us
Download Now
  • Home
  • Learn MySql
  • How is NULL value handled in MySQL give example
  • Learn MySql

How is NULL value handled in MySQL give example

Spoke Right January 6, 2023 2 min read
https://spokeright.com/

https://spokeright.com/

Handling NuLL Example

You can use the if…else condition to prepare a query based on the NULL value.

The following example takes the tutorial_count from outside and then compares it with the value available in the table.

Example

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Handling NULL</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = '[email protected]';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         $tutorial_count = null;
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
   
         if( isset($tutorial_count )) {
            $sql = 'SELECT tutorial_author, tutorial_count
               FROM  tcount_tbl
               WHERE tutorial_count = ' + $tutorial_count;
         } else {
            $sql = 'SELECT tutorial_author, tutorial_count
               FROM  tcount_tbl
               WHERE tutorial_count IS NULL';
         }
         $result = $mysqli->query($sql);
           
         if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
               printf("Author: %s, Count: %d <br />",
                  $row["tutorial_author"], 
                  $row["tutorial_count"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.
No record found.
  1. SELECT * FROM GrowthMindSets  
  2. WHERE EmailAddress IS NOT NULL; 
 

MySQL SET NULL Values in UPDATE Statement

 
By using the assignment operator (“=”), you can set any value of a column to NULL by using the Update Statement.
 
Example
  1. UPDATE growthmindsets  
  2. SET EmailAddress = NULL  
  3. WHERE GMS_ID = 4 and GMS_ID = 5 
By using the following query, you can check the output.
  1. SELECT * FROM GROWTHMINDSETS  
  2. WHERE EmailAddress IS NULL AND PhoneNo IS NULL 
 

NULL VALUES RELATED FUNCTIONS IN MYSQL

 

1) In MYSQL there are some functions intended specifically for use with NULL values which include ISNULL () and IFNULL (). ISNULL () is true if its argument is NULL and false otherwise.

 
Example
  1. SELECT ISNULL(NULL), ISNULL(0), ISNULL(1); 
 

2) IFNULL() takes two arguments. If the first argument is not NULL, that argument is returned; otherwise, the function returns its second argument.

 
Example 1
  1. SELECT IFNULL(NULL,‘a’), IFNULL(0,‘b’); 
 
Example 2
 
In this example, I will show you a query that returns the empty string (‘’) if its value is NULL.
  1. SELECT First_Name, Last_Name, Description, IFNULL(EmailAddress, ”), IFNULL(PhoneNo, ”) FROM GROWTHMINDSETS; 
 

3) Other functions handle NULL values in various ways, so you have to know how a given function behaves. In many cases, passing a NULL value to a function results in a NULL return value.
 
Example
 
Any NULL argument passed to CONCAT() causes it to return NULL:

  1. SELECT CONCAT(‘a’,‘b’), CONCAT(‘a’,NULL,‘b’); 

Continue Reading

Previous: How to use join query in PHP with example
Next: How to use database example in MySQL

Related Stories

How to use database example in MySQL
2 min read
  • Learn MySql

How to use database example in MySQL

January 6, 2023
How to use join query in PHP with example
1 min read
  • Learn MySql

How to use join query in PHP with example

January 3, 2023
How to sort data in MySQL using PHP https://spokeright.com/
1 min read
  • Learn MySql

How to sort data in MySQL using PHP

January 2, 2023

This AD Will support Us

Support Us

Coding Ustad LTD Support
Coding Ustad LTD Support

Coding Ustad LTD

Coding Ustad LTD
Coding Ustad LTD

Recent Posts

  • How to Install Node.js and npm on CentOS 7
  • How to Install Node.js and npm on CentOS 7
  • How to Install CentOS Web Panel (CWP) on CentOS 7
  • The Power of Marketing in Real Estate: A Guide to Boost Your Business
  • Vehicle Verification in Pakistan

Get free Hosting

Hostens.com - A home for your website

You may have missed

How to Install Node.js and npm on CentOS 7
4 min read
  • Coding Ustad LTD
  • Education for Everyone

How to Install Node.js and npm on CentOS 7

May 3, 2023
How to Install Node.js and npm on CentOS 7
4 min read
  • Coding Ustad LTD
  • Education for Everyone

How to Install Node.js and npm on CentOS 7

May 2, 2023
How to Install CentOS Web Panel (CWP) on CentOS 7
3 min read
  • Coding Ustad LTD
  • Education for Everyone

How to Install CentOS Web Panel (CWP) on CentOS 7

May 2, 2023
The Power of Marketing in Real Estate: A Guide to Boost Your Business https://thaikadar.com/secureinvestment/
2 min read
  • Article By Spoke Right
  • Blog By Spoke Right
  • Circular Byte Private Limited

The Power of Marketing in Real Estate: A Guide to Boost Your Business

January 30, 2023
  • Home
  • Our Services
  • Free Education
  • Spoke Right News
  • Earn Online
  • Classic Games by Spoke Right
  • Contact Us
Copyright © All rights reserved. | MoreNews by AF themes.