Updating multiple MYSQL rows with one submit button

jamb0 · April 4, 2011, 5:10pm

My problem is similar to here: PHP Questions and Answers - Page 750 - Bytes … -php-mysql except I changed my code to have one button at the top instead of one at the end of every row, to make things easier for the users.

I take values from a MYSQL table and print them into an HTML table using PHP, in a while loop. On EVERY row that is printed out there are two columns that the user can interact with: a dropdown box, and a text input. They can change the value of the dropdown box and enter text in the text input.
At the very top of my page I have an ‘Update’ button, which I would like to connect to a MYSQL UPDATE query to update every record depending on the conditions I set.

The problem is, at the minute I have put the form tags around the two fields in the while loop, so basically multiple forms with the same name will be generated if more than record is in the table and is displayed. The only other thing I can think of doing is to encase the majority of my code inside form tags, but I still wouldn’t understand how to update the fields for each record (each row displayed).

I hope this makes sense, please tell me if not!

Here is my code (which clearly isn’t working for updating the records. Some of the MYSQL at the top is rough and was written whilst I was trying to figure out how I would update every row, so also wont do anything):

<?php

session_start();

require("connect.php");

if (!isset($_SESSION['email']))
	header('Location: index.php');
	
//setting the user id
$useremail = $_SESSION['email'];
$useridquery = mysql_query("SELECT userid FROM users WHERE email = '$useremail'");
$userid = mysql_fetch_row($useridquery);
$userid = $userid[0];	
	
//checking lead status

$newstatus = $_POST['statusdropdown'];
$statusquery = mysql_query("SELECT status FROM leads WHERE userid = $userid");
$oldstatus = mysql_fetch_row($statusquery);
$oldstatus = $oldstatus[0];

//update button submission	

if ($submit)

	{
	
	//$statusupdate = "UPDATE leads SET status WHERE $newstatus != $oldstatus";
	//$commentsupdate = "UPDATE leads SET comments WHERE comments!=''";
	//mysql_query($statusupdate, $commentsupdate);
	
	
	
	
	
	}		

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
 <link rel="stylesheet" href="style.css" type="text/css" />
  <link rel="stylesheet" href="leadsboxes.css" type="text/css" />
	<meta http-equiv="Content-Type" content="text/html; charset-utf-8" />
	<title>Dashboard | Lead management</title>

</head>

