How to: DELETE * FROM table WHERE Greater than 2 week

[suttercain]

Hi guys,

 

I am building a module that displays the headline from a page that had the most hits last week. I was able to figure this out in PHP. I would now like to know how to delete and records that are over 2 weeks old. Anyone know how to do this in the mysql query?

 

Thanks.

 

SC

[only one]

$2weeks = time() - 1209600;

 

mysql_query("DELETE FROM `table` WHERE `date set` < '$2weeks'");

[suttercain]

Thanks, I'll give that a go.

 

SC

[GingerRobot]

Variable names cannot start with a number.

[only one]

Variable names cannot start with a number.

 

Duh, I was only giving an example.

[suttercain]

What about an underscore?

 

[otuatail]

How is this date stored. Is it as time() Preferable.

 

Desmond.

 

[suttercain]

mysql timestamp 9999-12-31 23:59:59

[otuatail]

Ok pitty If it was time() you would be dealing with numbers. Sugest sometime, you create an extra field and populate it with this. as in

$stamp = strtotime(original-date-format)

new-field = $stamp. This is very flexable format.

1209600 = 2 weeks in seconds.

 

will have to put something together for you.

 

Desmond.

 

[pocobueno1388]

I'm not sure if this will work, but you can try it

 

DELETE FROM table WHERE `date` > (NOW() - INTERVAL 14 DAYS)

[otuatail]

OR

Delete from EventLog2 WHERE Date  < '2007-03-01';

[suttercain]

Anyone know how to grab the most occurrences in a column? Let's say A appears 10 times in the column and B occurs 25 times.... anyway to pluck out B?

 

Thanks

 

[otuatail]

SELECT * FROM Table GROUP BY field  order by ID

[atlanta]

Anyone know how to grab the most occurrences in a column? Let's say A appears 10 times in the column and B occurs 25 times.... anyway to pluck out B?

 

Thanks

 

 

You want to query the database for all occurences of A and B then compare them using php ex.

$query1 = "SELECT * FROM `table` WHERE `field` = A";

$query2 = "SELECT * FROM `table` WHERE `field` = B";

$num_rows1 = mysql_num_rows($query1);

$num_rows2 = mysql_num_rows($query2);

 

if ($num_rows1 > $num_rows2)

{

echo $num_rows1;

or

do something with $numrows1

}

else

{

echo $num_rows2;

or

do something with $numrows2

}

 

understand?

 

 

[suttercain]

No I just want the one that occurs the most.

[rajivgonsalves]

for the above you can try this out

 

select count(*), fieldname from table group by fieldname having count(*)=max(count(*));

[otuatail]

SELECT * FROM Table GROUP BY field_name  order by field_name

 

This will show all the totals starting with the highest

 

Desmond.