how to update infinite amount of rows in 1 query?

[Monkuar]

 

My code:

 

$rank =  implode(',', $ibforums->input['rank']);
		echo $rank;

 

For that form, it will show:

 

'2,0,0'

 

 

Problem is, I need to update each row with each individual value, and is this possible with using  1 query?

[PFMaBiSmAd]

AFAIK to do it in one query, you would need to dynamically produce the query statement using CASE logic (untested) -

 

UPDATE your_table SET rank = CASE WHEN friend = 'shiskabob' THEN 2 WHEN friend = 'test2' THEN 0 WHEN friend = 'monkey' THEN 0 END WHERE id = x

 

[Monkuar]

AFAIK to do it in one query, you would need to dynamically produce the query statement using CASE logic (untested) -

 

UPDATE your_table SET rank = CASE WHEN friend = 'shiskabob' THEN 2 WHEN friend = 'test2' THEN 0 WHEN friend = 'monkey' THEN 0 END WHERE id = x

 

hmm sounds mysql intensive

 

do u recommend me just creating a button for a user to click to auto move the rank up/down each time? seems better and better performance? do u agree or no?

[scootstah]

AFAIK to do it in one query, you would need to dynamically produce the query statement using CASE logic (untested) -

 

UPDATE your_table SET rank = CASE WHEN friend = 'shiskabob' THEN 2 WHEN friend = 'test2' THEN 0 WHEN friend = 'monkey' THEN 0 END WHERE id = x

 

hmm sounds mysql intensive

 

do u recommend me just creating a button for a user to click to auto move the rank up/down each time? seems better and better performance? do u agree or no?

 

MySQL won't even bat an eyelash at that.

 

You could also just use multiple queries. In this case it won't hurt anything.

[Monkuar]

AFAIK to do it in one query, you would need to dynamically produce the query statement using CASE logic (untested) -

 

UPDATE your_table SET rank = CASE WHEN friend = 'shiskabob' THEN 2 WHEN friend = 'test2' THEN 0 WHEN friend = 'monkey' THEN 0 END WHERE id = x

 

hmm sounds mysql intensive

 

do u recommend me just creating a button for a user to click to auto move the rank up/down each time? seems better and better performance? do u agree or no?

 

MySQL won't even bat an eyelash at that.

 

You could also just use multiple queries. In this case it won't hurt anything.

 

could you help me run a foreach then? if i could do that with my query, even if somone selcted let's say 10 checkboxes, it will run 10 update queries, but it wont effect performance? if so that is great news, if you could help me

[kicken]

could you help me run a foreach then? if i could do that with my query, even if somone selcted let's say 10 checkboxes, it will run 10 update queries, but it wont effect performance? if so that is great news, if you could help me

 

More queries will be more time, always, but you probably wont notice a difference til you start doing several hundred in a loop.  I had a script at one point that updated users achievement data and it was doing anywhere between 700-1500 updates in a loop.  Rewriting it do a single query using a giant case statement made a noticeable increase in performance.

 

As for setting up your loop, that is simple.  Just make a standard foreach loop to go through your boxes.  In that loop, create and run your query.

foreach ($ibforums->input['rank'] as $rank){
   $sql = 'UPDATE ....';
   //run query $sql;
}