Use php array to generate a single MySQL INSERT query

Question

I have a dynamically generated table listing all users in a given group, and each user has a checkbox next to their name. The group admin may select/deselect these checkboxes to include/exclude a user. In the MySQL table, there is a status column for each user that is either a 0 or a 1. I use hidden values to handle the unchecked status of boxes and POST the data as an array with each user's unique ID as the key.

So my POST data is an array that looks thus:

Array ( [105] => 1 [106] => 1 [107] => 0 [108] => 0 [109] => 1 )

Now, I need to update the db. Here is how I want the query to look:

INSERT INTO users (uid,status) VALUES (105,1),(106,1),(107,0),(108,0),(109,1)
ON DUPLICATE KEY UPDATE status=VALUES(status);

Of course, that doesn't INSERT anything, but it updates every row with a single query.

What I can't figure out is how to use the array to generate the query. Especially given that the number of users (the number of items in the array) will vary.

After additional research, it seems that my question may boil down to:

Is it possible to use implode to turn this array:

Array ( [105] => 1 [106] => 1 [107] => 0 [108] => 0 [109] => 1 )

into this string:

(105,1),(106,1),(107,0),(108,0),(109,1)

with the flexibility to have more or fewer items in the array?

I can’t see why a variable number of items is a problem – you know that loop constructs exist, right? — C3roe
– C3roe, Commented Apr 13, 2013 at 14:38
Just avoid putting a , at then end of the values by checking if loop_index + 1 equals the length of the array. — plalx
– plalx, Commented Apr 13, 2013 at 14:41
Being inexperienced warrants downvotes? Thanks for the insight. — David
– David, Commented Apr 13, 2013 at 15:03
I want to generate a single query, as explained. I don't want to generate a query foreach array value. — David
– David, Commented Apr 13, 2013 at 15:05
So, the task is to generate a simple string out if simple array. What have you tried and what problem you struck into? — Your Common Sense
– Your Common Sense, Commented Apr 13, 2013 at 15:53

Peter Featherstone · Accepted Answer · 2013-04-13 20:20:09Z

3

Using a foreach loop you could turn your array in to a string as so:

foreach( $array as $key => $value ) {

$queryString .= "($key,$value), ";

}

You could then take the end comma off your query either using rtrim() as so:

rtrim( $queryString, "," )

or by setting up some count function inside your foreach loop to only append a comma when it's not the last item in your array.

Your final query would then look like this:

INSERT INTO users (uid,status) VALUES $queryString ON DUPLICATE KEY UPDATE status=VALUES(status);

answered Apr 13, 2013 at 20:20

Peter Featherstone

8,1326 gold badges37 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Over a year ago

Not as elegant as I was imagining, but it's functional; that's what really matters. Thanks for being helpful to an ignorant guy rather than commenting on how evident is my ignorance.

Peter Featherstone Over a year ago

Sorry I couldn't provide you with something more elegant but it is really only 4 lines of code max. No need to thank me, I was the ignorant guy once so I know what it's like, always happy to help.

Collectives™ on Stack Overflow

Use php array to generate a single MySQL INSERT query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related