Update Multiple MySQL Records With 1 Form

lowside67

Hello all,

I have been searching and reading and I cannot for the life of me figure out how to accomplish what I need to do. I'm a phpnoob so bear with me here.

Basically what I have is a form which pulls data from two tables joined using a common stock number field to form one line for each vehicle. I only need to update one table but I want to be able to hit Submit one time and have it update each record.

I have attached a screenshot of the form to give an idea of what I am trying to do. The "View" button is independent of what I need to do here and just links to an external page referencing the stock number.

I have each of those fields currently named as FIELDNAME[stocknumber] and also with a matching id as per some tutorials online. However, I'm at a loss for where to start on the action file end as I'm really quite helpless at arrays.

I need to update a form called "status_report" with each row being structured as stock, opt1, opt2, opt3... etc where opt are tinyints that just need to be either a 1 or 0 depending on the status of the checkboxes.

Any help would be appreciated!

Regards,
Mark

jgetner

can you submit some code so we can try and figureout you a solution?.

dalecosp

Do you have control of the HTML in the form? (eg, can you change its structure somewhat?)

lowside67

Yes I can change the way it is named, etc, but I need to maintain the look and organization of the HTML page.

Please excuse my horrible syntax, this is how the page is coded:

STATUS.PHP

<form method="post" action="status_submit.php">
<table cellpadding="2" cellspacing="0">
<tr><td colspan="3" style="font-size: 16pt; padding-bottom: 8px;">Vehicle Status Report</td><td colspan="12" style="font-size: 16pt; vertical-align: middle; text-align: right;"><input type="submit" value="Save Status Report"></td></tr>
<tr>
<td width="70" style="border-bottom: 2px solid; text-align: center; border-left: 1px solid; border-right: 1px solid;"><b> <a href="status.php?sort=datein">Date In</a></b></td>
<td width="295" style="border-bottom: 2px solid; border-right: 1px solid;"><b>Vehicle - <a href="status.php">Year</a> | <a href="status.php?sort=make">Make/Model</a> | <a href="status.php?sort=stock">Stock</a></b></td>
<td width="180" style="border-bottom: 2px solid; border-right: 1px solid; text-align: center;"><b>Exterior Colour</b></td>
<td width="60" style="border-bottom: 2px solid; text-align: center;"><b>Inspect</b></td>
<td width="60" style="border-bottom: 2px solid; text-align: center;"><b>RIV</b></td>
<td width="60" style="border-bottom: 2px solid; border-right: 1px solid; text-align: center;"><b>Regi</b></td>
<td width="60" style="border-bottom: 2px solid; text-align: center;"><b>Body</b></td>
<td width="60" style="border-bottom: 2px solid; text-align: center;"><b>Tires</b></td>
<td width="60" style="border-bottom: 2px solid; border-right: 1px solid; text-align: center;"><b>Wheels</b></td>
<td width="60" style="border-bottom: 2px solid; text-align: center;"><b>Detail</b></td>
<td width="60" style="border-bottom: 2px solid; border-right: 1px solid; text-align: center;"><b>Interior</b></td>
<td width="60" style="border-bottom: 2px solid; border-right: 1px solid; text-align: center;"><b>Keys</b></td>
<td width="45" style="border-bottom: 2px solid; text-align: center;"><b>Ad</b></td>
<td width="45" style="border-bottom: 2px solid; border-right: 1px solid; text-align: center;"><b>Pics</b></td>
<td width="55" style="border-bottom: 2px solid; border-right: 1px solid; text-align: center;"><b>Notes</b></td>
</tr>

<?php
mysql_connect("localhost", "DATABASE", "PASSWORD");
mysql_select_db(DATABASE);
$sortby = "my_pimpin_rides.year DESC, my_pimpin_rides.make ASC, my_pimpin_rides.model ASC";
if ($sort == "datein")
	$sortby = "my_pimpin_rides.datebought DESC";
if ($sort == "make")
	$sortby = "my_pimpin_rides.make ASC, my_pimpin_rides.model ASC, my_pimpin_rides.year DESC";
if ($sort == "stock")
	$sortby = "my_pimpin_rides.stock ASC";
$carcount = 0;	
$strQuery = "SELECT * ".
 "FROM my_pimpin_rides,status_report ".
	"WHERE my_pimpin_rides.stock = status_report.stock AND my_pimpin_rides.datesold IS NULL ORDER BY ".$sortby;
