current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question
1  
I can’t see why a variable number of items is a problem – you know that loop constructs exist, right? –  CBroe Apr 13 '13 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 Apr 13 '13 at 14:41
    
Being inexperienced warrants downvotes? Thanks for the insight. –  David Apr 13 '13 at 15:03
    
I want to generate a single query, as explained. I don't want to generate a query foreach array value. –  David Apr 13 '13 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 Apr 13 '13 at 15:53

1 Answer 1

up vote 1 down vote accepted

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);
share|improve this answer
    
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. –  David Apr 13 '13 at 22:16
    
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. –  Peter Featherstone Apr 14 '13 at 0:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.