I'm trying to write a PHP function that will generate dynamic MySQL queries along the following lines:
sprintf("INSERT INTO %s VALUES ($arbitrary_length_type_list)", $arbitrary_length_argument_list);
I've succeeded in writing a short routine that will generate my $arbitrary_length_type_list. It looks something like this:
$type_list = '';
$db_map = array('table_1' => array('record_id', 'foreign_key_id', 'string_value_1', 'string_value_2'),
'table_2' = array('record_id', 'value_1', 'value_2'));
$table = 'table_1';
foreach($db_map[$table] as $arg){
if(substr_compare($arg, '_id', -3, 3) == 0){
$type_list.= ', %d';
}else{
$type_list.= ', \'%s\'';
}
}
sprintf("INSERT INTO %s VALUES ($type_list)", $argument_list);
My difficulty is with the $argument_list for sprintf() (referred to as $arbitrary_length_argument_list in my first code sample), which cannot be a single string or array, but must be individual comma-separated variables or values. Is there a way to take an array or string and format it into a comma-separated list of variables or values that will be accepted by sprintf()?
To clarify, here's what I want the above routine to output:
sprintf("INSERT into %s VALUES (%d, %d, '%s', '%s')", $table, $record_id, $foreign_key_id, $string_value_1, $string_value_2);
Or, alternatively, instead of variable names, the actual values, like so:
sprintf("INSERT into %s VALUES (%d, %d, '%s', '%s')", 'table_1', NULL, 1, 'string_value_1', 'string_value_2');
I can't use implode() to create the list of arguments ('table_1', NULL, 1, 'string_value_1', 'string_value_2'), because that function generates a single string and sprintf() won't accept the following:
sprintf("INSERT into %s VALUES (%d, %d, '%s', '%s')", 'table_1, NULL, 1, string_value_1, string_value_2');
[note different positions of the quotes]
or this:
sprintf("INSERT into %s VALUES (%d, %d, '%s', '%s')", array('table_1', NULL, 1, 'string_value_1', 'string_value_2'));
In both cases it gives me an "incorrect number of arguments" error.
This also doesn't work:
$args = $db_map['table_1'];
sprintf("INSERT into %s VALUES (%d, %d, '%s', '%s')", function(){foreach($args as $k => $v){${$k} = $v;}});
In the last case the error is "closure could not be converted to string".
Maybe someone more experienced than myself has a solution?
