Turning an array into (part of) a MySQL query string

I frequently need to turn a PHP array such as:


$cols = array ( [access] => N [card] => N [child] => Y [cot] => Y [dinner] => N [ground] => Y [allyear] => Y [pets] => N [wifi] => Y )
 // derived from 'print_r', I'm aware this is not how one would construct the array

into a string (part of a query string for MySQL):


$cols_new = "access = 'N', card = 'N', child = 'Y', cot = 'Y', dinner = 'N', ground = 'Y', allyear = 'Y', pets = 'N', wifi = 'Y'"

I’ve done it with a FOREACH loop, but I’m just curious to know if there’s a better way (e.g. an array function I’ve missed).
Implode just gives me a string of the values. “N, N, Y, Y, N, Y, Y, N, Y”. I’ve wondered about ‘Array_walk’ but even if it were to work there will be at least as many lines of code as with the FOREACH loop:

	$cols_new = '';
	foreach ($cols as $key => $value) {
		$cols_new .= $key . " = '" . $value . "', ";
	}
	$cols_new = substr($cols_new, 0, -2);  // remove trailing comma and space

I am using this procedure quite freqently, so if there’s a better/shorter way it could be handy when I come to anonymise it.
In short what I’m looking for is:

$cols_new = someExistingPHPFunction($cols);

Failing which I’ll have to write my own based on what I’ve done so far.

I think IMPLODE() is exactly what you need. The resultant string can become the VALUES() part of a SQL query.
If I recall correctly, you can also apply IMPLODE() to an array of just keys and get the SET() part of your SQL statement also.

[FPHP]implode[/FPHP] the [FPHP]array_keys[/FPHP] for the field names, and implode the [FPHP]array_values[/FPHP] for the…well, values.

Thanks for your replies

The only syntax I know for UPDATE is “UPDATE tablename SET column1 = ‘value1’, column2 = ‘value2’,… WHERE…”. This means I need a string “column1 = ‘value1’, column2 = ‘value2’,…” which I’m currently getting via my FOREACH.
Implode just gives me a comma separated string of values, which isn’t what I need here.

I think your suggestion would work for INSERT, with the syntax “INSERT INTO tablename (column1, column2,…) VALUES (‘value1’, value2’,…) WHERE…” (grouping all the column names and then all the values).

Sorry, yes. For an UPDATE query this wont work. A most frustrating thing.
Foreach is pretty much the best way to do it. You could probably get there using some complex series of array functions and implodes, but…

Prepared statements makes it a bit easier since you don’t have to worry about quotes and escaping.


  function update($tableName,$keyName,$data)
  {
    // Build set list
    $set = '';
    foreach(array_keys($data) as $name)
    {
      if ($name != $keyName)
      {
        if ($set) $set .= ', ';
        $set .= $name . ' = :' . $name;
      }
    }
    $sql = "UPDATE {$tableName}\
SET {$set}\
WHERE {$keyName} = :{$keyName};";

    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($data);


But with a few mods you could stick the values directly into the sql statement.

I’m not sure how that eliminates the need for a foreach loop when it … uses… a foreach loop…itself?

The example posted does not. I edited the post accordingly. The post provides a more or less general solution for doing a sql update of one record.

However, depending on how the app is organized, you could write one sql statement for each table
$sql = ‘UPDATE xxx SET col1 = :value1, col2 = :value2 WHERE …’;
After which you could just pass your array of values to the execute statement. No foreach required.