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
– MR.Internet, Commented Sep 11, 2013 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
– STT LCU, Commented Sep 11, 2013 at 14:36
You will need to prepare your statement. See if this helps: stackoverflow.com/questions/5329542/… — Vigintas Labakojis
– Vigintas Labakojis, Commented Sep 11, 2013 at 14:39

Christoph Diegelmann · Accepted Answer · 2013-09-11 14:59:17Z

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, 2013 at 14:59

answered Sep 11, 2013 at 14:42

Christoph Diegelmann

2,03416 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MR.Internet Over a year ago

that is the echo from query.....INSERT INTO table VALUES('','','','','NULL','NULL','NULL','') as you see all the value are lost....some thing wrong?

Christoph Diegelmann Over a year ago

@MR.Test probably your php version doesn't include mysql_real_escape_string() another reason to use prepared statements.

Christoph Diegelmann Over a year ago

@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.

Sriram G · Accepted Answer · 2013-09-11 14:53:55Z

1

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

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

answered Sep 11, 2013 at 14:53

Sriram G

4094 silver badges14 bronze badges

Collectives™ on Stack Overflow

Associative array insert to Mysql

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related