Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-09-16
6
Medium Priority
?
332 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 82

Accepted Solution

by:
hielo earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

604 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