Selecting Rows Where a Field Is Null Using PHP Pdo Prepared Statements and MySQL

Selecting rows where a field is null using PHP PDO prepared statements and MySQL

Since this question has been written, mysql introduced a spaceship operator that allows us to use a regular query to match a null value

WHERE fieldName <=> :fieldName;

will match both a null or any not null value.

So just write your query right away and execute it as usual

$stmt = $db->prepare('SELECT field FROM table WHERE fieldName <=> :fieldName;');
$stmt->execute(['fieldName' => null]);
$result = $stmt->fetchAll(); // whatever fetch method is suitable

And with dynamically built queries it's all the same.

PHP PDO, excluding row with null field value

Simple add check before printing rows. Something like following.

while($ft_form_2 = $stmt->fetch()) // loop and display data
{
    if( !empty($ft_form_2->empire_name) && !is_null($ft_form_2->empire_name) )
    {
        $print .= '<tr>';
        $print .= "<td>{$ft_form_2->empire_name}</td>";
        $print .= "<td>{$ft_form_2->win}</td>";
        $print .= "<td>{$ft_form_2->building_1} <br> {$ft_form_2->building_1_notes} </td>";
        $print .= "<td>{$ft_form_2->building_2} <br> {$ft_form_2->building_2_notes} </td>";
        $print .= "<td>{$ft_form_2->building_3} <br> {$ft_form_2->building_3_notes} </td>";
        $print .= "<td>{$ft_form_2->building_4} <br> {$ft_form_2->building_4_notes} </td>";
        $print .= "<td>{$ft_form_2->building_5} <br> {$ft_form_2->building_5_notes} </td>";
        $print .= "<td>{$ft_form_2->building_6} <br> {$ft_form_2->building_6_notes} </td>";
        $print .= "<td>{$ft_form_2->building_7} <br> {$ft_form_2->building_7_notes} </td>";
        $print .= "<td>{$ft_form_2->building_8} <br> {$ft_form_2->building_8_notes} </td>";
        $print .= "<td>{$ft_form_2->building_9} <br> {$ft_form_2->building_9_notes} </td>";
        $print .= '</tr>';
    }
}

Or

You can add WHERE cause in your query. Like following

 WHERE empire_name IS NOT NULL AND empire != ''

PHP PDO check if return is null

Try to use is_null or === operator. Such as

if(!is_null($row['name']))

or

if($row['name'] !== NULL))

SELECT from database when i don´t know position of null values

To match exactly rows in your table, you can use NULLIF() to convert your PHP empty strings to SQL NULL values and NULL-safe operator to compare NULL to them without NULL result.

Ex. :

$sql = <<<SQL
    SELECT id 
    FROM adress 
    WHERE TRUE 
        AND street <=> NULLIF({$pdo->quote($street)}, '')
        AND house_number <=> NULLIF({$pdo->quote($houseNum)}, '')
        AND city <=> NULLIF({$pdo->quote($city)}, '')
        AND district <=> NULLIF({$pdo->quote($city)}, '')
    SQL;

PDO MySQL prepared statement with optional where statements

The execute() method does not return a results object. It returns a boolean, true or false.

fetchAll() is a method of the PDOStatement object. Here's an example:

$sth = $dbh->prepare($sql);
$sth->execute();    
$result = $sth->fetchAll();

PHP mysql PDO refuses to set NULL value

NULL values do not require any special treatment. Simply bind your value the usual way

<?php

$pdo = new PDO("mysql:host=localhost;dbname=test", "root", "pass");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$stmt = $pdo->prepare("INSERT INTO `null_test` (`can_be_null`) VALUES (:null)");
$stmt->bindValue(":null", null, PDO::PARAM_STR);

$stmt->execute();

PDO will detect a null value and send it to database instead of string.

PDO prepared statement seems to ignore HAVING clause

I believe this behavior is dependent on the RDBMS being used.

In the absence of the GROUP BY clause, it seems that in some circumstances, the entire result can be considered as "one group". Because one row in the results satisfies the HAVING condition, all shall pass.

Additional reading:

Use of HAVING without GROUP BY in SQL queries

HAVING without GROUP BY

p.s. I don't think the > 0 is necessary.

I suppose I'd write your query something like this:

SELECT tag,
    @t := '1,4' AS temp_var,
    1 AS is_match
FROM tagged
WHERE @t IS NULL OR FIND_IN_SET(tag, @t)
LIMIT 150;

Multiple rows update - update column NULL if blank field

You're testing the old shippedto_customer in the table, not the value from the form.

You can use the NULLIF() function to test if the value being stored is an empty string, and store NULL instead.

if(isset($_POST['Submit']))
{
    $count=count($_POST["id"]);

    for($i=0;$i<$count;$i++){
        $status = mysql_real_escape_string($_POST['status'][$i]);
        $ship_from_factory = mysql_real_escape_string($_POST['ship_from_factory'][$i]);
        $shipped_to_customer = mysql_real_escape_string($_POST['shippedto_customer'][$i]);
        }
        $ship_comments = mysql_real_escape_string($_POST['ship_comments'][$i]);
        $id = mysql_real_escape_string($_POST['id'][$i]);
        $sql1="UPDATE $tbl_name
               SET status='$staus', ship_from_factory='$ship_from_factory', 
                   shippedto_customer=NULLIF('$shipped_to_customer', ''), 
                   ship_comments='$ship_comments' WHERE id='$id'";
        $result1=mysql_query($sql1);
    }
}

If you're forced to use the old mysql extension, you need to escape all the parameters. I've shown that above. But as mentioned in the comments, you should migrate to a modern MySQL API (I recomment PDO) and use prepared statements. If you do this, a PHP null value will be converted automatically to SQL NULL when used as a parameter.

---

Related Topics