PHP Array to Tuple (Part 2): Conserving Null Values

rm_-rf_windows · 04-24-2012, 12:27 PM

Hi all,

This is the sequel of another thread which has been marked solved (hence Part 2, click here for initial thread). In the first thread, kelso.b and Guttorm kindly wrote some simple functions that allow you to insert data into a db using an associative array. It works like a charm, but I've encountered a small problem when it comes to NULL values.

If a value defaults as NULL in the database (previous value before executing the function), when running a query using the function, php NULL values don't insert into the db as NULL values but are replaced (I think) by empty strings (nothing appears when running a query in a MySQL terminal, whereas NULL would have appeared prior to the insertion).

Here's the function (a variation on the initial code in Part 1):

Code:

    public static function arrayToMysqlTupleUpdate($array, $table_name, $id) {
        $sql = "UPDATE `$table_name` SET ";
        $first = true;
        foreach ($array as $key => $value) {
            if ($first) {
                $first = false;
            } else {
                $sql .= ",";
            }
            $sql .= "`$key` = '" . mysql_real_escape_string($value) . "'";
            
        }
        $sql = $sql .  "WHERE id = $id";
        return mysql_query($sql);
    }

Now I've tried to insert an "if $value != NULL" condition (among others), however it simply doesn't work! NULL values in php do not seem to be the equivalent to NULL values in MySQL. I just don't understand.

That's my main question.

The "post script" part of this message concerns the second version provided by kelso.b. I simply couldn't get it to work:

Code:

function array_to_mysql_tuple($array, $table_name) {     array_walk($array, 'mysql_real_escape_string');     $sql = "INSERT INTO `$table_name` (`%s`) VALUES ('%s')";     return sprintf($sql, implode('`, `', array_keys($array)), implode("', '", $array)); }

Otherwise, this arrayToTuple function is really cool! I use array_merge() between functions to organize my tuple and then just send.

Thanks in advance,

rm

Guttorm · 04-25-2012, 07:15 AM

Yes PHP null values will be converted to empty strings. Here's a modification so PHP null is converted to SQL NULL.

PHP Code:



public static function arrayToMysqlTupleUpdate($array, $table_name, $id) {
        $sql = "UPDATE `$table_name` SET ";
        $first = true;
        foreach ($array as $key => $value) {
            if ($first) {
                $first = false;
            } else {
                $sql .= ",";
            }
            if (is_null($value)) {
                $sql .= "`$key` = NULL";
            } else {
                $sql .= "`$key` = '" . mysql_real_escape_string($value) . "'";
            } 
        }
        $sql = $sql .  " WHERE id = $id";
        return mysql_query($sql);
    }

rm_-rf_windows · 04-25-2012, 09:54 AM

Many thanks,

I just figured it out. It was very simple, I was tired yesterday when I got to that part of my application and just couldn't think anymore.

Here's what I came up with (and then I saw your post, after):

Code:

    public static function arrayToMysqlTupleUpdate($array, $table_name, $id) {
        $sql = "UPDATE `$table_name` SET ";
        $first = true;
        foreach ($array as $key => $value) {
            if ($first) {
                $first = false;
                $sql .= "`$key` = '" . mysql_real_escape_string($value) . "'";
                } else if($value != ""){
                $sql .= ",";
                $sql .= " `$key` = '" . mysql_real_escape_string($value) . "'";
            }            
        }
        $sql = $sql .  " WHERE id = $id";
        //echo $sql;
        return mysql_query($sql);
    }

But your is_null use is better. I could encounter a real empty string which is not NULL, but an actual empty string.

Many thanks!

rm