Solved

Why does this query not work for NULL?

Posted on 2014-04-16
7
232 Views
Last Modified: 2014-04-17
I have a recordset that looks like this in SQL Server Studio:

defaultname                                                       overridename

Chief Financial Officer
Group Vice President
Regional Vice President
Area Vice President                                            NULL
Chief Financial Officer                                        NULL
Vice President                                                     NULL
Manager                                                              NULL

As I'm looping through my results, I'm trying to check to see if the overridename column is NULL. I don't care if it's empty, I want to know if it's NULL.

if($workqueueexclusiverolelist[$i]["overridename"]==NULL)
{
echo $workqueueexclusiverolelist[$i]["defaultname"]." |  corp (unassigned)";
}

The result that I get from the above gives me every row without a value as opposed to only those that are NULL.

What am I doing wrong? How can I better craft my query so I'm getting only those rows that are NULL as opposed to everything that's empty?
0
Comment
Question by:brucegust
7 Comments
 
LVL 38

Assisted Solution

by:Tom Beck
Tom Beck earned 100 total points
ID: 40004337
How about is_null?

if(is_null($workqueueexclusiverolelist[$i]["overridename"]))
{
echo $workqueueexclusiverolelist[$i]["defaultname"]." |  corp (unassigned)";
}

http://www.php.net/is_null
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 300 total points
ID: 40004352
NULL in PHP is not really the same as a null column coming back from SQL.  For this reason I define my columns that might be expected to be empty this way:

NOT NULL DEFAULT ''

The '' there is a pair of apostrophes with nothing between them, in other words, an empty string.  Then when I fetch the column, I can test for PHP empty() or == NULL or == '' and get the expected result.

This is a little bit TL;DR but it shows what came to me as a surprise.  Look at the output when a bound variable contains an empty string and it's used to SELECT a column with SQL null in it.
http://www.iconoun.com/demo/temp_michael_munger.php

<?php // demo/temp_michael_munger.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';

// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray"    , "lname" => "Paseur"   )
, array( "fname" => "Bill"   , "lname" => "O'Reilly" )
)
;
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
require_once('RAY_live_data.php');
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// CREATING A FIRST TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE first_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) DEFAULT NULL
, lname VARCHAR(24) DEFAULT NULL
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

echo PHP_EOL . "****** LOADING FIRST TABLE (HAS NULLS)" . PHP_EOL;

// PREPARE A QUERY TO LOAD OUR DATA INTO THE TABLE
$sql = "INSERT INTO first_table ( lname ) VALUES ( ? )";
$ins = $mysqli->prepare($sql);
if (!$ins)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// NB: THESE VARIABLES MUST EXIST BEFORE THEY CAN BE BOUND!
$person = current($test_names_arrays);

