How to find rows not included in a 'foreach' statement?

Experts,

I am using a foreach loop to update a table of data.  What I need to do is identify all other rows that were not updated during this loop and update them accordingly.

Here is the code I currently have which updates a row of data when the variable $rowID1 = the unique ID of a row in my table.

$rowID1 = $_POST[expected];
foreach($_POST[expected] as $rowID1){
      $update_sql = "UPDATE schedule SET showed='1' WHERE ID='$rowID1'";
      $update_query = mysql_query($update_sql) or die ("Could not run query: ".$update_sql."<br />\n".mysql_error());
}

I now need to identify which rows were not updated in the loop above and use the following query to update these rows.

      $update_sql2 = "UPDATE schedule SET showed='0' WHERE ID='$rowID1'";
      $update_query2 = mysql_query($update_sql2) or die ("Could not run query: ".$update_sql2."<br />\n".mysql_error());
 
Can anyone offer any suggestions how to do this?
evibesmusicAsked:
Who is Participating?
 
hieloConnect With a Mentor Commented:
>>if($result['expected']=1)
    //send "checked"="checked"
    else
    //the non-check field is sent


So you must be executing some query (let's call that Q1) that is "tied" to $result.

Then when the form is posted, you need to update ALL the records from the ORIGINAL query (Q1) so that they are all set to showed='0'

In other words if Q1 was:
SELECT * FROM table WHERE active=1

Then upon submission of the form you need to do:
UPDATE table SET showed=1 WHERE active=1

The reason for this is that the browser will submit ONLY the checked items.  So AFTER you have updated all of them to showed=0, THEN you iterate over $_POST['expected']  (which are only the checked items) updating each item to showed='1' for that rowID
0
 
hieloCommented:
try:
$notUpdated=array();
$rowID1 = $_POST[expected];
foreach($_POST[expected] as $rowID1){
      $update_sql = "UPDATE schedule SET showed='1' WHERE ID='$rowID1'";
      $update_query = mysql_query($update_sql) or die ("Could not run query: ".$update_sql."<br />\n".mysql_error());

     //Refer to:
     //http://www.php.net/manual/en/function.mysql-affected-rows.php
     if( mysql_affected_rows() < 1 )
     {
       $notUpdated[]=$rowID;
     }
}

//now here you can iterate over your $notUpdated array and update those rows

Open in new window

0
 
Amar BardoliwalaCommented:
Hello evibesmusic,

it seems a strange question, may be I did not understand it properly.

But in your code above  row of your table will always be updated if it finds '$rowID1' in you table. It will fail only if  ID = '$rowID1' is not present in your table.

Which means that you can not run second update query because it will also fail as '$rowID1' is not present in table.

I think some detail is missing.

Hope this helps.

Thank You.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
evibesmusicAuthor Commented:
Experts,

More background.

I am dynamically creating a table.  Each row of the table has a checkbox which has a unique value.  This value corresponds to the unique ID for that row in the database.  I am trying to allow users to be able to check/un-check the checkboxes and submit their changes.

Each checkbox uses the following code:

if($result['expected']=1){echo'<td><input name="expected[]" checked="checked" type="checkbox" value="'.$result['ID'].'"></td>';}
else{echo'<td><input name="expected[]" type="checkbox" value="'.$result['ID'].'"></td>';}                        

Does this change the scenario listed above where if a checkbox is checked, it goes through the foreach loop and updates the database accordingly?

Once the foreach loop is finished the identity (ID) of the unaffected rows needs to be established, and those rows of data need to be altered.

The rows of data that were not altered in the foreach loop need to have the following querey applied:

$update_sql2 = "UPDATE schedule SET showed='0' WHERE ID='NONAFFECTED_ROW'";
$update_query2 = mysql_query($update_sql2) or die ("Could not run query: ".$update_sql2."<br />\n".mysql_error());
0
 
evibesmusicAuthor Commented:
I always seem to make things harder than they need to be.  Thank you!
0
 
hieloCommented:
>>I always seem to make things harder than they need to be
Agree :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.