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:

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.

ParkinT
—
2011-09-12T12:15:58Z —
#2

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.

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).

StarLion
—
2011-09-12T14:02:59Z —
#5

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...

ahundiak
—
2011-09-12T14:21:22Z —
#6

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

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

StarLion
—
2011-09-12T14:32:15Z —
#7

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

ahundiak
—
2011-09-12T14:44:38Z —
#8

StarLion said:

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.