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.

For anyone who might be confused: leads and closer relate to loans (e.g. "I have a lead on a new loan possibility. I have assigned Mike, our best loan closer, to pursue this lead"). — rockerest
– rockerest, Commented Feb 27, 2012 at 17:05
i think i got it right : you can create your UPDATES inside your loop, store them inside an array then run them inside a foreach / for. — Charles Forest
– Charles Forest, Commented Feb 27, 2012 at 17:43

MikeRapidPurple · Accepted Answer · 2012-02-29 03:25:51Z

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\">";

?>

Ofir Baruch · Accepted Answer · 2012-02-27 17:02:05Z

0

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?

answered Feb 27, 2012 at 17:02

Ofir Baruch

10.3k2 gold badges29 silver badges39 bronze badges

1 Comment

MikeRapidPurple Over a year ago

Actually I would ideally like to have a single drop down box. So the main lead listing will be where the leads whose closers must be changed are selected, on the next page the closer is selected from the drop-down and on the next page the closer change is perforrmed on all the selected leads.

Collectives™ on Stack Overflow

Update Multiple Rows (PHP + MySQL)

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related