associative array into a database.

[black-ops]

I am having a problem of inserting an associative array into a database.
For example I want to replicate

INSERT INTO table (value, value2, value3) VALUES ("1", "2", "3");

however with an associative array I cannot do this it seems to enter it into a database on multiple rows as opposed to one row.

I've tried doing

foreach($array as $k => $v){
mysql_query("INSERT INTO table ({$k}) VALUES ({$v})");
}

Any ideas?

 

[Inquisitor]

This is because you're executing the INSERT query once for each element of the array, so you're getting:

INSERT INTO table (value1) VALUES (1)
INSERT INTO table (value2) VALUES (2)
INSERT INTO table (value3) VALUES (3)

So it's no surprise that it's creating three separate rows. What you need to do is construct the query string itself from the array, for example:

$columns = implode(', ', array_keys($array));
$values = implode(', ', $array);
mysql_query("INSERT INTO table ($columns) VALUES ($values)");

Incidentally, I notice that you're not quoting or escaping anything; have you ensured that the values and column names that you're using won't cause syntax problems?

 

[black-ops]

This is because you're executing the INSERT query once for each element of the array, so you're getting:

INSERT INTO table (value1) VALUES (1)
INSERT INTO table (value2) VALUES (2)
INSERT INTO table (value3) VALUES (3)

So it's no surprise that it's creating three separate rows. What you need to do is construct the query string itself from the array, for example:

$columns = implode(', ', array_keys($array));
$values = implode(', ', $array);
mysql_query("INSERT INTO table ($columns) VALUES ($values)");

Incidentally, I notice that you're not quoting or escaping anything; have you ensured that the values and column names that you're using won't cause syntax problems?

It's nothing to do with the syntax I think, anyway; yeh your solution has worked a treat, definitely helps. I was overlooking the foreach structure and presuming it would carry out the insert for the whole row for each part of the array.

 

[Inquisitor]

It's nothing to do with the syntax I think

No, but unless you're sure that the values you're using are either escaped already or are very simple (e.g. integers), you should always escape and quote them. Not doing so will cause problems.

 

[black-ops]

No, but unless you're sure that the values you're using are either escaped already or are very simple (e.g. integers), you should always escape and quote them. Not doing so will cause problems.

Sound advice, I guess it's the little bad habit I have picked up along the way haha. Any who, I got it working using the method you suggested, honestly never used that before even though I knew of it, thanks a lot for that and will try and remember about escaping in the future.

Regards,

 

[Tetsujin]

I'll just add that the function you want to be using for escaping is mysql_real_escape_string

 

[Inquisitor]

Or, ideally, you should use some form of abstraction layer, like PDO, which allows for prepared/parametrized queries, which absolve you of any responsibility for data sanitization.