I'm creating a form to update multiple items in a database at once.
The sample form is located at
http://128.241.191.192/test/approvelistings.php

I'm not able to get the update script to update multiple rows at once.
Can you assist me with what the PHP should look like on the update
page?

Following is the code for the posting form:

<?php 

include("config.php");

//APPROVE LISTINGS FROM DATABASE

$sql = "SELECT id, category, email, phone, adtext, ip,
DATE_FORMAT(postdate, '%a-%d-%y %H:%i:%s') AS time FROM classifieds
WHERE approved='pending' ORDER BY time DESC LIMIT 5 ";

$result = mysql_query($sql, $conn) or die (mysql_error());

//GO THROUGH EACH ROW IN THE RESULT AND DISPLAY DATA

print "<link rel=stylesheet type=text/css href=database-styles.css>";


print "<BR><h1>Approve or Decline
Classifieds</h1><BR><br><center><table padding=5 id=approvelistings
width=650>";
print "<form method=post action=approve-update.php>";

while ($newArray = mysql_fetch_array($result)) {
   //give a name to the fields
   $category = htmlentities($newArray['category']);
   $email = $newArray['email'];
   $phone = $newArray['phone'];
   $adtext = $newArray['adtext'];
   $ip = $newArray['ip'];
   $id = $newArray['id'];
   $postdate = $newArray['time'];

   //echo the results on screen
   
   print "<tr align=left><b>AD:</b> $id  
&nbsp&nbsp&nbsp&nbsp<b>IP:</b> $ip  &nbsp&nbsp&nbsp<b>POSTED:</b>
$postdate<br>";
   print "<input type=hidden name=ad[id] value=$id>";
   print "<input type=text size=40 name=ad[category] value=$category><br>";
   print "<textarea rows=5 cols=60 name=ad[adtext]>$adtext</textarea><br>";
   print "<input type=text size=40 name=ad[email] value=$email><br>";
   print "<input type=text size=40 name=ad[phone] value=$phone><br>";
   print "<input type=checkbox value='yes' name=ad[approved]> Approved
<input type=checkbox value='no' name=ad[approved]> Declined
<br><br></tr>";

   }
   
print "</table><br>";  
print "<input value='Update Selected' type=submit>";
print "</form>";


?>

*******************

Here's code from the update page, but it only updates one row, not all of them:

$ads = $_POST[ad];

foreach($ads as $key => $value) { 

$update = "UPDATE classifieds SET 
              category='{formdata[$key][category]}',
              email='{formdata[$key][email]}',
              phone='{formdata[$key][phone]}',
              adtext='{formdata[$key][adtext]}',
              approved='{formdata[$key][approved]}'
              WHERE id=$key";
   
$result = mysql_query($update);

Hi martinluther245,

The problem you might be having is easily resolved by asigning to each
one of the form elements a unique name and id. This method has been
proven to be reliable every time and works with older browsers.

Currently, you asign the same name for the form elements to every row
you itirate, so you end up posting eight arrays of form elements and
it's complex to see which checkboxes are checked and then to which row
in the database they belong.

Theres an easier quick snippet of code that you might consider
testing. It works like a charm in these cases.


// Modified code

while ($newArray = mysql_fetch_array($result)) {
   $category = htmlentities($newArray['category']);
   $email    = $newArray['email'];
   $phone    = $newArray['phone'];
   $adtext   = $newArray['adtext'];
   $ip       = $newArray['ip'];
   $id       = $newArray['id'];
   $postdate = $newArray['time'];

   // Here we asign a unique form element name and id for each row we itirate. 
  
print "<tr align=left><b>AD:</b> $id <b>IP:</b> $ip <b>POSTED:</b>$postdate<br>";
   print "<input type=text size=40 name=category_$id id=category_id
value=$category><br>";
   print "<textarea rows=5 cols=60 name=text_$id 
id=text_$id>$adtext</textarea><br>";
   print "<input type=text size=40 name=email_$id id=email_$id value=$email><br>";
   print "<input type=text size=40 name=phone_$id id=phone_$id  value=$phone><br>";
   print "<input type=checkbox name=check_approved_$id> Approved
<input type=checkbox name=check_declined_$id> Declined <br><br></tr>";

   }


// So now we post the form and check for the results.

 // Itirate the objects of the form 
 foreach($_POST as $variable=>$value)  { 

     // We get the value of the form element here.  
     $$variable=$value;  
					
     // Now we check if the form is a checkbox
     if (substr($variable,0,5)=="check")  {  

   	   // If it indeed was a checkbox and it was checked 
	   if ($value == "on")  {  

	         // We see which checkbox was checked and we get the row id
by asigning it to an array exploding the checkbox name
		 $checkboxtypeandrowid = explode("_",$variable);  
		 $checkboxtype = $checkboxtypeandid[1];
		 $rowid = $checkboxtypeandid[2];

		 // We now get the corresponding values to the variables.
                 $elementCategory = "category_" . $rowid;
                 $category= $$elementCategory;

                 $elementEmail= "email_" . $rowid;
                 $email= $$elementEmail;

                 $elementPhone = "phone_" . $rowid;
                 $phone= $$elementPhone;

                 $elementText = "text_" . $rowid;
                 $adtext=$elementText;

		 // Depending of the checkbox type, we see if the transaction was
approved or not.
                 if ($checkboxtype=approved) $approved="YES"; else $approved="NO";

		$update = "UPDATE classifieds SET category='$category',
email='$email', phone='$phone', adtext='$adtext', approved='$approved'
where id=$rowid";
		$result = mysql_query($update);
		

         	 }
}



- Mike