Using a HTML table to update mysql records

Hi,

I am currently designing a website that will display league standings, sorted by point value. I have worked out how I can display the data in a table now and in the way that I want, but the website has to be updatable. Is it possible to design a table that will update points when entered into a field?

For example, would it be possible to grab the team names and place them in a dropdown menu, then use a seperate text field to input the point value, click submit which would add that value onto the existing value for the relevant team?

Either that, or display the teams in a list on a page so you can edit multiple records at any one time?

I have been looking around but I am struggling at the moment to find an answer

The fields I am using in my table are:

tname  - team name
points - current points.

Many Thanks
Matt
sys_admin_picAsked:
Who is Participating?
 
krv123Connect With a Mentor Commented:
Not sure what your db structure is but the code snippet should do what you need.
<?php
            if (isset($_POST['update'])) {
                $teamId = $_POST['hidId'];
                $pointValue = $_POST['txtPoints'];
                $numItem = count($pointValue);
                for ($i = 0; $i < $numItem; $i++) {
                    $newValue = (int)$pointValue[$i];
                    $sql = "UPDATE  team_data SET tpoints = $newValue WHERE id = {$tId[$i]}";
                    dbQuery($sql);
                    echo 'Updated';
                }
            }
?>
<form method="POST">
<table>
<tr>
	<td>Team Name</td>
	<td>Team Points</td>
	<td>New Points</td>
</tr>
<?php
$sql = "SELECT id,tname,tpoints FROM team_data";
$result = mysql_error($sql)or die(mysql_error());
 
while ($row = mysql_fetch_assoc($result)) {
	$team_id = $row['id'];
	$team_name = $row['tname'];
	$team_points = $row['tpoints'];
?>
<tr>
 
<td><?php echo $team_name; ?></td>
<td><?php echo $team_points; ?></td>
<td><input name="txtPoints[]" type="text" value="<?php echo $team_points; ?>" />
<input name="hidId[]" type="hidden" value="<?php echo $team_id; ?>" />
</td>
<td></td>
</tr>
<?php
}
?>
</td>
<td colspan="3" align="right"><input type="submit" value="Update" name="update" /></td>
</table>
</form>

Open in new window

0
 
sys_admin_picAuthor Commented:
Hi KRV123 - many thanks for your help.

Looks brilliant, but also I am getting an error on the following line:
$result = mysql_error($sql)or die(mysql_error());

Error as follows:
Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in E:\websvr\hosttest.php on line 36

Can you offer any help?

Thanks
Matt
0
 
sys_admin_picAuthor Commented:
Thats for that answer krv123. I have been playing with the code and have got it working now :)

There was a miss type on {$tId[$i]} - should have read {$teamId[$i]} so not to worry.

Just a quick one though - is there any way of getting the form to just add values on to the end, ie, start with blank fields and add on only the fields that have been filled in?

For example:

Team 1 gets 10 points, so you enter 10 in team 1's field - that adds on when you click update, but ignores all of the other empty fields in the form?

Thanks
Matt
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
sys_admin_picAuthor Commented:
Also,

When you click update - it prints "updated" 7 times across the page

Thanks
Matt
0
 
krv123Commented:
Take the echo out of the for loop. and as far as your other question about only updated the values you entered, you would have to use checkboxes. they are not fun when you have to check 10 + line items
0
 
sys_admin_picAuthor Commented:
Sure no problems, I am sure they can cope on it as it is!

Great answer - points are all yours...

Cheers
Matt
0
 
sys_admin_picAuthor Commented:
Just a quick clarification - I have entered a action="sitetest.php" into the form bracket, so when I click update, it refreshes the table.

I notice that it doesnt refresh the whole page - is there a way to do this?

Thanks
Matt
0
 
krv123Commented:
what browser are you using?
0
 
sys_admin_picAuthor Commented:
I have tested it in IE and Firefox
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.