Building SQL string based on keys and values

PHP
1

Hey everyone,
I’m trying to figure out how to construct an SQL INSERT query using keys and values from an array.

The array containing the info to insert into the DB:
$array[‘info1’] = “Info 1”;
$array[‘info2’] = “Info 2”;
$array[‘info3’] = “Info 3”;

I want the query to be 100% dependent on the individual KEYS of the array to specify the table’s fields, and the VALUES to specify the field’s values.

Essentially: INSERT INTO table (info1, info2, info3) <----These are the KEYS of the array
VALUES (‘Info 1’, ‘Info 2’, ‘Info 3’) <---- These are the VALUES of the KEYS

I need it to be dynamic so I can change the number of items in the array and the query string will accommodate for it. I’m pretty sure it’s possible but I’m not quite sure how to work the loops through the array that will be needed.

Any thoughts?

2

Use a foreach to loop through the array, and create a column names array and a values array.
Then create the query string like this:


$query =  "
  INSERT INTO table (" . implode(",", $columns) . " 
  VALUES ('" . implode("','", $values) . "')
";
3

Thank you for your reply. I will try it out!

4

Why not use array_keys & array_values?

Example:


$array = array();
$array['info1'] = "Info 1";
$array['info2'] = "Info 2";
$array['info3'] = "Info 3";

$columns = array_keys($array);
$values = array_values($array);

$query =  "
	INSERT INTO table (" . implode(", ", $columns) . ") 
	VALUES ('" . implode("', '", $values) . "')
";
5

Much better :slight_smile:

6

Not sure if this is relevant to the OP, but if $array is actually $_POST, then remove any unwanted POST values like “submit” or whatever with unset().

Having said that, if it was a POST array I would have expected to see mysql_real_escape_string() in there somewhere… oh well, I’ll get my coat.

7

Definitely a good solution! Thanks for the replied everyone.