Associative array insert to Mysql

Question

I have the following array...

Array
(
    [ID] => 10
    [code] => KA
    [rol] => B
    [pr] => 
)

what I want is when I insert to the MySQL all the empty array key filled with NULL...

here is what I tried...

foreach ($array as $key => $value) {
    $value = trim($value);
    if (empty($value))
        $value .= NULL;

    else
        echo $value;
    }

or in different way...like this..

$value = implode("', '",array_values($array));
$val = ($value == ' ') ? NULL : "$value";

and insert to table..

$sql = "INSERT INTO table VALUES('$val')";

But it seems I am not getting the NULL value in my fields... what did I do wrong?

In short, how do I add Null to the empty array key...[pr]???

yes,...That is the problem? If I don't have NULL in the empty field it crushes.. — MR.Internet, Sep 11 '13 at 14:36
first step: learn what's PDO. Second step: learn what are prepared statements in PDO. Third step: ????????. Fourth step: profit! — STT LCU, Sep 11 '13 at 14:36
You will need to prepare your statement. See if this helps: http://stackoverflow.com/questions/5329542/php-mysql-insert-null-values — Vigintas Labakojis, Sep 11 '13 at 14:39

Christoph Diegelmann · Accepted Answer · 2013-09-11T14:59:17.730

1

You could do:

$sql = array();
foreach ($array as $key => $value) {
    $value = trim($value);
    $sql[] = empty($value)?'NULL':"'".addslashes($value)."'";
}
$sql = 'INSERT INTO table VALUES('.implode(",", $sql).')';

echo $value; will not help you if you want to use it as a query.

But: Don't do it! Use prepared statements instead.

Example: http://codepad.org/lZfgVwIL

edited Sep 11 '13 at 14:59

answered Sep 11 '13 at 14:42

Christoph Diegelmann

2,004
15
26

that is the echo from query.....INSERT INTO table VALUES('','','','','NULL','NULL','NULL','') as you see all the value are lost....some thing wrong? – MR.Internet Sep 11 '13 at 14:55
@MR.Test probably your php version doesn't include mysql_real_escape_string() another reason to use prepared statements. – Christoph Diegelmann Sep 11 '13 at 15:00
@MR.Test Edited my post and created an example on codepad. Should work now. If you still need this than your using prepared statements the wrong way. They are meant to seperate query and data. – Christoph Diegelmann Sep 11 '13 at 15:02

score 1 · Answer 2 · answered Sep 11 '13 at 14:53

1

I think you should use double quotes 'NULL' or 'null' instead of NULL.

$val = ($value == ' ') ? 'NULL' : $value;

answered Sep 11 '13 at 14:53

Sriram G

369
3
14

Associative array insert to Mysql

2 Answers2