Home / Speed Up Queries / Question No: 16

I have a table which contains ratings of buyers who are in thousands. I use a form with radio buttons to change rating for each buyer. After submitting I have an array where index is $buyer_id and value is $new_rating. Since, I change the rating more than times a month, it's not big deal, but I would like to know what is better solution. I have a three queries like below:

$SQL_1 = "UPDATE buyers SET rating='R'WHERE buyer_id=1 OR buyer_id=5 
          OR buyer_id=8 OR buyer_id=23 OR ... OR buyer_id=1786";

mysql_query($SQL_1);

$SQL_2 = "UPDATE buyers SET rating='A' WHERE buyer_id=2 OR buyer_id=3 
          OR buyer_id=9 OR buyer_id=18 OR ... OR buyer_id=1823";

mysql_query($SQL_2);

$SQL_3 = "UPDATE buyers SET rating='NR' WHERE buyer_id=4 OR buyer_id=6 
          OR buyer_id=7 OR buyer_id=21  OR ... OR buyer_id=1824";

mysql_query($SQL_3); 

or like below for each record:

mysql_query("UPDATE buyers SET rating='R' WHERE buyer_id=1"); 
mysql_query("UPDATE buyers SET rating='A' WHERE buyer_id=2"); 
mysql_query("UPDATE buyers SET rating='A' WHERE buyer_id=3"); 
mysql_query("UPDATE buyers SET rating='NR' WHERE buyer_id=4");

etc. 

Or, is there is any better solution than above?

I would use three queries, as in your above first example, but use IN followed by a list of IDs that you construct. That would be easier to build than all of those OR conditions. Loop through the input something like this:

// Build three lists in this array, one for each rating ("A", "R", "NR")
$rating_list = array(); 

for ($i = 0; $i < count($buyer_id); $i++) {
    $rating_list[$new_rating] .= "'" . $buyer_id . "',";
}

// Each list probably has one extra comma
$thislen = strlen($rating_list["A"]);

if ($thislen > 0) {
   $rating_list["A"] = substr($rating_list["A"], 0, $thislen - 1);
}

$query = "UPDATE buyers SET rating='A' WHERE buyer_id 
          IN(${rating_list["A"]})";

... and so forth.