Updating dynamically formatted rows

endlyss

Hello

I'm attempting to design an online program for my employers for company use. I'm sure it's not that hard to do but I am stuck.

Basics of script...
I make a simple query to mySQL database and end up with a few rows of data outputted with a couple of fields formatted as text boxes waiting for input to update the table.

So far so good. At the end of each row of data I have a a submit button. What I want to do is to be able to update each row individually (with the option of updating all rows at once).

The way it's working now only the data in the last row is being used to update and is updating all fields in the database globally. I am unable, as yet, to find a way to work with each individually.

I suspect I need a way to give each text box a unique name that the next (update) SQL statement understands. I tried using $n1, which is unique to each row, as the name but it doesn't work.

Thank you.

Relevant code follows...

SQL involved...

$sql = "select zone, DATE_LAST_VISITED, ZONE_DESCRIPTION, CURRENT_STATUS from WESTERN_WASHINGTON where current_status = 'off' ORDER BY date_last_visited ASC";
..................................

// start results formatting
echo "<form action='http://127.0.0.1/php101/racks/assign.php'>";
echo"<table border= 1 align= CENTER WIDTH=550 >";

echo"<tr><form><th> Zone </th><th> Date Last Visited </th><th> Zone Description</th><th>Current Status</th><th>Truck #</th><th>Driver</th>";

while ($row = mysql_fetch_array($sql_result)) {

$n1 = $row["zone"];
$n2 = $row["DATE_LAST_VISITED"];
$n3 = $row["ZONE_DESCRIPTION"];
$n4 = $row["CURRENT_STATUS"];
$n5 = $row["truck_no"];
$n6 = $row["driver"];

echo "<tr>
<td>$n1</td>
<td>$n2</td>
<td>$n3</td>
<td>$n4</td>
<td><input type='text' size='7' name='truck'></td>
<td><input type='text' name='driver'><input type='submit' value='Assign Zone'></td>
</tr>";
}
echo"</table></form>";

next script SQL...

$sql = "update WESTERN_WASHINGTON set truck_no = '?', driver = '?' where zone = '?'";

Anon

It looks like from your php script that you are outputting a form and posting to assign.php...

echo \\\\"<form action=\\\\'http://127.0.0.1/php101/racks/assign.php\\\\'>\\\\";

how are you handling the posting... isn\\\\'t that where your updating sql and php routines are?

endlyss

Thanks jasonzzz

If I understand you right... yes I am posting to assign.php. At this point it's bare minimum with assign.php using the SQL statement...

$sql = "update WESTERN_WASHINGTON set truck_no = '$truck'";

Of course I've tried a million variations trying to get that truck_no variable to work with only the submit button that belongs to that row.

The script works but will only use the values in the last row. In fact sometimes even then I get odd inputs such as submitting 727 I end up with 127 in the database.

kparker

$sql = "update WESTERN_WASHINGTON set truck_no = '$truck'";

Well, this statement is telling the dbms to update every row. In the absence of any WHERE clause, it affects everything.

Regarding your difficulty in tying it to a SUBMIT button, I assume you have a primary key value that uniquely identifies each row? You should be able to concatenate that value to a button name, and then react to the button name in your action script.

endlyss

Actually I started with the where clause. Could not get it to work. Tried everything. Yes there is one value that uniquely identifies each row which is "zone"

$n1 = $row["zone"];

$sql = "update WESTERN_WASHINGTON set truck_no = '?', driver = '?' where zone = '?'";

? = variable I'm not sure of especially in the where clause.

Thanks

kparker

Then yes, saying

$qry = "update WESTERN_WASHINGTON set truck_no = '$truck',
driver = 'Wiley Coyote' where zone = '$target_zone';

is just what you want.

But oh my goodness--I just noticed your table name! You really don't want this kind of database design, where part of the data is encoded in the table name. If you have trucks in Eastern Washington, they are exactly the same kind of thing as trucks in Western Washington, just in a different region, so they really belong in the same table together. (I completely understand if you still want to make a separate table for California.)

I would definitely not move a bit further until you get your table design checked out and decently normalized.

endlyss

Looking into it.

Thanks Kirk