[SOLVED] Date range in MYSQL query

[tejama]

Hi everyone,

 

I'm trying to extract data in a certain range of date (say 1 month's worth), but my results aren't what I'm expecting.  Can you guys have a look at my code and suggest what may not be coded properly?

 

function display_archive($month) {
  $tempdate = $month. " 1, 2007";
  $startdate = date("F d, Y", strtotime($tempdate));

  $tempdate2 = $month. " 30, 2007";
  $enddate = date("F d, Y", strtotime($tempdate2));

  db_connect();
  $result = mysql_query("select * from blog where entered BETWEEN '$date' and '$enddate'");
  $num_rows = mysql_num_rows($result);
}

[AV1611]

I might be off on this, but I wrote a similar script a while back and here are some snippets from it.  Keep in mind this was talking to ODBC (VFP) and not MySQL so syntax might be wrong but the method will work:

$today="{d '".date('Y-m-d')."'}";
$ninetyDay="{d '".date('Y-m-d', strtotime('-120 days'))."'}";


$connect = odbc_connect("xxxx", "xxxx", "xxxx");
$query = "SELECT itdatshta.`it_date`, itdatshta.`insptype`, itdatshta.`process`, itdatshta.`product`, itdatshta.`descr`,
itdatshta.`rev_level`, itdatshta.`serialno`, itdatshta.`failed`
FROM `itdatshta` itdatshta WHERE
    itdatshta.`it_date` >= $ninetyDay AND
    itdatshta.`it_date` <= $today
    AND
    process = 'Functional Test'";

[skali]

function display_archive($month) {
  $tempdate = $month. " 1, 2007";
  $startdate = date("Y-m-d", strtotime($tempdate));

  $tempdate2 = $month. " 30, 2007";
  $enddate = date("Y-m-d", strtotime($tempdate2));

  db_connect();
  $result = mysql_query("select * from blog where entered BETWEEN '$date' and '$enddate'");
  $num_rows = mysql_num_rows($result);
}

[tejama]

I actually figured it out, here is the code I used:

 

function archive($month) {
  $tempdate = $month. " 1, 2007";
  $startdate = date("Y-m-d H:i:s", strtotime($tempdate));


  $tempdate2 = $month. " 11, 2007";
  $enddate = date("Y-m-d H:i:s", strtotime($tempdate2));

  db_connect();
  $result = mysql_query("select * from blog where entered >= '$startdate' and entered <= '$enddate'");
  $num_rows = mysql_num_rows($result);
}

 

Thanks for the help though!

[tejama]

Thanks Skali...got it just before I read your post!

[suttercain]

Is

>= '$startdate' and entered <=

what fixed it?

[tejama]

Nope, what fixed it was changing the date declaration from:

 

  $startdate = date("F d, Y", strtotime($tempdate));

 

to:

 

  $startdate = date("Y-m-d H:i:s", strtotime($tempdate));

 

for both $startdate and $enddate.

 

[suttercain]

Ahh... Thanks for the heads up. I am learning so I always look at the TOPIC SOLVED threads to run through it.