<body id="leads">
	<div id="container"/>
	
		<div id="header">
			<div id="accountinfo">
  				<table  border="0" align="right" cellpadding="2" cellspacing="0">
    			<tr>
    				<td nowrap="nowrap">
    				<div id="email"><?php echo $_SESSION['email']; ?></div><!--end email-->
    				 </td>
    				<td nowrap="nowrap">|</td>
	     			<td nowrap="nowrap"><a href="help.php">Help</a></td>
    	 			<td nowrap="nowrap">|</td>
      				<td nowrap="nowrap"><a href="logout.php">Logout</a></td>
      				<td nowrap="nowrap">&nbsp;</td>
    			</tr>
  				</table>
			</div><!--end accountinfo-->	
			<img src="images/logo.png" id="logo" alt="Lead management" /><!--end logo-->		
			<div id="search">
				<form method="get" id="searchleads" action="">
					<fieldset class="search">
						<input type="text" class="box" />
						<button class="btn" title="Submit Search">Search</button>
					</fieldset>
				</form>
			</div><!--end search-->
			<div id="navigation">
				<a id="dashboardnav" href="dashboard.php" title="Dashboard"><span>Dashboard</span></a>
				<a id="leadsnav" href="leads.php" title="Leads"><span>Leads</span></a>
				<a id="accountnav" href="account.php" title="Account"><span>Account</span></a>
			</div><!--end navigation-->
		</div><!--end header-->
		
		<div id="content">
		
			<div id="allleads">
				<div class="maincssbox">
					<div class="maincssbox_head">
					<div id='updatebutton'><input type="submit" name="Submit" value="Update" /></div><!--end updatebutton-->
					<h2>&nbsp;Leads</h2>

				</div><!--end cssbox-->
						<div class="maincssbox_body">
						<div id="allleadstable">
						
						<?php
							
							$submit = $_POST['submit'];
							$result = mysql_query("SELECT * FROM leads WHERE userid = $userid");
							
							
							echo "<table border ='1' width='910'>";
							echo "<tr>";
								echo "<th width='50'>Date</th>";
								echo "<th width='30'>Title</th>";
								echo "<th width='120'>Name</th>";
								echo "<th width='40'>DOB</th>";
								echo "<th width='120'>Email</th>";
								echo "<th width='100'>Phone</th>";
								echo "<th width='150'>Address</th>";
								echo "<th width='80'>Status</th>";
								echo "<th width='100'>Comments</th>";
							echo "</tr>";
								
								while ($row = mysql_fetch_array($result))
							
							{
							
									//Assign variables
									
									$leadid = $row['leadid'];
									$displaydate = $row['dateadded'];
									$displaytitle = $row['title'];
									$displayname = $row['forename']." ".$row['surname'];
									$displaydob = $row['dobday']."/".$row['dobmonth']."/".$row['dobyear'];
									$displayemail = $row['email'];
									$displayphone = $row['phone'];
									$displayaddress = $row['housenumber']." ".$row['street']." ".$row['postcode'];
									
									//Status dropdown box
									$statusvalue = $row['status'];
									if ($statusvalue=='New')
									
										{
										$displaystatus = '<select name="statusdropdown"><option value="New" selected="selected">New</option><option value="Contacted">Contacted</option><option value="Qualified">Qualified</option><option value="Converted">Converted</option><option value="Nurturing">Nurturing</option><option value="Bad Lead">Bad lead</option></select>';
										}
									elseif ($statusvalue=='Contacted')
									
										{
										$displaystatus = '<select name="statusdropdown"><option value="New">New</option><option value="Contacted" selected="selected">Contacted</option><option value="Qualified">Qualified</option><option value="Converted">Converted</option><option value="Nurturing">Nurturing</option><option value="Bad Lead">Bad lead</option></select>';
										}
										elseif ($statusvalue=='Qualified')
									
										{
										$displaystatus = '<select name="statusdropdown"><option value="New">New</option><option value="Contacted" >Contacted</option><option value="Qualified" selected="selected">Qualified</option><option value="Converted">Converted</option><option value="Nurturing">Nurturing</option><option value="Bad Lead">Bad lead</option></select>';
										}
										elseif ($statusvalue=='Converted')
									
										{
										$displaystatus = '<select name="statusdropdown"><option value="New">New</option><option value="Contacted">Contacted</option><option value="Qualified">Qualified</option><option value="Converted" selected="selected">Converted</option><option value="Nurturing">Nurturing</option><option value="Bad Lead">Bad lead</option></select>';
										}
										elseif ($statusvalue=='Nurturing')
									
										{
										$displaystatus = '<select name="statusdropdown"><option value="New">New</option><option value="Contacted">Contacted</option><option value="Qualified">Qualified</option><option value="Converted">Converted</option><option value="Nurturing" selected="selected">Nurturing</option><option value="Bad Lead">Bad lead</option></select>';
										}
										elseif ($statusvalue=='Bad lead')
									
										{
										$displaystatus = '<select name="statusdropdown"><option value="New">New</option><option value="Contacted">Contacted</option><option value="Qualified">Qualified</option><option value="Converted">Converted</option><option value="Nurturing">Nurturing</option><option value="Bad Lead" selected="selected">Bad lead</option></select>';
										}
										
										//Comments field
										
										$commentsvalue = $row['comments'];
										$displaycomments = "<input type='text' maxlength='255' name='commentsfield' value=\\"$commentsvalue\\" />";
										
										
									
									//the table contents
									
									
									echo "<tr>";
										echo "<td>";
										echo "$displaydate";
										echo "</td>";
										echo "<td>";
										echo "$displaytitle";
										echo "</td>";
										echo "<td>";
										echo "$displayname";
										echo "</td>";
										echo "<td>";
										echo "$displaydob";
										echo "</td>";
										echo "<td>";
										echo "$displayemail";
										echo "</td>";
										echo "<td>";
										echo "$displayphone";
										echo "</td>";
										echo "<td>";
										echo "$displayaddress";
										echo "</td>";
										echo "<td>";
										echo "<form id ='changes' action='leads.php' method='POST'>$displaystatus";
										echo "</td>";
										echo "<td>";
										echo "$displaycomments</form>";
										echo "</td>";
										echo "</td>";
									echo "</tr>";
									
																
							
							}	
							
							
							
							echo "</table>";
							
							?>
							</div><!--end allleadstable-->  							
						
							
						
					</div><!--end cssbox_head-->
						</div><!--end cssbox_body-->
			</div><!--end leads-->
			
		</div><!--end content-->
		
		<div id="footer">
			<a href="dashboard.php"> Dashboard </a>|<a href="leads.php"> Leads </a>|<a href=""> Account </a><p id="footercopyright">Copyright &copy 2011</p>
		</div><!--end footer-->
	
	</div><!--end container-->

</body>
</html>

Thanks in advance for any help!

Cups · April 4, 2011, 8:26pm

I hope this makes sense, please tell me if not!

What is your question exactly?

Try and put a bit of effort into asking it in a more condensed form.

jamb0 · April 4, 2011, 8:32pm

My problems are:

I am very new to PHP, I don’t know how to update multiple MYSQL rows using one submit button, and my code doesn’t work.

My question is:

Can anybody advise on how to update multiple MYSQL rows using one submit button by taking a look at what I’ve done wrong or haven’t included?

Thanks.

Cups · April 4, 2011, 9:31pm

The principle is quite simple.

You post some data back to your form handler and it progresses through the script line by line until it ends or hits a fatal error.

So, lets say your html form submits an id.


if ( isset( $_POST['id'] ){

// do insert one

// do insert two

// do insert three

}

Now, first off, PROVE that the id was sent, then PROVE that the first insert worked.

How do you do that? simply use echo or var_dump() on the variables and echo them onto your page (or into your error log file).


<?php
echo $_POST['id'];
var_dump($_POST['id']);
error_log("I asked for post ID and I got ". $_POST['id'] );
?>

Make yourself a mini version of your script, get that working and then keep adding features till you break it - and that is where it will have gone wrong - then you can tell us what the problem is, but more likely than not, you will find it yourself.

Cups · April 5, 2011, 8:41am

In the cold light of day I have re-read (again) your question.

You seem to be asking how to update multiple records, but are unsure of the best method to use.

I think you have 3 main options.

a) do as you say, make multiple forms - one for each row - when a row has been completed - press a button marked ‘save this row’ (lets call it that for now) the whole page is submitted, and refreshed.

b) same as above, but use Ajax to action each form submission - only that row is submitted and the contents of that table row are refreshed in the DOM.

c) permit the admin user to make multiple changes to a single form, then at the bottom of the page - press ‘save all changes’ - which then updates every single record and then refreshes the entire page

If you go for a single form for each row (a and b above) then the form you are aiming for should look something like this (for userids 3 and 4)


<form action=post method=leads.php>
<input type=hidden name=id value=3>
<select name="statusdropdown">
<option value="New">New</option>
<option value="Contacted">Contacted</option>
<option value="Qualified">Qualified</option>
<option value="Converted">Converted</option>
<option value="Nurturing">Nurturing</option>
<option value="Bad Lead" selected="selected">Bad lead</option>
</select>
<input type=submit value='save this row'>
</form>

<form action=post method=leads.php>
<input type=hidden name=id value=4>
<select name="statusdropdown">
<option value="New">New</option>
<option value="Contacted">Contacted</option>
<option value="Qualified">Qualified</option>
<option value="Converted">Converted</option>
<option value="Nurturing">Nurturing</option>
<option value="Bad Lead" selected="selected">Bad lead</option>
</select>
<input type=submit value='save this row'>
</form>

The fact that this is only two records, is not in a table, and is static HTML is neither here nor there. Get this static HTML working as it should for just this single field and two rows.

THEN add more form elements, testing as you go.

THEN dress it up in a table as you wish.

THEN put the HTML in a loop, and inject your PHP values you extracted from mysql.

“Work in iterations : divide and conquer.”

In all though, you should pay particular attention to usability with a monster form (or set of forms) and decide how to handle cases where one form has been updated BUT NOT SUBMITTED and a user accidentally moves to a second form - and submits just that one and loses the first edits they made (and then your users may hate you).

There are tradeoffs to be made when choosing between methods a) b) or c).

HTH

jamb0 · April 6, 2011, 10:54pm

Well I have made progress. In the code below the userid is 12 because I am logged in with that user, and 3 rows are shown in the generated table.

The update button works as it should for any of the rows BELOW the first. The first one does not update!!

Can you spot the problem? This is frustrating the hell out of me and I need to get it sorted by tomorrow latest

Thanks

<?php

session_start();

require("connect.php");

if (!isset($_SESSION['email']))
{
    header('Location: index.php');
}

