[SOLVED] time regulated display

[Lambneck]

Hello,

I would like to use mysql's date math to ignore old data during page loads.

I read an older post about this here that said:

 

...add a "WHERE datecolumn > NOW() - INTERVAL 1 MONTH" to your queries, and put an index on datecolumn.

 

however i already have a WHERE in my query and was wondering there can be multiple WHEREs or if there is another way to do this?

 

here is the code as it is now:

<?php
$result = mysql_query("SELECT * FROM $table WHERE col_1='plastic' ORDER BY submission_id DESC");
?>

[mapleleaf]

Assuming that you can put the extra condition:

<?php
$twentyfour = (time() - 86400);
$lastweek = (time() - 604800);
$lasttwo = (time() - 1209600);
$lastmonth = (time() - 2592000);
$lastyear = (time() - 31104000);


$result = mysql_query("SELECT * FROM $table WHERE col_1='plastic' AND date < $lastmonth ORDER BY submission_id DESC");
?>

 

Or something along those lines

[Lambneck]

i get an error:

Query to show fields from table failed:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date < 1222839424 ORDER BY submission_id DESC' at line 1

 

I want only the submissions from the previous 30days to be displayed.

I guess something like:

$result = mysql_query("SELECT * FROM $table WHERE submission_date > now() - {30days} ORDER BY submission_id DESC");

 

Also the submission date is formatted upon submission

$submission_date = date("l M dS, Y, H:i:s");

Not sure if is having a negative effect on what I am trying to do.

 

 

[mapleleaf]

It depends on how you have saved the date in your table. Also what is your date column called?

Assuming the date column is saved in this format 1225432548 which is now this may work.

 

"SELECT * FROM $table WHERE col_1='plastic' AND date < NOW() - INTERVAL 7 HOUR ORDER BY submission_id DESC";

Hope this is of use

[Barand]

you need to store DB dates in ISO format (Y-m-d H:i:s).

 

Format as required on output.

[Lambneck]

Ok I've fixed how the date is stored.

 

I think something is still wrong with the query:

<?php $result = mysql_query("SELECT * FROM $table WHERE submission_date > NOW() - INTERVAL 1 MONTH ORDER BY submission_id DESC"); ?>

Nothing is displayed from database with the above code.

Can anyone see what I'm doing wrong?

Thanks.

[Lambneck]

Fixed:

 

select * from dt_tb where `dt` >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)