Updating Nascar Points List with a Form - Newbie Help

crickert

This is a project for a fantasy nascar league I'm running.

I've got a page that lists 45 nascar drivers. It lists their CarNumber, DriverName, Points, Group. As you can see in the code, the Points column is a field that the user can change. Each week, I will change the points to reflect the official points standing.

The first page works fine. However, my update code is wrong because when I submit the form, it updates all the drivers with the same number of points that was input for the 45th record. I think some sort of loop is needed in the processpoints.php code but I am not sure how this is done 🙂

The code below is my form and the part I'm having trouble is with the <form action = processpoints.php .... which is also below.

$connection = mysql_connect($dbhost, $dbuser, $dbpass)
or die ("Not Able to connect");

$db = mysql_select_db($dbname, $connection)
or die ("Not Able to connect");

echo "<br>Update the Points For Each Driver - Then Hit Submit</br><hr>";

$query = "SELECT * FROM DriversORDER BY Points DESC";

$result = mysql_query($query)
or die ("Not Able to connect");

while ($row = mysql_fetch_array($result))
{
echo "<table cellspacing=0 border=0>\n";
extract($row);
echo "<form action='processpoints.php' method='post'>
<tr>\n
<td width=55>$CarNumber</td>\n
<td width=100>$DriverName</td>\n
<td><input type='text' name='Points' size = '6' maxlength='11' value='$Points'> </td>\n
<td width=55>$Group</td>\n
</tr>";

}

echo "</table>\n";

echo "<p align='left'><input type= 'submit' value='Submit Points Change'</p>
</form>";
?>

Here is the processpoints.php code (i know it's not correct. This is the part I need help on)

<?php

$dbhost =
$dbuser =
$dbpass =
$dbname = "nascar";
$connection = mysql_connect($dbhost, $dbuser, $dbpass)
or die ("Not Able to connect");

$db = mysql_select_db($dbname, $connection)
or die ("Not Able to connect");

$query = "UPDATE Drivers SET Points=$Points";

$result = mysql_query($query)
or die ("Not Able to connect");

bastien

you need to use the 'where' clause to limit the update to just one driver... add a hidden input field to hold the driver id from the database and use this value to update the correct driver

<td width=100>$DriverName</td>\n
<input type=hidden value='$driverid'>

$query = "UPDATE Drivers SET Points=$Points where id = $driverid";

crickert

getting closer. i agree with the hidden field for the driverID

I made the changes but it only updates the last record in the 45 record list.

I would like to plug different values in for all 45 records and then have those new values update the database at once.

don't i need to loop through each of the 45 records to update each one?

bastien

okay that description changes things a bit...we now will popluate the array with the drivers id in a hidden field and create an array of textbox fields


$i=1;   //counter for 
echo "<table cellspacing=0 border=0>"; 
while ($row = mysql_fetch_array($result)) 
{ 

extract($row); 
echo "<form action='processpoints.php' method='post'> 
<tr>
<td width=55>$Group</td>
<td width=55>$CarNumber</td> 
<td width=100>$DriverName 
[b]<input type=hidden value='driversid[$i]'></td>[/b]

<td><input type='text' name='Points[$i]' size = '6' maxlength='11' value='$Points'> </td></tr>";
}   //end while loop

then on the receiving page just use a for loop to get each value out of the array

for ($x=0;$x<=count($_POST['driversid']);$x++;){

$sql_update="update tablename set points=".$_POST['Points[$x]']." where id = ".$_POST['driversid[$x]'];

$result=mysql_query($sql_update);
}
?>

crickert

The form works fine but it is still not updating with the for loop to step through each driver(row). I've tried changing a few things - but haven't been successful yet. I don't think the syntax in the $sql_update you put up is correct. i changed it to the following - but heck it's wrong becuase i keep getting errors. I could swear this syntax matches the update statement in my PHP/MYSQL for Dummies book.

$query ="UPDATE Drivers SET Points = '$POST['Points[$x]']' WHERE CarID = '$POST['driverid[$x]']' ";

but i'm getting this error

Parse error: parse error, expecting T_STRING' orT_VARIABLE' or `T_NUM_STRING' in /www/processpoints.php on line 25

Also, I am a little unclear on whether the hidden value - driversid needs to be an actual field in the database? I already have a field in the database called CarID. Or is the hidden field - driversid just temporary.

bastien

carID will work fine, i was not sure what priomary key you where using for the table...i'll make the change...also gonna try assinging the some variables to try and fix the sql

for ($x=0;$x<=count($_POST['carID']);$x++{

$car=$POST['carID[$x]'];
$points=$POST['Points[$x]'];

//to test lets echo car and points to ensure the values are set
//remember to comment out if all works fine
echo "car is :$car"<br>points are: $points<br>";

//remember to add quotes if carID is alphanumeric field
$sql_update="update tablename set points= $points where id = $car";

$result=mysql_query($sql_update);
}
?>

crickert

you wrote:

for ($x=0;$x<=count($_POST['carID']);$x++{

are you missing a parenthis on the end of the loop before the bracket?

for ($x=0;$x<=count($_POST['carID']);$x++){

also you wrote:

//remember to add quotes if carID is alphanumeric field
$sql_update="update tablename set points= $points where id = $car";

should it be:

$sql_update="update tablename set points= $points where CarID = $car";

and what do you mean in your comment to add quotes if CarID is alphanumeric. In my database, the CarID field is an integer.

here is a working the working form

chrisrickert.com/drivers.php

bastien

whoops sorry about the typos...yes they should be changed/parantheses added.

the add quotes comment refers to any field that take alphanumeric data, those fields should use quotes around the values...in your case there is no problem as the datatype is an integer..