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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

danwguy
Forum Contributor
Posts: 256
Joined: Wed Nov 17, 2010 1:09 pm
Location: San Diego, CA

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

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

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

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
danwguy
Forum Contributor
Posts: 256
Joined: Wed Nov 17, 2010 1:09 pm
Location: San Diego, CA

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

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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'.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
danwguy
Forum Contributor
Posts: 256
Joined: Wed Nov 17, 2010 1:09 pm
Location: San Diego, CA

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

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

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

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

I like option a. Not sure why I didn't think of that.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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);
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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);
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

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

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

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

Post 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
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply