Solved

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

Posted on 2011-09-16
6
321 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

This article discusses how to create an extensible mechanism for linked drop downs.
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

730 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