0
+----+---------------+--------------------+--+
| id |    user_id    |       createAt     |  |
+----+---------------+--------------------+--+
|  1 |  1            |2022-05-12 11:36:44 |  |
|  2 |  1            |2022-05-12 11:36:50 |  |
|  3 |  1            |2022-05-12 11:37:01 |  |
|  4 |  2            |2022-05-15 19:22:20 |  |
|  5 |  2            |2022-05-15 19:22:25 |  |
|  6 |  2            |2022-05-15 19:22:27 |  |
|  7 |  3            |2022-05-16 14:05:44 |  |
|  8 |  3            |2022-05-16 14:05:44 |  |
+----+---------------+--------------------+--+

I have a table like the one above. There are about 100 thousand such records in the table. I want to delete the parts with the same user_id and same date from this table (I want it to be deleted even if there is a +- 5 minutes difference in the date part). How can I do that. I tried many ways but failed. Sorry for my bad english. Thank you.

Edit: I tried this code but not work with +-5 min

$sql = "SELECT id, user_id, DATE_FORMAT(createAt,'%Y-%m-%d %H:%i') as month_date FROM mytable WHERE user_id= '".$_GET['accountId']."' GROUP BY month_date HAVING COUNT( * ) >1";

if ($result=mysqli_query($con,$sql)) {
  while($row = mysqli_fetch_array($result)){
        echo $row["month_date"]. " ". $row["user_id"]. "<br>";
    }
}
lallana
  • 3
  • 2
  • Please show what you tried then we don't need to repeat the same mistakes again – ADyson Jul 22 '22 at 16:01
  • I have added my code @ADyson – lallana Jul 22 '22 at 16:24
  • Can you add the resulting table after deleting the rows? – The Impaler Jul 22 '22 at 16:55
  • I haven't done the deletion yet because I couldn't find the right code @TheImpaler – lallana Jul 22 '22 at 17:24
  • You said you tried many ways...show your best attempt and explain the problem – ADyson Jul 22 '22 at 18:04
  • Ps. Your select code is very vulnerable to sql injection attacks and related problems. Please always use prepared statements and parameters to construct your queries safely and reliably. See https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php for instructions. – ADyson Jul 22 '22 at 18:06
  • With the above code, I can list data with the same date, but I cannot list data that is +-5 minutes from the date. This code is the best I could write. – lallana Jul 22 '22 at 18:09

0 Answers0