CODEWITHSUNDEEP
phpmysql
EastsideDev
EastsideDev

Reputation: 6659

Passing NULL to a function designed to build a MySQL query in PHP

Language: PHP and MySQL

I have some rows in a database that have a column with a value of NULL. I have a function that builds the query when it receive a $params array. Here's the relevant piece of code in that function (to build the WHERE clause):

    if (isset($params['where'])) {
        $query .= "WHERE ";
        $count = count($params['where']);
        foreach ($params['where'] as $key => $value) {
            $count--;
            if ($count) {
                $query .= "`$key` ".$value['sign']." '".$value['text']."' AND ";
            } else {
               $query .= "`$key` ".$value['sign']." '".$value['text']."' ";
            }
        }
    }

The problem is that when I set $value['text'] to null, here's the result:

SELECT * FROM `db_profiles` WHERE `committed` IS '' LIMIT 100

If I set it to $value['text'] = 'NULL', then I get the following:

SELECT * FROM `db_profiles` WHERE `committed` IS 'NULL' LIMIT 100

Which of course produces nothing. Any ideas?

ANSWER

    if (isset($params['where'])) {
        $where_segment = array ();
        foreach ($params['where'] as $key => $value) {
            if (is_null($value['text'])) {
                $where_segment[] = "`".$key."`"." IS NULL";
            } else {
                $where_segment[] = "`$key` ".$value['sign']." '".$value['text']."' ";
            }
        }
        $query .= " WHERE ".implode('AND', $where_segment);
    }

Upvotes: 0

Views: 140

Answers (4)

Hard worker
Hard worker

Reputation: 4066

  $string_array = array();

    foreach($params['where'] as $field => $value) {

        if(is_null($value)) {
            $string_array[] = "`$field` IS NULL";
        }
        else {
            $string_array[] = "`$field`='$value'";
        }

    }

 $where_string = "WHERE ".implode(" AND ", $string_array);


 $sql = "SELECT * FROM `db_profiles` $where_string";

Notice that if the $value is NULL I omit the '' marks around the NULL phrase.

Upvotes: 3

echo_Me
echo_Me

Reputation: 37243

this should help you without single quotes 

 SELECT * FROM `db_profiles` WHERE `committed` IS NULL LIMIT 100

you could set your variable like that to null

 $value['text'] = NULL;

and then in your query

 SELECT * FROM `db_profiles` WHERE `committed` IS $value['text'] LIMIT 100

Upvotes: 0

hek2mgl
hek2mgl

Reputation: 158230

When populating the values to the query, you'll need an explicit check for something like:

if(is_null($value)) {
    $query . ' WHERE something IS NULL';
} else {
    $query . ' WHERE something \'' . $value . '\'';
}

Note that when you are new to MySQL and PHP you should start to use prepared statements as soon as possible. They are most secure against SQL injection vulnerabilities and easy to use. You can start here to learn to use them.

Upvotes: 0

user2003356
user2003356

Reputation: 455

Please use this query.

SELECT * FROM db_profiles WHERE committed IS NULL LIMIT 100.

Remove the single quotes.

Upvotes: 0

Related Questions