$result = mysql_query($strQuery);
while ($data = mysql_fetch_array($result, MYSQL_ASSOC)) {
	$carcount++;
	if ($carcount % 2 == 0) $bgcolor = "rgb(220,220,220)";
	else $bgcolor = "rgb(255,255,255)";
	echo "<tr>";
	echo "<td height=\"18\" style=\"border-left: 1px solid rgb(0,0,0); border-right: 1px solid rgb(0,0,0); text-align: center; background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); color: rgb(100,100,100);\">".$data[datebought]."</td>";
	if ($sort == "stock")
		echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); border-right: 1px solid;\">(Stock <b style=\"color: rgb(200,0,0);\">".$data[stock]."</b>) ".$data[year]." ".$data[make]." ".$data[model]."</td>";	
	else
		echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); border-right: 1px solid;\"><b>".$data[year]." ".$data[make]." ".$data[model]."</b> (Stock <b>".$data[stock]."</b>)</td>";
	echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); border-right: 1px solid; text-align: center;\">".$data[extcolor]."</td>"; 

//BEGIN CHECKBOXES

if ($data[inspect] == 0) echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); text-align: center;\"><input type=checkbox name=\"inspect[".$data[stock]."]\" id=\"inspect[".$data[stock]."]\"></td>";
	else echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); text-align: center;\"><input type=checkbox name=\"inspect[".$data[stock]."]\" id=\"inspect[".$data[stock]."]\" checked></td>";

if ($data[riv] == 0) echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); text-align: center;\"><input type=checkbox name=\"riv[".$data[stock]."]\" id=\"riv[".$data[stock]."]\"></td>";
	else echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); text-align: center;\"><input type=checkbox name=\"riv[".$data[stock]."]\" id=\"riv[".$data[stock]."]\" checked></td>";

if ($data[regi] == 0) echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); border-right: 1px solid; text-align: center;\"><input type=checkbox name=\"regi[".$data[stock]."]\" id=\"regi[".$data[stock]."]\"></td>";
	else echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); border-right: 1px solid; text-align: center;\"><input type=checkbox name=\"regi[".$data[stock]."]\" id=\"regi[".$data[stock]."]\" checked></td>";

if ($data[body] == 0) echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); text-align: center;\"><input type=checkbox name=\"body[".$data[stock]."]\" id=\"body[".$data[stock]."]\"></td>";
	else echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); text-align: center;\"><input type=checkbox name=\"body[".$data[stock]."]\" id=\"body[".$data[stock]."]\" checked></td>";

if ($data[tires] == 0) echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); text-align: center;\"><input type=checkbox name=\"tires[".$data[stock]."]\" id=\"tires[".$data[stock]."]\"></td>";
	else echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); text-align: center;\"><input type=checkbox name=\"tires[".$data[stock]."]\" id=\"tires[".$data[stock]."]\" checked></td>";

echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); border-right: 1px solid; text-align: center;\"><input type=\"text\" size=\"1\" maxlength=\"1\" name=\"wheels[".$data[stock]."]\" id=\"wheels[".$data[stock]."]\" value=\"".$data[wheels]."\"</td>";

if ($data[detail] == 0) echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); text-align: center;\"><input type=checkbox name=\"detail[".$data[stock]."]\" id=\"detail[".$data[stock]."]\"></td>";
	else echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); text-align: center;\"><input type=checkbox name=\"detail[".$data[stock]."]\" id=\"detail[".$data[stock]."]\" checked></td>";

if ($data[interior] == 0) echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); border-right: 1px solid; text-align: center;\"><input type=checkbox name=\"interior[".$data[stock]."]\" id=\"interior[".$data[stock]."]\"></td>";
	else echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); border-right: 1px solid; text-align: center;\"><input type=checkbox name=\"interior[".$data[stock]."]\" id=\"interior[".$data[stock]."]\" checked></td>";

if ($data[keys] == 0) echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); border-right: 1px solid; text-align: center;\"><input type=checkbox name=\"keys[".$data[stock]."]\" id=\"keys[".$data[stock]."]\"></td>";
	else echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); border-right: 1px solid; text-align: center;\"><input type=checkbox name=\"keys[".$data[stock]."]\" id=\"keys[".$data[stock]."]\" checked></td>";

//END CHECKBOXES

if ($data[online] == 0) echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); text-align: center;\">No</td>";
	else echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); text-align: center;\">Yes</td>";
echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); border-right: 1px solid; text-align: center;\">";
if (file_exists('../data/'.$data[stock].'/')) echo "Yes</td>";
else echo "No</td>";

echo "<td style=\"background-color: ".$bgcolor."; border-bottom: 1px solid rgb(155,155,155); border-right: 1px solid; text-align: center;\"><a href=\"#\">View</a></td>";
echo "</tr>"; }
echo "</table>";
?>
</form>