By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,028 Members | 1,784 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,028 IT Pros & Developers. It's quick & easy.

delete last row from specific user

100+
P: 155
i got for example results like that

user1
user1
user2
user1
user1
user3
user1

i want to limit user1 to have maximum 4 resilts ,if user1 have 5 results it will be deleted ,this is example of code that im trying to build:
Expand|Select|Wrap|Line Numbers
  1. <?
  2. $query = mysql_query("SELECT * FROM `my_table` where username='$username' ") or die(mysql_error());
  3. $last = '';
  4. $count = 0;
  5. while($line = mysql_fetch_assoc($query)) {
  6.   if($last == $line['username']) $count++; 
  7.   else {
  8.     $last = $line['username'];
  9.     $count = 1;
  10.   }
  11.   if($count > 4) {
  12. $sql_delete="DELETE FROM my_table WHERE username = '$username'";
  13. mysql_query($sql_delete)or die(mysql_error());
  14.   }
  15. ?>
what i need is to delete the oldest record from the specific user ,but it is not doing that!! ,please help!

thanx in advanced.
May 12 '09 #1
Share this Question
Share on Google+
7 Replies


Ciary
Expert 100+
P: 247
i can guess what does happen: all records from user1 are deleted when he has more then 4 records.

the problem is very simple. if you use a delete query it will delete every record that matches with the given patern.
Expand|Select|Wrap|Line Numbers
  1. WHERE username = '$username'"
what you need to do is this. in your while-loop you save the ID or the primary key of your record in a variable.

then you use the same query but this time you use
Expand|Select|Wrap|Line Numbers
  1. WHERE PK_ID= '$lastID'"
May 12 '09 #2

Dormilich
Expert Mod 5K+
P: 8,639
there's probably a way to do that using SQL too (using subqueries and such), but you should ask the SQL guys, because that's a more advanced matter.
May 12 '09 #3

100+
P: 155
Expand|Select|Wrap|Line Numbers
  1. <?
  2. $query = mysql_query("SELECT * FROM `my_table` where username='$username' order by id asc") or die(mysql_error());
  3. $last = '';
  4. $count = 0;
  5. while($line = mysql_fetch_assoc($query)) {
  6.   if($last == $line['username']) $count++; 
  7.   else {
  8.     $last = $line['username'];
  9.     $count = 1;
  10.   }
  11.   if($count > 4) {
  12. $sql_delete="DELETE FROM my_table WHERE username = '$username' order by id asc limit 1";
  13. mysql_query($sql_delete)or die(mysql_error());
  14.   }
  15. ?>
i got the last id from user1 ,and after i delete the oldest id from user1 with limit 1

is it going to work like that? i didnt try it!

thanx
May 12 '09 #4

Ciary
Expert 100+
P: 247
i'm not sure. never used delete with limit. i think you just need to try if it does what you need. if it does, pls report back at the forum. i think this might also be helpful to many others :)
May 12 '09 #5

100+
P: 155
working perfect!! :)
May 12 '09 #6

100+
P: 155
thanx all for your help!
May 12 '09 #7

Ciary
Expert 100+
P: 247
np, i'm glad to help :)
May 12 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.