Update Multiple Rows

[sseeley]

I have about 100 records within my database, each record has a job title from 5 possible job titles.

 

I have listed using PHP every row with a unique ID and a dropdown list for the job titles at the end of the row.  I want to be able to update a number a rows, not in any order.  When I use an update button I want to send via POST all the ID's and drop down selections.  I have managed to automate the post id's i.e. POST1, POST2, POST3.

 

My problem is on the POST page where I want to do the database query, I need to automate the following

 

$POST1 = $_POST[postx];

 

How can I make the x a variable?

 

Many thanks in advance

 

Stuart Seeley

[premiso]

Why does it need to be a variable? Why not just make it access the element of the array. It will be much faster and easier, as you do not have to do the extra code to make it a variable.

 

But if you are one of those hardheaded guys look into extract and or PHP Variable's variable with a foreach loop.

[sseeley]

Ah never used an array...

 

Any coding examples you could help me with?

[ldougherty]

I've done something similar... my code is below.

 

  for($count=0;$count<$numcategories;$count++) {

    ## If Any Information Changes Update Category Table
    if ($newcat[$count]<>$oldcat[$count]) {
      echo "<b>Change Found: Updating Database</b><br>";
      mysql_query("REPLACE INTO `category` (`id`, `name`, `parent`, `thumbimage`, `displayorder`) VALUES
        ('$newid[$count]', '$newcat[$count]','$newparent[$count]','$thumbimage[$count]','$neworder[$count]')");
    }

    echo "<br>";

  } # End For Loop

 

The form has a POST element storing the old value and new value for each item on the chart into an array.  Then I use the FOR loop to parse through the array comparing the old and new and updating mySQL when a change is found.

[premiso]

$sql = "UPDATE table_name SET post1 = '" . $_POST['POST1'] . "', post2 = '" . $_POST['POST2'] . "' WHERE x=x";

 

However, I doubt your column names are post1 etc. I would name your fields the same as what your mysql columns are, that way you can really automate this process if you wanted to and not have to write out each statement like the above.

 

$allowedCols = array("Col1", "col2", "col3", "col4");
$set = array();
foreach ($_POST as $key => $val) {
    if (in_array($key, $allowedCols)) {
         $set[] = " {$key} = '{$val}' ";
    }
}

$setClause = implode(",", $set);

$sql = "UPDATE tablE_name SET {$setClause} WHERE x=x";

 

Makes it a bit easier than defining them all out one by one

 

[sseeley]

I could be getting confused, but here is the form I create with multiple rows.  Each row has two posts, ID, and then the rank value.

	echo "<form action=\"updateRanks.php\" method=\"POST\" name=\"updateRanks\">";
echo "<table class=\"reportsTitleStyle\" border=\"1\">";
	echo "<tr>";
		echo "<td align=\"left\" height=\"20px\" width=\"50px\" style=\"font-family:arial; font-weight:bold; font-size:10px; text-color:black;\">";
			echo "rank";
		echo "</td>";
		echo "<td align=\"left\" height=\"20px\" width=\"100px\" style=\"font-family:arial; font-weight:bold; font-size:10px; text-color:black;\">";
			echo "last name";
		echo "</td>";
		echo "<td align=\"left\" height=\"20px\" width=\"100px\" style=\"font-family:arial; font-weight:bold; font-size:10px; text-color:black;\">";
			echo "first name";
		echo "</td>";
		echo "<td align=\"left\" height=\"20px\" width=\"170px\" style=\"font-family:arial; font-weight:bold; font-size:10px; text-color:black;\">";
			echo "new rank";
		echo "</td>";
	echo "<tr>";
echo "</table>";
echo "<table class=\"reportsTitleStyle\" border=\"1\">";
		while ($i < $num_results)
		{
		$userID = mysql_result($getCadetDetails,$i,"tbluser.pkUserID");
		$rank = mysql_result($getCadetDetails,$i,"tblranks.fkCadetRankID");
		$getRankDescription = mysql_query( " SELECT shortDescription FROM tblcadetranks WHERE pkCadetRankID = $rank " ) or die(mysql_error());
		$lastName = mysql_result($getCadetDetails,$i,"tbluser.lastName");
		$firstName = mysql_result($getCadetDetails,$i,"tbluser.firstName");			                         $rankName = mysql_result($getRankDescription,0,"shortDescription");
			echo "<tr>";
				echo "<td align=\"left\" height=\"20px\" width=\"50px\" style=\"font-family:arial; font-weight:normal; font-size:10px; text-color:black;\">";
					$postID = $post . $i;
					echo "<input type=\"input\" name=$postID id=$postID value=$userID>";
					echo $rankName;
				echo "</td>";
				echo "<td align=\"left\" height=\"20px\" width=\"100px\" style=\"font-family:arial; font-weight:normal; font-size:10px; text-color:black;\">";
					echo $lastName;
				echo "</td>";
				echo "<td align=\"left\" height=\"20px\" width=\"100px\" style=\"font-family:arial; font-weight:normal; font-size:10px; text-color:black;\">";
					echo $firstName;
				echo "</td>";
				echo "<td align=\"left\" height=\"20px\" width=\"170px\" style=\"font-family:arial; font-weight:normal; font-size:10px; text-color:black;\">";
				echo "<select name =$pkUserID size=\"1\">";
					$getAllRanks = mysql_query( " SELECT pkCadetRankID, description FROM tblcadetranks ORDER BY rankPriority DESC " ) or die(mysql_error());
					$num_results1 = mysql_num_rows($getAllRanks);
					$ii = 0;
					while ($ii < $num_results1)
					{
						$rankID = mysql_result($getAllRanks,$ii,"pkCadetRankID");
						if ($rank == $rankID)
									{
									$rankDescription = mysql_result($getAllRanks,$ii,"description");
									echo "<option value=$rankID selected=\"selected\">$rankDescription</option>";
									}
								else if ($rank != $rankID)
									{
									$rankDescription = mysql_result($getAllRanks,$ii,"description");
									echo "<option value=$rankID>$rankDescription</option>";
									}
						$ii++;
					}
				echo "</select>";
				echo "</td>";
			echo "</tr>";
		$i++;
		}
		echo "<tr>";
			echo "<td align =\"right\" colspan =\"4\">";
				echo "<input type=submit value=\"update ranks\">";
			echo "</td>";

 

This creats me a page with all the POST value.  On submit I need to post to another page.

 

Could anyone help me with an example for how I can vary the $_POST variable on the second page.  I am currently unfamiliar with arrays, and am not exactly sure if they are the right things to use.

 

Many thanks in advance.

 

Stuart