[RESOLVED] UPDATE query problem on MySQL

[tony_l]

ok, I have not done sql in a while and I am desperately trying to get this query to work. I have read other solutions, but they require such a complex query, I am convinced that there is an easier way to do this......

THE SET-UP:

I have a guestbook application we are developing - the users enter a guestbook entry. The entries are automatically set to have their status set to "0" - which means not approved for display.

We have everything working, and now we are working on the admin part - approving and disapproving the entries. An admin sees a form with each entry listed on a seperate row of a table and a checkbox for "Mark as Read", "Approve", and "Deny". This part works as well.

PROBLEM:

The UPDATE query is not doing anything. When we check 1 box, it is ok, but with several boxes, it is not doing anything. I go into phpmyadmin and try the query in the SQL box, and it does not do anything there either. - CONCLUSION - it is my update statement with several rows that is not doing anything.

THE CODE

IF(count($denyArray) >= 1)
{

//denyArray is passed to this function - it contains the id key of each item in the
//database that has been checked as "denied".

//Make Denial Query
$denialQuery = "UPDATE $table SET status = '0' WHERE";

   foreach ($denyArray as $check)
   {
         //$table is passed to this function as well, it is the name of table (duh LOL)
   	$denialQuery .=  " $table.id = '$check' AND";

   }//end foreach

//get rid of last AND
   $denialQuery = substr($denialQuery, 0, -3);

PRINT("<br><br>Denail Query = <br>$denialQuery<br>");

return mysql_query($denialQuery);
}//end if

THE RESULTING QUERY:

When we test it by selecting the first 6 entries, this is the resulting query:

UPDATE guestbook SET status = '0' WHERE guestbook.id = '1' AND guestbook.id = '2' AND guestbook.id = '3' AND guestbook.id = '4' AND guestbook.id = '5' AND guestbook.id = '6' 

[Roger_Ramjet]

You need to use OR not AND, no id is equal to 1 AND 2 etc.

[tony_l]

huh!

That did the trick!

Well I guess I was thinking a different logic - I would have thought that the query would have stopped at the first id since it would be an OR.

Thanks!

[Roger_Ramjet]

What you were thinking of was this query form

"UPDATE guestbook 
SET status = '0' 
WHERE id IN (1,2,3,4,5,6)";

Very easy to construct as you just need to implode the $denArray into a comma seperated string and use that in the query.

[tony_l]

and that is SO much better - thanks ..."you da man!"