[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Updating multiple records at one time in MySQL with PHP

Posted on 2009-02-18
19
Medium Priority
?
1,734 Views
Last Modified: 2013-12-12
I am trying to update a single field in multiple records at the same time in MySQL using PHP.

When the user views the page (code below) they see the information in the records but the last field is editable. I would like them to be able to enter a number in the last fields (order them) and then update the database records all at once.
MAIN PAGE WITH EDITABLE FIELDS
 
 
echo "<form name='tstest25' enctype='multipart/form-data' method='post' action='changeOrderDB.php'>";
 
$whichRoom = $_POST["room700"];
$data = mysql_query("SELECT * FROM documents WHERE roomnumber='$whichRoom'") or die(mysql_error());
 
$records=0;
while($info = mysql_fetch_array( $data ))
{
  if ($records%2==0)
  echo "<tr>";
  echo "<td>";
  echo $info["roomnumber"]."</td><td>";
  echo $info["docname"]."</td><td>";
  echo $info["docfile"]."</td><td>";
  echo $info["doctype"]."</td><td>";
  echo $info["docdate"]."&nbsp;</td><td>";
  echo "<input type='text' value='" .$info["fileOrder"]."' size='3' name='orderDB'>&nbsp;";
  echo "</b></td></tr>";
  if ($records%2==1)
    echo "";
  $records++; //---------------
}
echo "<tr><td colspan='5'></td><td><input type='submit' value='Save Order'></td></tr></table>";
echo "</form>";
 
 
UPDATE MYSQL SCRIPT ('changeOrderDB.php') FORM IS SENT HERE TO UPDATE
 
mysql_query("UPDATE documents SET fileOrder = '$_POST[orderDB]'");
 
echo "<div align='center'>File Order Has Been Changed<br /><a href='javascript:history.back()'>Go Back</a></div>";

Open in new window

0
Comment
Question by:rcimasi
  • 9
  • 4
  • 3
  • +1
19 Comments
 
LVL 15

Expert Comment

by:Tomeeboy
ID: 23673498
Updating the same field in multiple rows is typically pretty easy, IF you are updating the fields with the same information, or with some kind of simple conditional statement.  It sounds like you are building something similar to a queue or ordered list where items can be shifted up/down in order by changing their order value.  I'm assuming this means that you don't want any two rows to have the same order value, meaning most or all of the rows would need to be updated every time one row had its order changed.

I don't think you are going to find a query capable of updating multiple rows with different, dynamic values, in one fell swoop (ie: if you change multiple, or all, order values at once).  However, I think you CAN cut down on the number of update queries that will be needed to manage this list through the use of some smart coding and conditional MySQL UPDATE queries.  If you have a lot of rows, this may be easier to manage if you only change the order of one row at a time.

For example, you can easily move the position of a single row AND update all other rows to reflect the new order with a couple queries.  The code below shows how, and could be used in conjunction with buttons on your form that would move rows up, down, to the top, etc..  You could also modify a row's order manually by changing it's number, but only one row at a time unless you further build on this code.
<?php
 
if ($new_position < $current_position) {
     $query = "UPDATE yourtablename SET orderfield = orderfield+1 WHERE orderfield >= $new_position AND orderfield < $current_position";
} else {
     $query = "UPDATE yourtablename SET orderfield = orderfield-1 WHERE orderfield <= $new_position AND orderfield > $current_position";
}
 
// This essentially shifts everything up or down one slot, when needed.
 
$result = mysql_query($query) or die("Could not update order or affected rows");
 
// Once the affected rows have been shifted, the order is changed on the row you modified
 
if($result) {
     mysql_query("UPDATE yourtablename SET orderfield = $new_position WHERE id = $id) or die ("Could not update positon of modified row");
}
 
?>

Open in new window

0
 
LVL 15

Expert Comment

by:Tomeeboy
ID: 23673554
Edit:  I missed a quotation mark in that last mysql_query call.  Also, I should note that the $id variable in this query would be your primary key, or whatever unique identifying information you are using to update a specific row.  Updated version:
if($result) {
     mysql_query("UPDATE yourtablename SET orderfield = $new_position WHERE id = $id") or die ("Could not update positon of modified row");
}

Open in new window

0
 
LVL 8

Expert Comment

by:agamal
ID: 23674143
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:rcimasi
ID: 23674324
It doesn't have to be that complicated. I just need to be able to update a database field. With the code above the fields are all updated with the same input because it sees the "orderDB" field as one field and alwasy updates all the files with the same number.
0
 
LVL 15

Expert Comment

by:Tomeeboy
ID: 23675068
Your above query doesn't work because there is no "WHERE" clause that tells it which row to update, so it updates them all.  Correct syntax:

UPDATE table SET fieldname = "fieldvalue" WHERE identifying_field = "identifying_value"

So basically, you need to tell it which row you want to update.  Even if you add this, you will not be able to update the same field in multiple rows with one query if the values going into those fields are all different.  You'll need to use something like my example above, or have a separate UPDATE query for each row that is being updated.
0
 

Author Comment

by:rcimasi
ID: 23675191
You are correct.. and that is fixed. but it still updates all rows that match the WHERE clause with the same number. if there is three records to update then it takes on the last records number.
RECORDS

"Doc Name"  "File Name" "Date Posted" "Order Number"

This displays to the user but the user can only change "Order Number"  but if there is multiple records that match the WHERE clause (because there is mutliple documents posted) then the "Order Number" becomes the same.. If I put 1 in first record then 2 in second record then 3 in third record then hit "Update" (submit) the records all change to 3...
0
 

Author Comment

by:rcimasi
ID: 23675211
Here is a screenshot of the user interface.
0
 

Author Comment

by:rcimasi
ID: 23675221
Ooops.. here it is..
userIF.jpg
0
 

Author Comment

by:rcimasi
ID: 23675271
Updated Code (still not working)...
// USer interface as seen above
 
 
<?php
 
$con = mysql_connect("localhost","root","");
mysql_select_db("pass", $con);
$hotdog = "roomnumber";
 
// Displays documents and their information from MySql
 
 
 
$data = mysql_query("SELECT * FROM documents WHERE roomnumber='$whichRoom'") or die(mysql_error());
 
$records=0;
while($info = mysql_fetch_array( $data ))
{
  if ($records%2==0)
  echo "<tr>";
  echo "<td>";
  echo "<input type='text' value='" .$info["roomnumber"]. "' name='roomNumb' style='border:0px;' readonly></td><td>";
  echo $info["docname"]."</td><td>";
  echo $info["docfile"]."</td><td>";
  echo $info["doctype"]."</td><td>";
  echo $info["docdate"]."&nbsp;</td><td>";
  echo $info["fileOrder"]."&nbsp;</td><td>";  
  ?>
  
  <select name="orderDB" id="orderDB">
  <?php
   echo "<option value=''></option>
   <option value='01'>1</option>
   <option value='02'>2</option>
   <option value='03'>3</option>
   <option value='04'>4</option>
   <option value='05'>5</option>
   <option value='06'>6</option>
   <option value='07'>7</option>
   <option value='08'>8</option>
   <option value='09'>9</option>
   <option value='10'>10</option>
   <option value='11'>11</option>
   <option value='12'>12</option>
   <option value='13'>13</option>
   <option value='14'>14</option>
   <option value='15'>15</option>
   <option value='16'>16</option>
   <option value='17'>17</option>
   <option value='18'>18</option>
   <option value='19'>19</option>
   <option value='20'>20</option>
   </select><br />";  
  echo "</b></td></tr>";
  if ($records%2==1)
    echo "";
  $records++; //---------------
}
?>
 
 
// MySQL update script that follows code (form) above
 
 
<?php
 
$whichRoom2 = $_POST["roomNumb"];
$whatever = $_POST["orderDB"];
$con = mysql_connect("localhost","root","");
mysql_select_db("pass", $con);
 
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
   }
 
//mysql_query("INSERT INTO documents (fileOrder) 
//VALUES ('$_POST[orderDB]')"); 
 
 
mysql_query("UPDATE documents SET fileOrder = '$whatever' WHERE roomnumber = '$whichRoom2'");
 
echo "<div align='center'>File Order Has Been Changed<br /><a href='javascript:history.back()'>Go Back</a></div>";
?>

Open in new window

0
 
LVL 8

Expert Comment

by:agamal
ID: 23681065
a dump from "documents" table will be more than helpful
0
 
LVL 12

Expert Comment

by:Richard Davis
ID: 23681910
One thing I am failing to see implemented here is any use of a primary key's value for making each record's row on your form uniquely identifiable upon submission.

Without seeing the structure of your documents table...I am merely  taking stabs in the dark here, but based on the content of previous posts, my guess is that your documents table is not using any primary key at all. As such, it makes working on the data in that table far more difficult as in your existing problem.

I would suggest that you add a field to your table (if it doesn't already exist) as follows;

Field Name: docID
Field Type: INT
Attributes: AUTO_INCREMENT
Index: Primary Key

By doing this, this allows each record to have it's own unique, auto-generated value that identifies one record from the other.

The current way you are doing things can be done, but it's a far cry more difficult to work with.

~A~
0
 

Author Comment

by:rcimasi
ID: 23682191
Ok.. The docID field has been added to the table.. Now how can I utilize that to Update multiple records.. Thanks for the hint!
0
 
LVL 8

Expert Comment

by:agamal
ID: 23682628
as i said a dump from table will help test the code and see what you see and fix it before sending it back to you
0
 
LVL 12

Expert Comment

by:Richard Davis
ID: 23682809
Well, your next step will be to create dynamically generated names for each <select> tag.

Perhaps something like in the code snippet below. This will now name each select tag according to which record it's related to. I think you can begin to see how the docID field begins to play a role in the significance of its direct tie to each record now.

Is this making sense still?
<?php
while($info = mysql_fetch_array( $data ))
{
?>
  <select name="orderDB_<?=$info['docID']?>" id="orderDB_<?=$info['docID']?>">
  <?php
   echo "<option value=''></option>
   <option value='01'>1</option>
   <option value='02'>2</option>
   <option value='03'>3</option>
   <option value='04'>4</option>
   <option value='05'>5</option>
   <option value='06'>6</option>
   <option value='07'>7</option>
   <option value='08'>8</option>
   <option value='09'>9</option>
   <option value='10'>10</option>
   <option value='11'>11</option>
   <option value='12'>12</option>
   <option value='13'>13</option>
   <option value='14'>14</option>
   <option value='15'>15</option>
   <option value='16'>16</option>
   <option value='17'>17</option>
   <option value='18'>18</option>
   <option value='19'>19</option>
   <option value='20'>20</option>
   </select><br />";  
  echo "</b></td></tr>";
  if ($records%2==1)
    echo "";
  $records++; //---------------
}
?>

Open in new window

0
 

Author Comment

by:rcimasi
ID: 23683411
I think I understant but I am having a heck of a time understanding how it works together with the initial scripts and the Update script..
0
 
LVL 12

Expert Comment

by:Richard Davis
ID: 23683724
We're going to get to that right now.

Now that you have uniquely named <select...> tags, the next phase comes into play.
You now need your PHP script to create a dynamic UPDATE query that will contain ALL of the submitted data from those select options.

Okay...here's some sample code...give this a shot.
<?php
	if($_POST['submit'])
	{
		print "<pre>";
		print_r($_POST);
		print "</pre>";
		
		$i = 0;
		foreach($_POST as $key => $val)
		{
			// lets only use the orderDB keys from the posted form values
			if(substr($key, 0, 7) == 'orderDB')
			{
				// break out the record ID from the select tag's name
				$rParts = split('_', $key);
				// apply the record ID to the UPDATE query in the where clause
				$sqlOut[$i] = "UPDATE documents SET fileorder=$val WHERE docID={$rParts[1]}";
			}
			$i++;
		}
		
		// perform all sql statements now
		for($s = 0; $s < count($sqlOut); $s++)
			//replace print statement with this mysql_query($sqlOut[$s]);
			print $sqlOut[$s].'<br />';
	}
?>
 
<form name="myForm" method="post" action="<?=$_SERVER['PHP_SELF']?>">
<?php
for($i = 0; $i < 3; $i++)
{
	print "Select $i<br />";
  print "<select name=\"orderDB_".$i."\" id=\"orderDB_".$i."\">";
  print " <option value=''></option>
 <option value='01'>1</option>
 <option value='02'>2</option>
 <option value='03'>3</option>
 <option value='04'>4</option>
 <option value='05'>5</option>
 <option value='06'>6</option>
 <option value='07'>7</option>
 <option value='08'>8</option>
 <option value='09'>9</option>
 <option value='10'>10</option>
 <option value='11'>11</option>
 <option value='12'>12</option>
 <option value='13'>13</option>
 <option value='14'>14</option>
 <option value='15'>15</option>
 <option value='16'>16</option>
 <option value='17'>17</option>
 <option value='18'>18</option>
 <option value='19'>19</option>
 <option value='20'>20</option>
</select><br />";
}
?>
<input type="submit" name="submit" value="Submit" />
</form>

Open in new window

0
 

Author Comment

by:rcimasi
ID: 23684043
I seems to be doing what it should kinda.. LOL  here is the output. No change in the Db

Array
(
    [orderDB_0] =>
    [orderDB_1] =>
    [orderDB_2] => 15
    [submit] => Submit
)


UPDATE documents SET fileOrder= WHERE DocID=0
UPDATE documents SET fileOrder= WHERE DocID=1
0
 
LVL 12

Accepted Solution

by:
Richard Davis earned 1500 total points
ID: 23684087
Here's your original code modified with my implementations. Perhaps that will make things a bit easier.
<?php
$con = mysql_connect("localhost","root","");
mysql_select_db("pass", $con);
 
if(!$con)
  die('Could not connect: ' . mysql_error());
 
$hotdog = "roomnumber";
 
// Displays documents and their information from MySql
$data = mysql_query("SELECT * FROM documents WHERE roomnumber='$whichRoom'") or die(mysql_error());
 
$records=0;
while($info = mysql_fetch_array( $data ))
{
  if ($records%2==0)
  echo "<tr>";
  echo "<td>";
  echo "<input type='text' value='" .$info["roomnumber"]. "' name='roomNumb' style='border:0px;' readonly></td><td>";
  echo $info["docname"]."</td><td>";
  echo $info["docfile"]."</td><td>";
  echo $info["doctype"]."</td><td>";
  echo $info["docdate"]." </td><td>";
  echo $info["fileOrder"]." </td><td>";  
  ?>
  
  <select name="orderDB_<?=$info["docID"]?>" id="orderDB_<?=$info["docID"]?>">
  <?php
   echo "<option value=''></option>
   <option value='01'>1</option>
   <option value='02'>2</option>
   <option value='03'>3</option>
   <option value='04'>4</option>
   <option value='05'>5</option>
   <option value='06'>6</option>
   <option value='07'>7</option>
   <option value='08'>8</option>
   <option value='09'>9</option>
   <option value='10'>10</option>
   <option value='11'>11</option>
   <option value='12'>12</option>
   <option value='13'>13</option>
   <option value='14'>14</option>
   <option value='15'>15</option>
   <option value='16'>16</option>
   <option value='17'>17</option>
   <option value='18'>18</option>
   <option value='19'>19</option>
   <option value='20'>20</option>
   </select><br />";  
  echo "</b></td></tr>";
  if ($records%2==1)
    echo "";
  $records++; //---------------
}
?>
 
// MySQL update script that follows code (form) above
<?php
 
$whichRoom2 = $_POST["roomNumb"];
 
$i = 0;
foreach($_POST as $key => $val)
{
  // lets only use the orderDB keys from the posted form values
  if(substr($key, 0, 7) == 'orderDB')
  {
    // break out the record ID from the select tag's name
    $rParts = split('_', $key);
    // apply the record ID to the UPDATE query in the where clause
    $sqlOut[$i] = "UPDATE documents SET fileorder=$val WHERE roomnumber=$whichRoom2 AND docID={$rParts[1]}";
  }
  $i++;
}
                
// perform all sql statements now
for($s = 0; $s < count($sqlOut); $s++)
  mysql_query($sqlOut[$s]);
 
echo "<div align='center'>File Order Has Been Changed<br /><a href='javascript:history.back()'>Go Back</a></div>";
?>

Open in new window

0
 

Author Closing Comment

by:rcimasi
ID: 31548361
Thank you very much!
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month18 days, 8 hours left to enroll

825 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