// USE AN ITERATOR TO LOAD THE TABLE
foreach ($test_names_arrays as $person)
{
    // BIND INPUT VARIABLES TO THE QUERY
	$ins->bind_param('s', $person['lname']);

    // RUN THE QUERY TO INSERT THE ROW
    $ins->execute();

    // GET THE AUTO_INCREMENT ID FROM CONNECTION, NOT FROM STATEMENT
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>{$person['lname']}</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;

// PREPARE A SELECT-ALL QUERY
$sql = "SELECT * FROM first_table";
$res = $mysqli->query($sql);
if (!$res)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;

// SHOW THE RESULTS SET
while ($row = $res->fetch_object())
{
    print_r($row);
    echo PHP_EOL;
}

// PREPARE A SELECT-WHERE QUERY
$sql = "SELECT id, fname, lname FROM first_table WHERE (fname=? AND lname=?)";
$sel = $mysqli->prepare($sql);
if (!$sel)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// BIND INPUT VARIABLES TO THE QUERY
$fname = "";
$lname = "Paseur";
$sel->bind_param('ss', $fname, $lname);

// BIND RESULT VARIABLES TO THE QUERY
$sel->bind_result($first_id, $first_fname, $first_lname);

// RUN THE QUERY AND STORE THE RESULT
$sel->execute();
$sel->store_result();

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $sel->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;

// ACCESS AND PRINT THE RESULTS SET VIA THE BOUND RESULT VARIABLES
while ($sel->fetch())
{
    echo "id='$first_id' fname='$first_fname' lname='$first_lname'";
    echo PHP_EOL;
}

// RELEASE THE OBJECT
$sel->close();

// REPEAT THE SELECT-WHERE QUERY
$sql = "SELECT id, fname, lname FROM first_table WHERE lname=? OR id=? ";
$sel = $mysqli->prepare($sql);
if (!$sel)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// BIND INPUT VARIABLES TO THE QUERY
$lname = "Paseur";
$id    = 1;
$sel->bind_param('si', $lname, $id);

// BIND RESULT VARIABLES TO THE QUERY
$sel->bind_result($first_id, $first_fname, $first_lname);

// RUN THE QUERY AND STORE THE RESULT
$sel->execute();
$sel->store_result();

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $sel->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;

// ACCESS AND PRINT THE RESULTS SET VIA THE BOUND RESULT VARIABLES
while ($sel->fetch())
{
    echo "id='$first_id' fname='$first_fname' lname='$first_lname'";
    echo PHP_EOL;
}

// RELEASE THE OBJECT
$sel->close();




// CREATING ANOTHER TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE second_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

echo PHP_EOL . "****** LOADING SECOND TABLE (HAS NOT NULL)" . PHP_EOL;

// PREPARE A QUERY TO LOAD OUR DATA INTO THE TABLE
$sql = "INSERT INTO second_table ( lname ) VALUES ( ? )";
$ins = $mysqli->prepare($sql);
if (!$ins)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// NB: THESE VARIABLES MUST EXIST BEFORE THEY CAN BE BOUND!
$person = current($test_names_arrays);

// USE AN ITERATOR TO LOAD THE TABLE
foreach ($test_names_arrays as $person)
{
    // BIND INPUT VARIABLES TO THE QUERY
	$ins->bind_param('s', $person['lname']);

    // RUN THE QUERY TO INSERT THE ROW
    $ins->execute();

    // GET THE AUTO_INCREMENT ID FROM CONNECTION, NOT FROM STATEMENT
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>{$person['lname']}</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;

// PREPARE A SELECT-ALL QUERY
$sql = "SELECT * FROM second_table";
$res = $mysqli->query($sql);
if (!$res)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;

// SHOW THE RESULTS SET
while ($row = $res->fetch_object())
{
    print_r($row);
    echo PHP_EOL;
}

// PREPARE A SELECT-WHERE QUERY
$sql = "SELECT id, fname, lname FROM second_table WHERE (fname=? AND lname=?)";
$sel = $mysqli->prepare($sql);
if (!$sel)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// BIND INPUT VARIABLES TO THE QUERY
$fname = "";
$lname = "Paseur";
$sel->bind_param('ss', $fname, $lname);

// BIND RESULT VARIABLES TO THE QUERY
$sel->bind_result($second_id, $second_fname, $second_lname);

// RUN THE QUERY AND STORE THE RESULT
$sel->execute();
$sel->store_result();

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $sel->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;

// ACCESS AND PRINT THE RESULTS SET VIA THE BOUND RESULT VARIABLES
while ($sel->fetch())
{
    echo "id='$second_id' fname='$second_fname' lname='$second_lname'";
    echo PHP_EOL;
}

// RELEASE THE OBJECT
$sel->close();

// REPEAT THE SELECT-WHERE QUERY
$sql = "SELECT id, fname, lname FROM second_table WHERE lname=? OR id=? ";
$sel = $mysqli->prepare($sql);
if (!$sel)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// BIND INPUT VARIABLES TO THE QUERY
$lname = "Paseur";
$id    = 1;
$sel->bind_param('si', $lname, $id);

// BIND RESULT VARIABLES TO THE QUERY
$sel->bind_result($second_id, $second_fname, $second_lname);

// RUN THE QUERY AND STORE THE RESULT
$sel->execute();
$sel->store_result();

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $sel->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;

// ACCESS AND PRINT THE RESULTS SET VIA THE BOUND RESULT VARIABLES
while ($sel->fetch())
{
    echo "id='$second_id' fname='$second_fname' lname='$second_lname'";
    echo PHP_EOL;
}

// RELEASE THE OBJECT
$sel->close();

Open in new window

0
 

Author Comment

by:brucegust
ID: 40004500
Tom, thanks so much for weighing in. I had tried your suggestion, but ran into the same problem and I believe Ray has confirmed what I've seen in the past.

Ray, here's my dilemma: The recordset that I'm using is a result of a LEFT JOIN query. The NULL values are representative of a row in one of the tables that doesn't exist as opposed to a situation where I had a legitimate row with a NULL column. That being the case, changing the default value wouldn't make a dent, would it? Reason being is that the NULL value displays to indicate a record in one table that doesn't exist in the other.

My logic may be off, but if what I'm saying resonates with you, what then are my options if I can't change the default value of my column?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40004537
I would be glad to experiment with it a little bit.  Can you please give us the SSCCE?  That would be a pair of CREATE TABLE statements, a few queries to load the tables and a query representative of the LEFT JOIN.  If you use CREATE TEMPORARY TABLE you can put all of these into a single script that we can run and rerun to tinker with the settings and displays of data.
0
 
LVL 52

Assisted Solution

by:Julian Hansen
Julian Hansen earned 100 total points
ID: 40004562
How about chaning your query to
SELECT * FROM table1 t1 LEFT JOIN t2 ON t1.joinfield = t2.joinfield
WHERE overridename IS NULL;

Open in new window

So any row that has a blank in overridenname is a null value.
0
 

Author Comment

by:brucegust
ID: 40007311
Folks, thanks so much for your input. Here's what I did:

Once it became obvious that PHP and SQL weren't going to be talking to one another like I had hoped, I chose to simply run another query at that point I was needing the info and checked for an id value as opposed to a NULL value. It required a little more in the way of heavy lifting, but the end result was credible information exactly where I needed it and, like a great man used to say, "You can't argue with success."

Thanks so much!
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40007517
Thanks for the points and thanks for using EE, ~Ray
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now