-2

image photo

I'm new to mysql query and I'm having issue with lots of same data duplicates on my database when inserting new records.

What I want is to delete old records and keep the latest one only

I have tried offset limit but not working rather deleting all

  INSERT INTO follow 
    (sender_id, receiver_id, name, photo1) 
    VALUES ('".$_POST["sender_id"]."', '".$_SESSION["user_id"]."', '".$phot["user_RealName"]."', '".$phot["photo"]."')
    ";

$gk=mysqli_query($db,"delete from follow where sender_id = '{$_POST['sender_id']}' 
    AND receiver_id = '{$_SESSION['user_id']}'
offset 1 desc   
    ");
Barry Clinton
  • 1
  • 2
  • Can you please make the syntax of the code in your question error free? It would be better if you could find a way to just update a record instead of having to insert and delete. – KIKO Software May 30 '22 at 11:44
  • A simply solution would be to first delete all messages for a particular combination of sender and receiver, before you insert the only record you want to keep. – KIKO Software May 30 '22 at 11:46
  • @KIKOSoftware The insert always duplicate records so deleting first won't work – Barry Clinton May 30 '22 at 11:51
  • @KIKOSoftware update won't work if the user unfollow also – Barry Clinton May 30 '22 at 11:52
  • Your insert statement only inserts one record, it does not duplicate anything. Why do you think it does? Yes, you're right, an update won't work in all situation, but it can probably replace a "insert" - "delete" sequence. – KIKO Software May 30 '22 at 11:55
  • I'm using ajax to load dynamic pages. If user first click on the link for the first time, it submit one data. – Barry Clinton May 30 '22 at 11:57
  • @KIKOSoftware But if user click on the link 5 times and click follow it submit 5 data's resulting to duplicate – Barry Clinton May 30 '22 at 11:58
  • OK, so the first time the user click you should do an INSERT but the next time it should be an UPDATE. That way nothing is duplicated. This is such a common occurrence that there's a special statement for it: [INSERT ... ON DUPLICATE KEY UPDATE Statement](https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html). But you don't have to use this, you can just simply check if the combination of `sender_id` and `user_id` already exists in the database, if it does you UPDATE, otherwise you INSERT. – KIKO Software May 30 '22 at 12:03
  • @KIKOSoftware Update won't work if the unfollow. If the user and try to follow again. Because when user unfollow the record is deleted – Barry Clinton May 30 '22 at 12:06
  • All I need is a delete query to leave only the latest record – Barry Clinton May 30 '22 at 12:07
  • For an unfollow you can, obviously, do something else. Anyway, this is clearly an [xy-problem](https://xyproblem.info/). You have a problem, think you know a solution, but you get stuck. So you ask for help with that solution. But the solution you think you need is wrong, and complex. Endless discussions ensue. I can only help you so far. – KIKO Software May 30 '22 at 12:08
  • 2
    And we haven't even addressed the elephant in the room: [SQL-injection](https://www.php.net/manual/en/security.database.sql-injection.php). Comments usually start with this, because it is a serious problem in your code. – KIKO Software May 30 '22 at 12:10
  • @KIKOSoftware I just update my question. I added a screenshot to know what I'm talking about – Barry Clinton May 30 '22 at 12:20
  • How do you know which is the "latest" record? Is there a timestamp being stored as a default value, or are you using an incrementing ID column somewhere? Keep in mind MySQL doesn't presume anything about order - just because you INSERT some rows in a particular order doesn't mean that they'll come out in that same order when you SELECT them. – droopsnoot May 30 '22 at 12:26
  • If you're getting duplicates where there should not be duplicates, that suggests that the database tables are not set up correctly. – droopsnoot May 30 '22 at 12:27
  • On the subject of the database layout, what's the need for storing the user id and the user name (and photo)? Aren't they linked anyway in the user table, and could therefore be retrieved with a JOIN when you need them? – droopsnoot May 30 '22 at 12:28
  • @KIKOSoftware I have tried base on the link provided but it no longer inserting to database. INSERT INTO follow (sender_id, receiver_id, name, photo1) VALUES ('".$_POST["sender_id"]."', '".$_SESSION["user_id"]."', '".$phot["user_RealName"]."', '".$phot["photo"]." limit 2') ON DUPLICATE KEY UPDATE follow set sender_id='{$_POST['sender_id']}' AND receiver_id='{$_SESSION['user_id']}' where sender_id = '{$_POST['sender_id']}' AND receiver_id = '{$_SESSION['user_id']}' "; – Barry Clinton May 30 '22 at 12:48
  • This statements checks your indexes, specifically: _"a UNIQUE index or PRIMARY KEY"_. I don't know what indexes you have defined. You would probably need a UNIQUE index on the combination of `sender_id` and `user_id` for this to work. – KIKO Software May 30 '22 at 13:05
  • @KIKOSoftware I finally found a solution that works for me here. $gk=mysqli_query($db,"DELETE p1 FROM follow p1,follow p2 WHERE p1.sender_id = p2.sender_id AND p1.follow_id > p2.follow_id "); – Barry Clinton May 30 '22 at 13:07
  • Ok, if it works for you, go with it. Just remember that you still need to solve the SQL-injection problem. I know this doesn't seem important now, but every time you show your code to someone else they'll comment on that. Better do it right from the start. – KIKO Software May 30 '22 at 13:09

0 Answers0