Updating multiple records at one time in MySQL with PHP

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");
}
 
?>

Select allOpen in new window

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");
}

Select allOpen in new window

is this provide any help to you ....


https://www.experts-exchange.com/questions/24153308/How-to-REPLACE-or-UPDATE-the-existing-records-in-MySQL-db.html

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.

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.

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...

Here is a screenshot of the user interface.

Ooops.. here it is..
userIF.jpg

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

Select allOpen in new window

a dump from "documents" table will be more than helpful

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~

Ok.. The docID field has been added to the table.. Now how can I utilize that to Update multiple records.. Thanks for the hint!

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

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++; //---------------
}
?>

Select allOpen in new window

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..

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>

Select allOpen in new window

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

Thank you very much!