Update Multiple Rows (PHP + MySQL)

Question

I am working on a lead management system - and as the database for it grows the need for more bulk functions appears - and unfortunately I am getting stuck with one of them. The database stores many different leads - with each lead being assigned to a specific closer; thus the database stores for each lead the lead id, name, closer name, and other info. The main lead list shows a checkbox next to each lead which submits the lead id into an array:

<input type=\"checkbox\" name=\"multipleassign[]\" value=\"$id\" />

Now this all goes to the following page:

<?php
include_once"config.php";
$id = $_POST['multipleassign'];
$id_sql = implode(",", $id);
$list = "'". implode("', '", $id) ."'";

$query = "SELECT * FROM promises WHERE id IN ($list) ";
$result = mysql_query($query);
$num = mysql_num_rows ($result);

if ($num > 0 ) {
$i=0;
while ($i < $num) {


$closer = mysql_result($result,$i,"business_name");
$businessname = mysql_result($result,$i,"closer");


echo "$closer - $businessname";
echo"<br>";

++$i; } } else { echo "The database is empty"; };



echo "<select name=\"closer\" id=\"closer\">";

$query2 = "SELECT * FROM members ";
$result2 = mysql_query($query2);
$num2 = mysql_num_rows ($result2);

if ($num2 > 0 ) {
$i2=0;
while ($i2 < $num2) {

$username = mysql_result($result2,$i2,"username");
$fullname = mysql_result($result2,$i2,"name");

echo "<option value=\"$fullname\">$fullname</option>";

++$i2; } } else { echo "The database is empty"; }
echo "</select>";

?>

I want to be able to use the form on this page to select a closer from the database - and then assign that closer to each of the leads that have been selected. Here is where I have no idea how to continue.

Answer 1

Actually - i got it. I don't know why I didn't think of it sooner. First off I passed the original $list variable over to the new page - and then:

<?php
include_once"config.php";
$ids = $_POST['list'];
$closer = $_POST['closer'];
$query = "UPDATE `promises` SET `closer` = '$closer' WHERE id IN ($ids) ";
mysql_query($query) or die ('Error updating closers' . mysql_error());
echo "A new closer ($closer) was assigned to the following accounts:";

$query = "SELECT * FROM promises WHERE id IN ($list) ";
$result = mysql_query($query);
$num = mysql_num_rows ($result);

if ($num > 0 ) {
$i=0;
while ($i < $num) {
$businessname = mysql_result($result,$i,"business_name");
echo "<li>$businessname";

++$i; } } else { echo "The database is empty"; };
?>

The updated page before this:

$query = "SELECT * FROM promises WHERE id IN ($list) ";
$result = mysql_query($query);
$num = mysql_num_rows ($result);

if ($num > 0 ) {
$i=0;
while ($i < $num) {


$closer = mysql_result($result,$i,"business_name");
$businessname = mysql_result($result,$i,"closer");


echo "$closer - $businessname";
echo"<br>";

++$i; } } else { echo "The database is empty"; };


echo "<form name=\"form1\" method=\"post\" action=\"multiple_assign2.php\">";
echo "<input type=\"hidden\" name=\"list\" value=\"$list\" />";
echo "<select name=\"closer\" id=\"closer\">";

$query2 = "SELECT * FROM members ";
$result2 = mysql_query($query2);
$num2 = mysql_num_rows ($result2);

if ($num2 > 0 ) {
$i2=0;
while ($i2 < $num2) {

$username = mysql_result($result2,$i2,"username");
$fullname = mysql_result($result2,$i2,"name");

echo "<option value=\"$fullname\">$fullname</option>";

++$i2; } } else { echo "The database is empty"; }
echo "</select>";
echo "<input name=\"submit\" type=\"submit\" id=\"submit\" value=\"Reassign Selected Leads\">";

?> 

Answer 2

After you select the leads and submit the form , your script should show them in a list with hidden inputs (with name=leads[] and value=the_lead's_id) and next to each lead there will be a dropdown box () which will be populated with all the closers. After choosing and sending the second form your script will "run" all-over the leads' ids array and update each and every one of them.

Got the idea or you want some code?