[SOLVED] Combine and Optimize MYSQL UPDATE with PHP

[mikenl]

Hi,

 

I want to set all fields in a MySQL row to the default value (0) and update the row with new values.

 

I now use 2 queries and I'm curious if these can be combined and/or optimized?

 

// set prof_pref to 0

$setfetish0 = "UPDATE prof_pref SET ";

for ($N=1; $N<=12; $N++) 
    {     
$setpref0 = $setpref0 . "pref".$N." = '0'";
if($N != 12){
	$setpref0 = $setpref0 . ", ";
	} 
  } 	

$setpref0 = $setpref0 . " WHERE unid = '$session_id'";

mysql_query($setpref0 );


// update prof_pref 

$pref= $_POST[pref];

dbsystem();	
$setpref= "UPDATE prof_pref SET ";	

foreach ($pref as $pre=> $value){
if(!empty($pre)){
	$setpref= $setpref. ", ";
	}  
$setpref= $setpref. "pref".$value." = '1'";
 }

$setpref= $setpref. " WHERE unid = '$session_id'";

mysql_query($setpref);

[bluejay002]

can you display the query instead? sorry, am lazy sorting your code now.

[mikenl]

UPDATE prof_pref SET pref1 = '0', pref2 = '0', pref3 = '0', etc WHERE unid = '$session_id'

 

UPDATE prof_pref SET pref1 = '1', pref2 = '0', pref3 = '1', etc WHERE unid = '$session_id'

[bluejay002]

if you are pertaining to the same info and update them consecutively, i dont think its a good idea since you are going to update the same fields. why not go directly to the second update instead? or do you have other special implication.

 

sorry but i dont really see any good reason for doing such.

[hitman6003]

Why do you set them back to the defaults first?  As bluejay said, just issue the second update query and be done with it.

[mikenl]

I want to set them to default first as I receive only the selected checkboxes (as '1') and pass them on to the db.

 

The previous selection must be erased (filled with '0's) otherwise the db will fill up with '1's...

[mikenl]

OK, messed around with the query, and this is now working:

 

dbsystem();	
$setlanguage = "UPDATE prof_language SET ";	

foreach ($language as $lang => $value){
if(!empty($language)){
	$setlanguage = $setlanguage. ", ";
	}  
$setlanguage = $setlanguage. "lang".$value." = '$lang'";
 }

$setlanguage = $setlanguage. " WHERE unid = '$session_id'";

mysql_query($setlanguage);