CODEWITHSUNDEEP
phpsqlmysql
user559142
user559142

Reputation: 12527

Find Earliest Date - PHP

I use PHP to perform SQL to pull out some data from my database between a date range. The dates are stored as date in the relation:

   $from = "2011-08-11";
   $to = "2011 - 08- 25";
   $query = mysql_query("SELECT date FROM `entries` WHERE date BETWEEN '$from' AND '$to' ORDER BY date ASC");

I would like to find the earliest date pulled from the relation.

If the query is successful, I store the 'date' attribute in a php array called $dates. I thought I could iterate over this $dates and compare the $date values after converting them into dates. 

 if($query){
    $dates = array();
    while($row = mysql_fetch_array($query)){      
      $dates[] = $row['date'];  
    }    

    $min = strftime("%Y-%m-%d", strtotime($dates[0]));
    for($i = 1; $i < count($dates); $i++){

      if($dates[i] < $min){
    $min = $dates[i];
      }
    }

This does not work however...It prints random values....Perhaps there is a much simpler way to do this and I am overcomplicating matters...

HEEEELLLP!!

Upvotes: 1

Views: 2662

Answers (5)

dkuntz2
dkuntz2

Reputation: 156

Convert the dates to numbers and sort the array?

Take what you have (lines 1-5), 

foreach ($dates as $date) {
    // convert each date from "YYYY-MM-DD" to "YYYYMMMDD" and turn it into an int
    $date = intval(str_replace("-", "", $date));
}
// sort the array from lowest to highest
asort($dates);
// the minimum is the first item in the array
$minint = $date[0];
// convert back into "YYYY-MM-DD" format
$min = substr($minint, 0, 4) . "-" . substr($minint, 4, 6) . "-" . substr($minint, 6);

Upvotes: 0

Headshota
Headshota

Reputation: 21449

If you order your query, then it will be the first (or the last) row in you're query. So you wouldn't need to find it.

Upvotes: 3

Mark Bouchard
Mark Bouchard

Reputation: 861

If all you want to do is find just the earliest date, and you don't care about the rest, you could use the aggregate function min() in your query like so:

SELECT MIN(date) AS earliest FROM `entries` WHERE date BETWEEN '$from' AND '$to'

Then just grab the earliest column from the result set in your php code.

Upvotes: 1

styfle
styfle

Reputation: 24720

The simplest way is to use the first date since you know it is already the earliest due to ASC in your SQL statement. After you read the rows into your array, just use the first element.

while($row = mysql_fetch_array($query)){      
    $dates[] = $row['date'];  
} 

$earliest_date = $dates[0];

Upvotes: 1

Andrej
Andrej

Reputation: 7504

Instead of 

$min = strftime("%Y-%m-%d", strtotime($dates[0]));

you should use 

$min = date("%Y-%m-%d", strtotime($dates[0]));

Upvotes: 1

Related Questions