Trouble with between dates

[duke1980]

Hey Y'all,

Having a little trouble with trying to select records of new members who have joined in the last 30 days. My date field in the table is datejoined and the type is date with a format of 0000-00-00.

What I'm eventually trying to do is post on the home page the names of members who have joined in the last 30 days or better yet in the current month.

<?php
//set paramenets
$dbhost = 'xxxxxx';
$dbuser = 'xxxxx';
$dbpass = 'xxxxx';
$dbname = 'bahs80_dante';
$dbtabl = 'users';

// Connect to the database server
$dbcon = mysql_connect($dbhost, $dbuser, $dbpass) OR die('could not connect to MySQL: ' . mysql_error());

// Select the  database
mysql_select_db ($dbname) OR die('could not connect to database: ' . mysql_error());

//query the database
$sql = 'SELECT * '
        . ' FROM `users` '
        . ' WHERE `joindate` > 0000-00-00 LIMIT 0, 60';
$result = mysql_query($sql) or die('Failed to execute ' . $sql . ' due to ' . mysql_error());

// generate and display result
while ($row = mysql_fetch_array($result))
{
  echo '<table border="1">';
  echo '<tr>';
  echo '<td>';
  echo $row['last'] . ', ';
  echo $row['first'] . '<br>';
  echo $row['address'] . '<br>';
    echo $row['city'] . ', ';
  echo $row['state'] . '  ';
  echo $row['zipcode'] . '<br>';
  echo $row['joindate'] . '<br>';
  echo '</td>';
  echo '</tr>';
  echo '</table>';
}
?>

Any help with this would be greatly appreciated and thanks in advanced

Ed

[alfoxy]

If your format is 0000-00-00 is this like 1st Feb 2004 would be 2004-02-01 ?

If so then I'd use a like operator in your select statement

e.g. something like

$sql = "SELECT * FROM usersWHERE joindate LIKE '2004-02-%' " ;

Hope this helps

Al

[phil_j_poore]

i would use the following snippet to find out the date of the 1st of the month....

//gets current date in YYYY-MM-DD format eg. 2005-01-17
$current_date = date("Y-m-d");
//gets date in YYYY-MM-DD for first of the month eg. 2005-01-01
$first_of_month = substr($current_date,0,8)."01";

this gives you the date of the first of the month then just add this to your query, and you get the following.

<?php
//set paramenets
$dbhost = 'xxxxxx';
$dbuser = 'xxxxx';
$dbpass = 'xxxxx';
$dbname = 'bahs80_dante';
$dbtabl = 'users';

$dbcon = mysql_connect($dbhost, $dbuser, $dbpass) OR die('could not connect to MySQL: ' . mysql_error());
mysql_select_db ($dbname) OR die('could not connect to database: ' . mysql_error());

//gets current date in YYYY-MM-DD format eg. 2005-01-17
$current_date = date("Y-m-d");
//gets date in YYYY-MM-DD for first of the month eg. 2005-01-01
$first_of_month = substr($current_date,0,8)."01";

//add limit if you dont want more than a cirtain amount of results
//e.g. add LIMIT 30 to get newest 30
$sql = "SELECT * FROM users WHERE joindate > {$first_of_month} ORDER BY joindate DESC";
$result = mysql_query($sql) or die('Failed to execute ' . $sql . ' due to ' . mysql_error());

// generate and display result
while ($row = mysql_fetch_array($result))
{
  echo '<table border="1">';
  echo '<tr>';
  echo '<td>';
  echo $row['last'] . ', ';
  echo $row['first'] . '<br>';
  echo $row['address'] . '<br>';
    echo $row['city'] . ', ';
  echo $row['state'] . '  ';
  echo $row['zipcode'] . '<br>';
  echo $row['joindate'] . '<br>';
  echo '</td>';
  echo '</tr>';
  echo '</table>';
}
?>

i have also changed $sql to take out the quotes around the field names that arnt needed there.
if this doesnt work post again with any problems.

[devinemke]

to select all records from the last 30 days:

SELECT * FROM users WHERE joindate >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)

to select all records from current month/year:

SELECT * FROM users WHERE MONTH(joindate) = MONTH(CURDATE()) AND YEAR(joindate) = YEAR(CURDATE())

[duke1980]

Thanks to all for the help, they all seemed to do the trick. As always this place rocks.
ED