1

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')";
Jason
  • 15,017
  • 23
  • 85
  • 116
Anony123
  • 55
  • 6

4 Answers4

3

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.

Torge
  • 2,174
  • 1
  • 23
  • 33
  • Correct me if I'm wrong, but I think it wouldn't work to have just the date as a primary key. In this case, the primary key should consist of the date _and_ the person since there should only be one entry per day per person. – Anil Jan 28 '15 at 20:11
  • 1
    I just answered the question. And he has only the date as primary key. I don't judge the sense of his implementation. – Torge Jan 28 '15 at 20:13
  • I understand you _partially_ answered his question; however, your solution still will not work as he will not be able to add multiple people entries, per day, which he did state he needed. The way you wrote it, only one entry can ever be added per day if he makes the date the primary key. It is the SO communities job to point out mistakes like this so that the poster is sent off in right track. – Anil Jan 28 '15 at 20:14
  • Please read his question again. He wants only one row per day. – Torge Jan 28 '15 at 20:16
  • Ack, missed that. sorry, you are correct. Sorry for the confusion. Cheers. – Anil Jan 28 '15 at 20:18
  • Doesn't have to be a primary key for `ON DUPLICATE KEY UPDATE...` to work. Any unique key will work. – Marcus Adams Jan 28 '15 at 20:47
  • thanks for your answer, unfortunately, this doesn't work. I don't know why, but whenever there is new data inserted in the database on the same date, it makes a second row. There should only be one row per day. the rest doesn't matter – Anony123 Jan 31 '15 at 21:21
  • Either you forgot to make the Date column primary or unique, or it is actually a Date and Time format. Else this is not possible. – Torge Feb 02 '15 at 12:34
3

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'
ReallyMadeMeThink
  • 1,061
  • 7
  • 11
1

Try using REPLACE INTO instead of using INSERT INTO.

Goddard
  • 2,863
  • 31
  • 37
  • that doesn't work cause then it always replaces the previous row, even though it's another day. It should make a new row if it's the next day. – Anony123 Jan 31 '15 at 21:22
  • Then you should add more then 1 key. – Goddard Jan 31 '15 at 23:00
  • how doyou mean add more than 1 key? – Anony123 Feb 01 '15 at 17:29
  • You want to Update rather then insert if it is the same date correct? If that is the case you should make it a key because SQL will update based on finding a duplicate key. So make your two keys and it will update. If the date changes then it will insert. – Goddard Feb 02 '15 at 03:19
1

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

Community
  • 1
  • 1
Jonathan
  • 1,542
  • 3
  • 16
  • 24
  • This will throw away the statement completely. But he wants information to be added, just not in a new, but in the current row. – Torge Jan 28 '15 at 20:20
  • yeah, just re-read the question, got it backwards the first time :P – Jonathan Jan 28 '15 at 20:21