I would use three queries, as in your 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 (not tried,
and probably a little sloppy):
$rating_list = array(); // Build three lists in this array, one for each
rating ("A", "R", "NR")
for ($i = 0; $i < count($supp_id); $i++) {
$rating_list[$new_rating] .= "'" . $supp_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 suppliers set rating='A' where supp_id IN
(${rating_list["A"]})";
... And so forth.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Monday, February 26, 2007 12:38 PM
> To: mysql@lists.mysql.com
> Subject: what's better query to update table
>
> hi to all!
>
> I have a table that contains ratings of suppleirs. almost
> 2000 records.
> administrator uses a form with radio buttons (values R, A and NR) to
> chanage rating for each supplier.
>
> after he submit I have an array where index is $supp_id and value is
> $new_rating.
>
> Since, admin changes the rating 2-3 times a month, it's not
> big deal, but
> I would like to know what is "correct" solution: have a three queries,
> e.g.:
>
> mysql_query("
> update suppliers
> set rating='R'
> where supp_id=1 or supp_id=5 or supp_id=8 or supp_id=23 or ... or
> supp_id=1786");
> mysql_query("
> update suppliers
> set rating='A'
> where supp_id=2 or supp_id=3 or supp_id=9 or supp_id=18 or ... or
> supp_id=1823");
> mysql_query("
> update suppliers
> set rating='NR'
> where supp_id=4 or supp_id=6 or supp_id=7 or ... or
> supp_id=1824");
>
> or
>
> have a query for each record:
> mysql_query("update suppliers set rating='R' where supp_id=1");
> mysql_query("update suppliers set rating='A' where supp_id=2");
> mysql_query("update suppliers set rating='A' where supp_id=3");
> mysql_query("update suppliers set rating='NR' where supp_id=4");
> e.t.c.
>
> Or, there is other solution?
>
> Thanks for any help.
>
> -afan
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
