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>
No comments:
Post a Comment
leave your comments here..