Solved

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

Posted on 2011-09-16
6
314 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:evibesmusic
  • 3
  • 2
6 Comments
 
LVL 82

Expert Comment

by:hielo
ID: 36550581
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
 
LVL 11

Expert Comment

by:Amar Bardoliwala
ID: 36550583
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
 

Author Comment

by:evibesmusic
ID: 36550751
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 82

Accepted Solution

by:
hielo earned 500 total points
ID: 36550840
>>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
 

Author Closing Comment

by:evibesmusic
ID: 36551591
I always seem to make things harder than they need to be.  Thank you!
0
 
LVL 82

Expert Comment

by:hielo
ID: 36551708
>>I always seem to make things harder than they need to be
Agree :)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
The viewer will learn how to count occurrences of each item in an array.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now