Solved

Using a HTML table to update mysql records

Posted on 2009-04-15
9
603 Views
Last Modified: 2013-12-12
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
0
Comment
Question by:sys_admin_pic
  • 6
  • 3
9 Comments
 
LVL 1

Accepted Solution

by:
krv123 earned 75 total points
ID: 24150217
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
 

Author Comment

by:sys_admin_pic
ID: 24160493
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
 

Author Comment

by:sys_admin_pic
ID: 24162001
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
 

Author Comment

by:sys_admin_pic
ID: 24162024
Also,

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

Thanks
Matt
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 1

Expert Comment

by:krv123
ID: 24162314
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
 

Author Comment

by:sys_admin_pic
ID: 24162334
Sure no problems, I am sure they can cope on it as it is!

Great answer - points are all yours...

Cheers
Matt
0
 

Author Comment

by:sys_admin_pic
ID: 24162365
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
 
LVL 1

Expert Comment

by:krv123
ID: 24163678
what browser are you using?
0
 

Author Comment

by:sys_admin_pic
ID: 24174504
I have tested it in IE and Firefox
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PHPStorm debugging issues 1 25
MySQL Grouping 2 25
php construct 5 15
Excel - SQL export question 3 19
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now