Solved

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

Posted on 2011-09-16
6
311 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 82

Accepted Solution

by:
hielo earned 500 total points
Comment Utility
>>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
Comment Utility
I always seem to make things harder than they need to be.  Thank you!
0
 
LVL 82

Expert Comment

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

Featured Post

Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 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

13 Experts available now in Live!

Get 1:1 Help Now