Update values in a MySQL table instead of adding new ones

Question

I'm trying to create a way for a user to submit team scores through an HTML form, and store those values in a MySQL table to read back later.

The issue (besides that I am finding random code online) is that I need each instance of the score (Score1, Score2, Score3, etc) to just update its value. So if a new score is added for "Score1" it should replace the old score. Right now it's just creating rows with each new submission listed.

So here is what I was able to put together so far. This is the form that will be duplicated roughly 200 times (I have 200 scores to insert):

<form action="storage.php" id="scores" method="post">
   <input type="number" placeholder="00" maxlength="2" name="score" id="score1">

Then my storage.php file looks like this:

<?php
    $host     = "localhost"; // Host name 
    $username = "scoreboard"; // Mysql username 
    $password = "XXXX"; // Mysql password 
    $db_name  = "admin_scoreboard"; // Database name 
    $tbl_name = "scores"; // Table name 
    // Connect to server and select database.
    mysql_connect("$host", "$username", "$password") or die("cannot connect");
    mysql_select_db("$db_name") or die("cannot select DB");
    // Get values from form 
    $score1 = $_POST['score1'];
    // Insert data into mysql 
    $sql    = "INSERT INTO $tbl_name(score1)VALUES('$score1')";
    $result = mysql_query($sql);
    // if successfully insert data into database, displays message "Successful". 
    if ($result) {
        echo "Successful";
        echo "<BR>";
        echo "<a href='test.html'>Back to main page</a>";
    } else {
        echo "ERROR";
    }
    // close connection 
    mysql_close();
?>

In case this is confusing, my overall goal is to have coaches submit scores for their teams through an HTML form, store that in MySQL, and then display the scores in an HTML table late. But yeah, the main thing is to have the coaches go back and update the scores for the particular team if needed. I'll worry about pulling the data back from MySQL at another time :)

Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[this happens](https://media.giphy.com/media/kg9t6wEQKV7u8/giphy.gif)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions and prepared statements. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, Jul 20 '17 at 13:32
Some sensible code indentation would be a good idea. It helps us read the code and more importantly it will help **you debug your code** [Take a quick look at a coding standard](http://www.php-fig.org/psr/psr-2/) for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. — RiggsFolly, Jul 20 '17 at 13:32
I'm voting to close this question as off-topic because the answer would involve a tutorial session and SO is not a Tutorial Site — RiggsFolly, Jul 20 '17 at 13:34
Thanks guys. I don't mean to have you "code for me". I was obviously tasked with a project that is out of my league. I'll take a look at some of these resources. — user2237247, Jul 20 '17 at 13:36
https://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists — Anandhu Nadesh, Jul 20 '17 at 13:47

score 0 · Answer 1 · edited Jul 20 '17 at 13:39

0

QUERY:

INSERT INTO table (id, name, age) VALUES(1, "A", 19) 
    ON DUPLICATE KEY UPDATE name="A", age=19

try like this..

edited Jul 20 '17 at 13:39

RiggsFolly

83,545
20
96
136

answered Jul 20 '17 at 13:36

score 0 · Answer 2 · answered Jul 20 '17 at 13:52

0

Replace your query with this one

INSERT INTO $tbl_name(score1) VALUES('$score1') 
    ON DUPLICATE KEY UPDATE score1='$score1'

answered Jul 20 '17 at 13:52

Anandhu Nadesh

686
2
11
20

Update values in a MySQL table instead of adding new ones

2 Answers2