[SOLVED] select by date

[zhangy]

Hi,

I am wondering why the following code doesnt work to pull the database data of the previous month. I am trying to expose data only listed within the previous month based of the current date.

Thanks!

 

<?php
mysql_query("SELECT * FROM $table WHERE s_date BETWEEN CURDATE( ) and DATE_SUB( CURDATE( ) ,INTERVAL 15 DAY ) ORDER BY submission_id DESC");
?>

[waynew]

<?php
mysql_query("SELECT * FROM $table WHERE s_date BETWEEN CURDATE( ) and DATE_SUB( CURDATE( ) ,INTERVAL 15 DAY ) ORDER BY submission_id DESC") or die(mysql_error());
?>

 

Then report back.

[PFMaBiSmAd]

To start with, the BETWEEN syntax expects the first value to be the minimum and the second value to be the maximum -

expr BETWEEN min AND max

 

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max)

 

What is your definition of previous month, because even if you get the min and max in the correct order, the values you are using would give you records from 15 days ago up through the current date.

[zhangy]

Hi,

Waynewax, I tryed with (mysql_error()); and there was no change, and no error was displayed.

 

PFMaBiSmAd, I have no idea what that means... but if im understanding right its should be something like the following... however it still doesnt work.  ???

 

<?php
mysql_query("SELECT * FROM $table WHERE s_date BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 30 DAY ) and CURDATE( ) ORDER BY submission_id DESC");
?>

[PFMaBiSmAd]

however it still doesnt work

Telling us it does not work is pointless. We know it does not work or you would not be posting in a help forum. You have got to tell us what it is doing that is wrong. Tell us what you see in front of you 

[zhangy]

I see nothing where something should be 

 

The rest of the page's content loads fine but the text thats supposed to be generated from the query doesnt show up. nothing there. empty, void...

[PFMaBiSmAd]

Are you sure the code where your query is at is being executed? Post all your code. Are there any matching rows in the database and what data type is s_date?  Post a row from your table that has an s_date within the last 15 days that should match the query.

[zhangy]

Hope this helps:

 

data type: int(11)

 

s_date: 1228899148

 

<?php

require_once('Load.php');

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't select database")

if (!mysql_select_db($database))
    die("Can't select database");

$result = mysql_query("SELECT * FROM $table WHERE s_date BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 30 DAY ) and CURDATE( ) ORDER BY submission_id DESC") or die(mysql_error());

while($row = mysql_fetch_array($result))
{
  	echo "<div id=\"recentJobs\">";
echo "<strong>";
  	echo '<a href="jobsDisplay.php?id='.$row['submission_id'].'" class="white">'.$row['col_4'].'</a>';
  	echo "</strong>";
  	echo $row['col_2'];
  	echo "  |  ";
  	echo date("l M dS, Y", $row['s_date']);
echo "</div>";
}
mysql_free_result($result);
?>

 

[PFMaBiSmAd]

The CURDATE() and DATE_SUB() functions you are attempting to use produce or operate on DATE and DATETIME data types - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

 

A Unix Timestamp (what you are using) must be converted to be used for most purposes and that conversion is both slow and subject to errors if your time zone information is not kept current (for things like DST stop/start dates.)

 

The following will work, but it would be much better and faster if you used a DATETIME data type for s_date -

 

$result = mysql_query("SELECT * FROM $table WHERE FROM_UNIXTIME(s_date) BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 30 DAY ) and CURDATE( ) ORDER BY submission_id DESC") or die(mysql_error());

 

You could also use the UNIX_TIMESTAMP() function to convert DATE_SUB( CURDATE( ) ,INTERVAL 30 DAY ) to a Unix timestamp and for the max value in the BETWEEN min AND max syntax -

 

$result = mysql_query("SELECT * FROM $table WHERE s_date BETWEEN UNIX_TIMESTAMP(DATE_SUB( CURDATE( ) ,INTERVAL 30 DAY )) and UNIX_TIMESTAMP( ) ORDER BY submission_id DESC") or die(mysql_error());

[zhangy]

Ok it looks like its working now.

The text from the past month is being displayed.

 

Will this continuely update itself?

Tomorrows collected data will be added,

and the data from a month ago from yesterday will removed?