//setting the user id
$useremail = $_SESSION['email'];
$useridquery = mysql_query("SELECT userid FROM users WHERE email = '$useremail'");
$userid = mysql_fetch_row($useridquery);
$userid = $userid[0];

//checking lead status


$statusquery = mysql_query("SELECT status FROM leads WHERE userid = $userid");
$oldstatus = mysql_fetch_row($statusquery);
$oldstatus = $oldstatus[0];

//update button submission
$submit = $_POST['submit'];
$id = $_POST['id'];

        /*
        not sure if i should have used userid or leadid for this update since i dont know your database all that well
        but im going to guess that this will work. if it dosent try swapping lead id for userid and if you do you can
        remove the hidden field and $id = $_POST['id']
        */


    //$statusupdate = "UPDATE leads SET status WHERE $newstatus != $oldstatus";
    //$commentsupdate = "UPDATE leads SET comments WHERE comments!=''";
    //mysql_query($statusupdate, $commentsupdate);







?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
 <link rel="stylesheet" href="style.css" type="text/css" />
  <link rel="stylesheet" href="leadsboxes.css" type="text/css" />
    <meta http-equiv="Content-Type" content="text/html; charset-utf-8" />
    <title>Dashboard | Lead management</title>

</head>

<body id="leads">
    <div id="container"/>

        <div id="header">
            <div id="accountinfo">
          <table  border="0" align="right" cellpadding="2" cellspacing="0">
                <tr>
                    <td nowrap="nowrap">
                    <div id="email"><?php echo $_SESSION['email']; ?></div><!--end email-->
                     </td>
                    <td nowrap="nowrap">|</td>
                  <td nowrap="nowrap"><a href="help.php">Help</a></td>
                  <td nowrap="nowrap">|</td>
              <td nowrap="nowrap"><a href="logout.php">Logout</a></td>
              <td nowrap="nowrap">&nbsp;</td>
                </tr>
          </table>
            </div><!--end accountinfo-->
            <img src="images/logo.png" id="logo" alt="Lead management" /><!--end logo-->
            <div id="search">
                <form method="get" id="searchleads" action="">
                    <fieldset class="search">
                        <input type="text" class="box" />
                        <button class="btn" title="Submit Search">Search</button>
                    </fieldset>
                </form>
            </div><!--end search-->
            <div id="navigation">
                <a id="dashboardnav" href="dashboard.php" title="Dashboard"><span>Dashboard</span></a>
                <a id="leadsnav" href="leads.php" title="Leads"><span>Leads</span></a>
                <a id="accountnav" href="account.php" title="Account"><span>Account</span></a>
            </div><!--end navigation-->
        </div><!--end header-->

        <div id="content">

            <div id="allleads">
                <div class="maincssbox">
                    <div class="maincssbox_head">
                    <h2>&nbsp;Leads</h2>

                </div><!--end cssbox-->
                        <div class="maincssbox_body">
                        <div id="allleadstable">

                        <?php


                            $result = mysql_query("SELECT * FROM leads WHERE userid = $userid");



                            echo "<table border ='1' width='910'>
                                                        <tr>
                                                        <th width='50'>Date</th>
                                                        <th width='30'>Title</th>
                                                        <th width='120'>Name</th>
                                                        <th width='40'>DOB</th>
                                                        <th width='120'>Email</th>
                                                        <th width='100'>Phone</th>
                                                        <th width='150'>Address</th>
                                                        <th width='80'>Status</th>
                                                        <th width='100'>Comments</th>
                                                        </tr>
                                                        <form id ='changes' action='leadstest.php' method='POST'>
                                                        <input type='submit' name='submit' value='Update' />";

                                while ($row = mysql_fetch_array($result))

                            {

                                    //Assign variables

                                    $leadid = $row['leadid'];
                                    $displaydate = $row['dateadded'];
                                    $displaytitle = $row['title'];
                                    $displayname = $row['forename']." ".$row['surname'];
                                    $displaydob = $row['dobday']."/".$row['dobmonth']."/".$row['dobyear'];
                                    $displayemail = $row['email'];
                                    $displayphone = $row['phone'];
                                    $displayaddress = $row['housenumber']." ".$row['street']." ".$row['postcode'];




                                        //Comments field

                                        $commentsvalue = $row['comments'];
                                        $displaycomments = "<input type='text' maxlength='255' name='commentsfield' value=\\"$commentsvalue\\" />";



                                    //the table contents


                                    echo "<tr><td>
                                                                        $displaydate
                                                                        </td><td>
                                                                        $displaytitle
                                                                        </td><td>
                                                                        $displayname
                                                                        </td><td>
                                                                        $displaydob
                                                                        </td><td>
                                                                        $displayemail
                                                                        </td><td>
                                                                        $displayphone
                                                                        </td><td>
                                                                        $displayaddress
                                                                        </td><td>";

                                                                        //Status dropdown box
                                                                   $statusvalue = $row['status'];

                                                                        $array["New"] = "New";
                                                                        $array["Contacted"] = "Contacted";
                                                                        $array["Qualified"] = "Qualified";
                                                                        $array["Converted"] = "Converted";
                                                                        $array["Nurturing"] = "Nurturing";
                                                                        $array["Qualified"] = "Qualified";
                                                                        $array["Bad lead"] = "Bad lead";

                                                                        echo "
                                                                        <select name='statusdropdown'>";
                                                                        foreach ($array as $key => $value)
                                                                        {
                                                                        if ($statusvalue==$key)
                                                                        echo "<option value='$key' selected='selected'>$value</option>";
                                                                        else if ($statusvalue!=$key)
                                                                        echo "<option value='$key'>$value</option>";
                                                                        }
                                                                        echo "</select>
                                                                        <input type='hidden' name='id' value='$leadid'>
                                                                        </td><td>
                                                                        $displaycomments
                                                                        </td></td></tr>";

                            }

                           echo "</form></table>";

        if(isset($_POST['submit']))

    {
        $newstatus = $_POST['statusdropdown'];
        $newcomments = $_POST['commentsfield'];
        $leadidpost = $_POST['id'];
        mysql_query("UPDATE leads SET status = '$newstatus', comments = '$newcomments' WHERE leadid = '$leadidpost'");
        echo "MYSQL error:".mysql_error();

        }

                            ?>
                            </div><!--end allleadstable-->



                    </div><!--end cssbox_head-->
                        </div><!--end cssbox_body-->
            </div><!--end leads-->

        </div><!--end content-->

        <div id="footer">
            <a href="dashboard.php"> Dashboard </a>|<a href="leads.php"> Leads </a>|<a href=""> Account </a><p id="footercopyright">Copyright &copy 2011</p>
        </div><!--end footer-->

    </div><!--end container-->

