CODEWITHSUNDEEP
phpmysqldaterow
Anony123
Anony123

Reputation: 55

MySQL - make row and update if already exists

I have table with 4 columns: Date, John, Frank, Anthony

I want to make a form which can be filled in by these 3 people everyday and store these values in the database. When John fills in the form today, a new row should be created with the date of today, with his value in the database. When Frank fills in the form 1 hour later (so the same day) his values should also be inserted in the database but in the same row because there's already a row with today's date. But when Anthony fills in the form tomorrow, a new row should me created with the date of tomorrow.

So in short: the program checks if anyone has already filled in the form today. If yes: it just adds the value to the existing row of today in column of the person who filled it in. if not: it makes a new row with the date of today.

I already wrote this code, but the problem is that it makes a new row everytime someone fills in the form, and there should only be a row created if $person is the first one to fill in the form on that day.

$sql = "INSERT INTO table (Date, $name) VALUES (CURDATE(),'$values')";

Upvotes: 1

Views: 1692

Answers (4)

Torge
Torge

Reputation: 2284

First make sure that the Date field is a primary or unique key. Then you can use ON DUPLICATE KEY UPDATE

$sql = "INSERT INTO table (Date, $name) VALUES (CURDATE(),'$values') 
        ON DUPLICATE KEY UPDATE $name='$values'";

But you really should check for prepared statements in your language (PDO in case of PHP) to prevent SQL injections.

Upvotes: 3

ReallyMadeMeThink
ReallyMadeMeThink

Reputation: 1071

You should change your table.

Instead of a column for each person make a name column so you have:

Date | Name | Values

Make date and person the primary key:

ALTER TABLE 'table' ADD PRIMARY KEY (Date,Name)

Then insert like this:

INSERT INTO table (Date,Name,Values) VALUES (CURDATE(),'$name','$values') ON DUPLICATE KEY UPDATE Values='$values'

Upvotes: 3

Jonathan
Jonathan

Reputation: 1564

EDIT

I just re-read the question - the OP wants to overwrite the data if the user re-submits the form - first time I read it I thought they wanted to keep the original data

An alternative [if you only wanted to keep the original data only], would be using insert ignore : see this answer for a comparison between insert ignore and on duplicate key update: "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

Though you should probably change the table design to be 'date', 'person', 'data' as suggested in another answer

Upvotes: 1

Goddard
Goddard

Reputation: 3059

Try using REPLACE INTO instead of using INSERT INTO.

Upvotes: 1

Related Questions