updating mutiple rows at the same time

[friedice]

is there a way to do it?

 

lets say in one form  i select 3 different rows from a table and want to save them in the database for each one as "yes"  in a selected column

and the rest of the rows to be as "no" or blank

 

so its like this first

ID      product              selected

1       apple                  yes
2       banana               no
3       pear                    yes
4       orange                yes
5       plum                    no

and then user selects 3 again and it updates it again like

 

ID      product              selected

1       apple                  no
2       banana               no
3       pear                    yes
4       orange                yes
5       plum                    yes

the reason y i want to do this is because another page wants to access only the rows that have "yes" as being selected each time it loads dynamically

 

 

i was thinkin use a seperate update for each selected ID so 3 update statements

but if the user changed it, the old ones would still be saved as "yes " in the db >.<

thanks

[optikalefx]

Knowing the primary keys of each row you want to update - you can use REPLACE to do multiple Updates just like you would use INSERT to do multile inserts.

REPLACE INTO table (primaryKeyColumn, col2, col3) VALUES ('primaryKeyValue','val2','val3')

[friedice]

ugh i forgot bout using replace >.<

bbut would that work for my case?

like that table can keep gettin more entries as more time goes by

would using replace still replace the ones u want using the ID primary key and also modify ALL others to change it to blank or no?

[friedice]

anyone?

[friedice]

i decided to keep using updates statements since i cant seem to get a working replace statement

$q1 = "UPDATE Events SET HPselected = 'yes' WHERE ID = '{$eID}'";
$mdb2->exec($q1);
$q2 = "UPDATE Events SET HPselected = 'yes' WHERE ID = '{$eID2}'";
$mdb2->exec($q2);
$q3 = "UPDATE Events SET HPselected = 'yes' WHERE ID = '{$eID3}'";
$mdb2->exec($q3);

$q4 = "UPDATE Events SET HPselected = 'no' WHERE ID != '{$eID}' OR ID != '{$eID2}' OR ID != '{$eID3}'";
$mdb2->exec($q4);

 

the first 3 changes all events ids to yes when it matches the $eIDs values so thats fine

the only problem now is that if it doesnt match any of them , i want it to update all other rows with a "no"

anyone know wats wrong with it?

 

[friedice]

anyone?

[optikalefx]

replace would replace all of them. Which should be fine for your case.

 

Paste the code you couldn't get to work.

[DavidAM]

You could simplify this code and use fewer queries with:

 

$q1 = "UPDATE Events SET HPselected = 'yes' WHERE ID IN ( '{$eID}', '{$eID2}', '{$eID3}')";
$mdb2->exec($q1);

$q4 = "UPDATE Events SET HPselected = 'no' WHERE ID NOT IN ( '{$eID}', '{$eID2}', '{$eID3}' )";
$mdb2->exec($q4);

 

You could actually do it in a single query:

 

$q1 = "UPDATE Events SET HPselected = CASE(ID IN ( '{$eID}', '{$eID2}', '{$eID3}'), 'yes' , 'no')";
$mdb2->exec($q1);

[optikalefx]

Couldn't you use IF as well?

 

$q1 = "UPDATE Events SET HPselected = IF(ID IN ( '{$eID}', '{$eID2}', '{$eID3}'), 'yes' , 'no')";

[friedice]

thx optikaefx

it works now

 

the IF statement worked

for some reason the CASE one didnt

 

hopefully i dont have to adjust this functionality

[optikalefx]

sure np.  A little extra tip.  For yes or no type storage, you should really use TINYINT(1) and store 1 or 0 instad of yes or no.  Your database will be smaller and all your code will be simpler.

 

Instead of checking WHERE blah ='yes'  you will be able to just do WHERE blah. 

and if you want to display yes or no, you can do that with SELECT IF(blah,'yes','no') as blah