Search code examples
phpdatabasesql-delete

how can i save just one for that day not more than one for that day , using php code `DELETE`

I save stats for my website i got problem my stats are saved more than once for day, i tried to write php code to delete other for that day and keep just one Here is photo of my database saved

if you see date 2018-08-13 are saved 3 times some of them are saved once or twice or more for that day,

Real Question: How can i keep only one for that day using php code DELETE

For time is saved in database time();

output in photo is date('Y-m-d H:i',$getstats['time']);

(If you want you can edit to make it more clear to undestand)

Solution

  • A. THE DIRECT ANSWER TO YOUR QUESTION

    1. I assume you are using MySQL
    2. I assume your table name is stats

    Step 1: Add Primary Key column + constraint to your table

    ALTER TABLE `stats`
ADD COLUMN `id` INT(11) NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (`id`);

    Step 2: Create procedure as below

    BEGIN
    DECLARE idToRemove INT;
    WHILE exists(select count(*) from stats group by date(time) having count(*) > 1) DO
        select min(id) into idToRemove from stats group by date(time) having count(*) > 1 limit 1;
        delete from stats where id = idToRemove;
    END WHILE;
END

    Step 3: Execute procedure

    call removeDuplicates()

    B. THINGS TO CONSIDER

    1. Primary Key should remain
    2. why the duplicates are even created? How about adding UNIQUE constraint on time column and use INSERT IGNORE ... statement in your code?