Page 2 of 2

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Thu Apr 21, 2011 5:02 pm
by danwguy
like pickle said, when you run your query limit the results to 5...

Code: Select all

$sql = mysql_query("SELECT * FROM table_name ORDER BY ID DESC LIMIT 5");
Will give you only the last 5 rows that have been entered.

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Thu Apr 21, 2011 5:05 pm
by simonmlewis
Yes I know how to do what pickle said - but this doesn't prevent a mountain of rows building up.
I want to know WHEN this person has reached 5, and if they add another, the oldest gets deleted.

How??

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Thu Apr 21, 2011 5:21 pm
by pickle
Two queries. One to pull the last 5 ids, one to delete anything from that user that doesn't have one of those ids.

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Thu Apr 21, 2011 5:27 pm
by danwguy
run a query to check how many rows there are, if the number of rows are greater than 5 delete the oldest one.

Code: Select all

$sql = mysql_query("SELECT * FROM table_name order by id desc limit 1");
while($row = mysql_fetch_assoc($sql)) {
    $id = $row['ID'];
}
$newnum = (int)($id - 5);
$sql2 = mysql_query("SELECT * FROM table_name");
$num = mysql_num_rows($sql2);
if($num > 5) {
   $sql2 = mysql_query("DELETE * FROM table_name WHERE ID < '$newnum'");
 
assuming that your ID field is set to auto increment.

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Thu Apr 21, 2011 5:35 pm
by simonmlewis
The only problem I think I think I can spot here, is:
$newnum = (int)($id - 5);

The numbers would be perfectly incremented. Not, 3, 4, 5, 6, 7..... so not sure how "- 5" will find the oldest 5 rows back.
Please see my comment a few stages back in this thread about the numbers. Or is that - 5, meant to mean the 5th row back relating to a row I entered?

I am sure I entered the original code here - it knows it's me because of a field named 'ipaddress'.

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Thu Apr 21, 2011 5:42 pm
by danwguy
the $newnum = (int)($num -5); code takes the number of the newest row (that we saved with $num = mysql_num_rows($sql); line and minuses 5 so that if your newest line is ID 237 for example, $newnum would be 232 so you want to delete anything that is less than 232 thereby giving you only 5 rows in your database, thats what you were asking for, find the number of rows, and delte everything that was -5 from that.

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Thu Apr 21, 2011 5:51 pm
by pickle
That's a bad idea. Rows can be deleted, so sequential rows might not have sequential ids. The only way you can do it is to either:

a) SELECT the latest 5 ids and delete everything from that user without those IDs or
b) SELECT the 5th last ID & delete everything from that user with an ID less than that.

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Fri Apr 22, 2011 2:05 am
by simonmlewis
I like option a. Not sure why I didn't think of that.

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Fri Apr 22, 2011 2:29 am
by simonmlewis
Ok Option a has it's own flaw.
In theory it's correct, but this fails for obvious reasons:

Code: Select all

$check = mysql_query ("SELECT * FROM recent WHERE ipaddress = '$ip' DESC LIMIT 10");
    while($rowr = mysql_fetch_object($check))
    {
    $check2 = mysql_query ("SELECT * FROM recent WHERE ipaddress = '$ip'  AND id != '$rowr->id'");
    while($rowr2 = mysql_fetch_object($check2))
      {
      mysql_query ("DELETE FROM recent WHERE ipaddress = '$ip' AND id == '$rowr2->id'");
      }	
      mysql_free_result($check2);
    }	
      mysql_free_result($check);
Line one selects only those 10. So $check2 isn't going to find anything at all!
I'm sure there is a way to assign each LIMIT 10 variable to it's own variable, to then do a new query, but I'm not quite sure how....but I think that's the answer.

Then you can query on the new $1, $2, $3.... variables you have found.

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Fri Apr 22, 2011 2:23 pm
by simonmlewis

Code: Select all

