FOREACH / UPDATE query

[Clinton]

if (is_array($_POST['uid'])) {
foreach($_POST['hours']AS $keyd => $value)
  {
   echo "UPDATE participantlog SET noshow=1 AND hours=" . $value . " WHERE cid=" . $cid . " AND uid=" . $keyd . "<br>"; 
   mysql_query("UPDATE participantlog SET noshow='1' AND hours='$value' WHERE cid='$cid' AND uid='$keyd'") or die(mysql_error());
  }
}

For some reason my query isn't executing. The foreach is working properly and producing all the right variables (as verified by the echo). But it just isn't updating the DB. Why might this be? Thanks!

 

Clinton

[jcbones]

Have you tried running the echo'd query in the mysql console?

[computermax2328]

mysql_query("UPDATE participantlog SET noshow='1' AND hours='$value' WHERE cid='$cid' AND uid='$keyd'") or die(mysql_error());

In your query here, are your cid and uid stored in your database as integers? If they are, remove the ''. Quotes are for strings, not for integers. 

[jcbones]

Correct Max, but that will not cause the query to fail. It is just bad form, and another reason that people should have already migrated to MySQLI, and/or PDO.

 

In fact when I was a yongling, I was instructed to always insert integers as strings, to help combat injection attempts.. Being that it was a security firm requesting it, I did it that way for a few years before I was 'learned' better.

[Clinton]

Bad Form, Youngling... I guess that's still me. :-( I'll change that.

Okay, so that won't cause the query to fail, and I should try it in mysql console. Here's a yongling question... where do I find that? In the phpmyadmin area????

[Psycho]

Create your query as a string variable. Then when you run into problems echo THAT to the page. By recreating the query for debugging, you run the risk of creating a different string for the output than you are for the actual execution. That's a recipe for many lost hours.

 

The problem with your query is that you are using "AND" between the values you are setting - you need to separate them with a comma

if (is_array($_POST['uid']))
{
    foreach($_POST['hours'] as $keyd => $value)
    {
       $query = "UPDATE participantlog SET noshow=1, hours='$value' WHERE cid='$cid' AND uid='$keyd'";
       mysql_query($query) or die("Query: $query <br>Error: ".mysql_error());
    }
}

EDIT: Also, running queries in loops is a bad idea. Unless you know that the list will be very small, you need to find a single query solution. Here is a page that may help you: http://www.karlrixon.co.uk/writing/update-multiple-rows-with-different-values-and-a-single-sql-query/

Edited May 28, 2013 by Psycho