I want to check my database when adding new10 rows entry for 24 hours limit not to repeat. How can I achieve it through Php & MySql?

Question

I have a database and I want to avoid repeating any data (say by phone number) for a 24 hour limit when adding 10 rows together. I have saved the time of arrival also by now function but I want to know how to achieve it?

<?php

if (($getdata = fopen($target_file, "r")) !== FALSE) {
    fgetcsv($getdata);
    while (($data = fgetcsv($getdata)) !== FALSE) {
        $fieldCount = count($data);
        for ($c = 0; $c < $fieldCount; $c++) {
            $columnData[$c] = $data[$c];
        }
        $mobile        = mysqli_real_escape_string($connect, $columnData[0]);
        $value         = mysqli_real_escape_string($connect, $columnData[1]);
        $import_data[] = "('" . $mobile . "','" . $value . "',NOW())";
    }
    $import_data = implode(",", $import_data);
    $query       = "INSERT INTO master(name,value,whenadded) VALUES         $import_data ;";
    $result      = mysqli_query($connect, $query);
    $message .= "Data imported successfully.";
    fclose($getdata);
}
?>

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Feb 24 '17 at 07:57

Amr Eladawy · Answer 1 · 2017-03-01T09:01:40.977

Although the sample code is not clear. I think the main question is to prevent adding duplicate columns within time period 24 hours.

The steps to do so is as following:

There should be a column od DATETIME to track the creation date of the record in the DB. It may have the default value CURRENT_TIMESTAMP. Let's assume is called CREATED.
Before inserting your data, make sure to check that there is no duplicate within 24 hours using the following SQL:

Please note that you can put on any column that requires being unique.

 SELECT COUNT(*) AS CONT 
 FROM MY_TABLE 
 WHERE TIMESTAMPDIFF(HOUR,CREATED,NOW())<=24 AND (PHONE = ? OR EMAIL=? OR NAME=? ......)

If the query returns any number greater than 0 then you should return an error the user.

Update As requested in the asker comment below, please check this question about removing duplicate entries keeping only one

sir i have used DATETIME column as whenadded with NOW() function. But in your query i have to check a particular entry manually but i wanted not just to select but to delete if there any duplicacy but remain the one entry. Thanks in advance — Gunjan Sharma, Feb 25 '17 at 11:10

I want to check my database when adding new10 rows entry for 24 hours limit not to repeat. How can I achieve it through Php & MySql?

1 Answers1