$check = mysql_query ("SELECT * FROM recent WHERE ipaddress = '$ip' DESC LIMIT 10");
    while ($rowr = mysql_fetch_object($check))
    {
    if ($id1 == NULL) { $id1 = $rowr->id;}
    if ($id1 != NULL && $id2 == NULL) { $id2 = $rowr->id;}
    if ($id1 != NULL && $id2 != NULL && $id3 == NULL) { $id3 = $rowr->id;}
    if ($id1 != NULL && $id2 != NULL && $id3 != NULL && $id4 == NULL) { $id4 = $rowr->id;}
    if ($id1 !=NULL && $id2 != NULL && $id3 != NULL && $id4 != NULL && $id5 == NULL) { $id5 = $rowr->id;}
    if ($id1 !=NULL && $id2 != NULL && $id3 != NULL && $id4 != NULL && $id5 != NULL && $id6 == NULL) { $id6 = $rowr->id;}
    if ($id1 !=NULL && $id2 != NULL && $id3 != NULL && $id4 != NULL && $id5 != NULL && $id6 != NULL && $id7 == NULL) { $id7 = $rowr->id;}
    if ($id1 !=NULL && $id2 != NULL && $id3 != NULL && $id4 != NULL && $id5 != NULL && $id6 != NULL && $id7 != NULL && $id8 == NULL) { $id8 = $rowr->id;}
    if ($id1 !=NULL && $id2 != NULL && $id3 != NULL && $id4 != NULL && $id5 != NULL && $id6 != NULL && $id7 != NULL && $id8 != NULL && $id9 == NULL) { $id9 = $rowr->id;}
    if ($id1 !=NULL && $id2 != NULL && $id3 != NULL && $id4 != NULL && $id5 != NULL && $id6 != NULL && $id7 != NULL && $id8 != NULL && $id9 != NULL && $id10 == NULL) { $id10 = $rowr->id;}
    }	
      mysql_free_result($check);
      
if ($id10 != NULL)
{        
      mysql_query ("DELETE FROM recent WHERE ipaddress = '$ip' AND (id != '$id1' AND id != '$id2' AND id != '$id3' AND id != '$id4' AND id != '$id5' AND id != '$id6' AND id != '$id7' AND id != '$id8' AND id != '$id9' AND id != '$id10')"); 
      }
This is too long winded, and errors on:

Code: Select all

while ($rowr = mysql_fetch_object($check))
and

Code: Select all

mysql_free_result($check);

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Tue Apr 26, 2011 10:06 am
by simonmlewis
Ok I have worked out why this is going wrong.
Going back to a previous comment here, it said to fine the 5th item - how do I do that?

I think I can do it by limiting how many, and doing it in order - then the EARLIEST ID number... you delete everything that is LESS than that number.

So if a LIMIT extracted IDs, 5, 6, 7, 8 and 9, then it would delete (if applicable to the IP) 4, 2 and 1.

Code: Select all

$check2 = mysql_query ("SELECT * FROM recent WHERE ipaddress = '$ip' ORDER BY ID DESC LIMIT 10");
    while ($rowr = mysql_fetch_object($check2))
    {
$check3 = mysql_query ("SELECT * FROM recent WHERE ipaddress = '$ip' AND id < '$rowr->id'");
    while ($rowr2 = mysql_fetch_object($check3))
    {
    echo "$rowr2->id<br/>";
    } mysql_free_result($check3);
    }	mysql_free_result($check2);
This is it so far, but it obviously producing the wrong results.
I think $check2 is correct. It will sort the results, then find the latest 10 results in order. The final result it should find should have a lower ID than the first.

So how do I spot the "final result"? The 10th entry it finds. So that I can do a new query where the ipaddress is the same, but the IDs are all LOWER than $tenthentry? Coz I think that's the answer to this question.

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Tue Apr 26, 2011 10:15 am
by simonmlewis
Oh my - think I have cracked it.

Code: Select all

$check2 = mysql_query ("SELECT * FROM recent WHERE ipaddress = '$ip' ORDER BY ID DESC LIMIT 10, 1");
    while ($rowr = mysql_fetch_object($check2))
    {
    mysql_query ("delete from recent where ipaddress = '$ip' AND id = '$rowr->id'");
    }	mysql_free_result($check2);

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Tue Apr 26, 2011 12:07 pm
by superdezign
You know... Instead of deleting rows, you could just ignore them.
And you could turn that long string of if-statements into a switch statement.

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Tue Apr 26, 2011 1:06 pm
by simonmlewis
No I have to delete them otherwise we will have 150,000+ rows in one weekend (like we did at Easter weekend).
We just want to stop their last 10 entered.

Re: Date and Time (2011-04-19 00:00:00).... how do I add tim

Posted: Wed Apr 27, 2011 12:54 pm
by pickle
re: option a)

Use the IN/NOT IN clause:

Code: Select all

DELETE
FROM
  `recent`
WHERE
  `ipaddress` = '$ip' AND
  `id` NOT IN ('id1','id2','id3','id4')
http://dev.mysql.com/doc/refman/5.0/en/ ... unction_in
http://www.w3schools.com/sql/sql_in.asp