mysql null comparison example
mysql-null-comparison-example.php
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>mysql null comparison example</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="Author" content="Md Iqbal Hosan">
<meta name="title" content="mysql null comparison example">
<meta name="keywords" content="php, example, code, table, sql command, sql,mysql null value, check">
<meta name="description" content="mysql null comparison example">
<style>
h2, h4{background:orange; color:#000087;}
h2{ padding:3px; margin:3px; font-size:22px;}
h4{ padding:2px; margin:2px; font-size:19px;}
p{padding:2px; margin:2px; color:#0099Fd;}
body{ background:#FFFFFb;}
</style>
</head>
<body>
<h2>mysql null comparison example</h2>
<pre>
<?php
/*
-------------------------------------------------------------------------------------
WE HAVE A TABLE NAMED employees LIKE THIS:
CREATE TABLE `employees`
(
`EmployeeID` int(11) NOT NULL auto_increment,
`FirstName` varchar(50) default NULL,
`LastName` varchar(50) default NULL,
`JoiningDate` datetime default NULL,
`DepartmentID` int(11) default NULL,
`Designation` varchar(50) default NULL,
`MonthlySalary` double default NULL,
`CommissionPercentage` double default NULL,
`ContactNo` varchar(20) default NULL,
`EmailAddress` varchar(255) default NULL,
`PresentAddress` varchar(200) default NULL,
`Gender` varchar(1) default NULL,
`Religion` varchar(20) default NULL,
PRIMARY KEY (`EmployeeID`)
);
NOW WE WANT TO GET FirstName AND JoiningDate OF ALL EMPLOYEES
AND WOULD WANT TO SUBSTITUTE NULL BY N/A BY COMPARING WITH NULL COMPARISON FUNCTION
-------------------------------------------------------------------------------------
*/
$sql = "SELECT FirstName, IFNULL(JoiningDate, 'N/A') FROM employees";
/*
-------------------------------------------------------------------------------------
NOW QUESTION IS WHAT IS NULL ??
1. NULL IS A VALUE THAT IS UNAVAILABLE
2. NULL IS A VALUE THAT IS UNASSIGNED
3. NULL IS A VALUE THAT IS UNKNOWN
4. NULL IS A VALUE THAT IS INAPPLICABLE
5. NULL IS A VALUE THAT IS NOT EVEN EQUAL TO NULL
6. NULL IS A VALUE THAT IS NOT EQUAL TO ZERO : ZERO IS A NUMBER
7. NULL IS A VALUE THAT IS NOT EQUAL TO BLANK SPACE OR EMPTY STRING : SPACE IS A CHARACTER
AND 8. COLUMNS OF ANY DATATYPE CAN CONTAIN NULL VALUE
-------------------------------------------------------------------------------------
*/
$sql = "SELECT FirstName, JoiningDate FROM employees WHERE JoiningDate IS NOT NULL";
/*
----------------------------------------------------
USE ABOVE EXAMPLE IF YOU WANT NULL LESS VALUES
----------------------------------------------------
*/
$sql = "SELECT FirstName, JoiningDate FROM employees WHERE JoiningDate IS NULL";
/*
----------------------------------------------------
USE ABOVE EXAMPLE IF YOU WANT NULL DATE VALUES
----------------------------------------------------
*/
$sql = "SELECT FirstName, IFNULL(JoiningDate, 'N/A') FROM employees WHERE JoiningDate IS NULL";
/*
--------------------------------------------------------------------------------
USE ABOVE EXAMPLE IF YOU WANT TO SUBSTITUTE NULL JOINING DATE WITH N/A
--------------------------------------------------------------------------------
*/
$sql = "SELECT FirstName, IFNULL(JoiningDate, 'N/A') AS JoiningDate FROM employees WHERE JoiningDate IS NULL";
/*
---------------------------------------------------------------
USE ABOVE EXAMPLE IF YOU WANT TO USE ALIAS IN NULL DATE VALUES
---------------------------------------------------------------
*/
/*
----------------------------------------------------
USING PHP YOU CAN DO THIS.
GET CONNECTION TO DATABASE BY USING : mysql_connect()
SELECT DATABASE BY USING : mysql_select_db()
THEN RUN THE QUERY
----------------------------------------------------
*/
mysql_connect('localhost', 'root', 'root');
mysql_select_db('hr');
mysql_query($sql);
/*
-----------------------------------------------------------------------------
TO RUN THE PHP CODE PROVIDE REAL DATA TO mysql_connect AND mysql_select_db()
-----------------------------------------------------------------------------
*/
?>
</pre>
</body>
</html>
mysql null comparison example - output in the client
No comments:
Post a Comment
leave your comments here..