Passing array into sql

PHP
1

Hello, I wanted to ask if there is a way to pass arrays into SQL queries for instance having something under this shape:

$form = array(
first_name=>$first_name,
last_name=>$last_name,
gender=>$gender);

and so on passed to an insert statement.What I want exactly to do is somehow to be able to use the column,value and table name of the insert into sql statement as parameters of a php function so i can use it in my php code and thought to do so with arrays although I do not know if this is the way to solve my issue I’d like to be helped figuring this out. Thank you for your attention to this matter!

2

Which extension are you using to execute your queries, mysql, mysqli, or PDO?

3

If you are using something like Doctrine it would be as simple as:

$db->insert('table', $array)

However if you use something like PDO or mysql(i) you would need to loop through the array to build the sql.


foreach ($array as $column => $value)
{
    $columns[] = $colums;
    $values[] = $value;
}

'INSERT INTO table (' . implode(', ', $columns) . ')  VALUES (' . implode(', ', $values) . ')'

If you go for the latter you will need to wrap non integer values in quotes.

4

Why do I need to loop through it, and yes I am using mysqli to answer the previous questions

5

You wouldn’t :stuck_out_tongue: That is just me using a convoluted method. You could just use array_keys and array_values

'INSERT INTO table (' . implode(', ', array_keys($form)) . ') VALUES (' . implode(', ', array_values($form)) . ')'
6

Using prepare:


// assume: $array assoc array of data to be inserter
// assume: $db instance of PDO
$placeholders = substr(str_repeat('?,', count($array)), 0, -1);
$stmt = $db->prepare('INSERT INTO ('.implode(',', array_keys($array)).') VALUES ('.$placeholders.')');
$stmt->execute(array_values($array));