</body>
</html>

Cups · April 7, 2011, 11:10am

echo $leadid onto the page and check its value, if it looks good AND it corresponds to a real record and you see nothing wrong then go and look in your mysql log file, and find out what happened at that end.

Isolate the html, php and mysql and zoom in on which layer actually let you down.

I was going to try and help you with your array generation but the way you posted all those tabs in your code just made me give up half way through - I have to go and eat.

For future note: Making us scroll left and right is not a winning strategy if you want help.

jamb0 · April 7, 2011, 12:36pm

Sorry

Cups · April 7, 2011, 2:20pm

That’s OK. Just trying to get you the most help I can.

What I wanted to say (now I’m well fed )

as the key and value are the same thing in your select box loop, simply do this:


$options_to_match = array("Contacted", "Qualified", "Converted"
"Nurturing", "Bad Lead");

echo "<select name='statusdropdown'>";

foreach ($options_to_match as $match){

 if ($statusvalue==$match){
   echo "<option value='$match' selected='selected'>$match</option>";
 } else {
   echo "<option value='$match'>$match</option>";
 }

}
echo "</select>

Although it is possible to get that down further…


$options_to_match = array("Contacted", "Qualified", "Converted"
"Nurturing", "Bad Lead");

echo "<select name='statusdropdown'>" . PHP_EOL;

foreach ($options_to_match as $match){

// use of a ternary operator if there are only 2 'if' options
// if (condition) is TRUE do first, else do second, an empty string

  $selected = ($statusvalue==$match) ? "selected='selected'" : "" ;
  echo "<option value='$match' $selected>$match</option>" . PHP_EOL;

// use PHP_EOL so when you look at html source code you can see
// the line ends instead of one great long string of options 
// EOL = END OF LINE, inserts OS dependent end of line char(s)

}
echo "</select>" . PHP_EOL ;

Chop the comments out when you use it.

chelse · April 16, 2012, 10:27pm

Hi

Your question is similar to mine (844094-Implement-multiple-row-edits-with-one-submit-phpmyadmin-style) . You can find it in this section. The experts were a considerable help.