Querying the database by Date

GeneBuchite · August 31st, 2011, 08:22 AM

Does Any know how to Query the Database using a date ie. something like:

Code:

   $q = "Select * From " . $TableName . " Where DateEntered = '" . $MyDate . "' Order By DateEntered";

My Guess would be that the $myDate needs to be formatted... but how?

GeneBuchite · August 31st, 2011, 08:59 AM

I guess this does work as written as long as $myDate is in the format "yyyy-m-d" ... See Page 298 "PHP6 and MySQL5" For formatting Dates

Code:

$dte = "9/9/2011";
$myDate = strtotime($dte);
$gDate = getDate($myDate);
$formattedDate = $gDate['year'] . "-" . $gDate['mon'] . "-" . $gDate['mday'] ;
  $Table = "YourTableName";
 $query = "SELECT * FROM " .  $Table . " Where KillDate = '" . $formattedDate . "'";

UseLess · September 2nd, 2011, 02:59 AM

Greetings,

When working with PHP and MySQL I tend to use a Unix timestamp instead of the date, that way you're just comparing numbers and once you get the number back from the db you can then format it for display however you like using the date() function.

And it makes life much easier as your not dealing with various date formats.

GeneBuchite · September 3rd, 2011, 04:38 AM

you mean like so ?

Code:

$dte = "9/9/2011";
$myDate = strtotime($dte);
  $Table = "YourTableName";
 $query = "SELECT * FROM " .  $Table . " Where KillDate = '" . $myDate. "'";
    $result = mysql_query($query);

while($find = mysql_fetch_array($result)) 
 {
 list($returnlDate,$maxB, $filledB, $maxP, $filledP, $Counter) = $find;

$gDate = getDate($returnDate);
$formattedDate = $gDate['year'] . "-" . $gDate['mon'] . "-" . $gDate['mday'] ;

echo $formatted Date

UseLess · September 4th, 2011, 06:15 AM

Greetings,

Close...

Code:

$dte = "9/9/2011";
$myDate = strtotime($dte);

$Table = "YourTableName";

// no need to wrape the killdate value in quotes as it's a number
// the killdate field in the db should be set to: INT(11)
$query = "SELECT * FROM " .  $Table . " Where KillDate = " . $myDate;
if( !$result = mysql_query($query) )
{
	echo 'There was a problem executing the SQL: ' . $query . '<br />';
	echo 'MySQL Error Msg: ' . mysql_error() . '<br />';
	echo 'MySQL Error No: ' . mysql_errno() . '<br />';
}
else
{
	while($row = mysql_fetch_array($result)) 
	{
		// the following assumes the key => value pairs returned from the db
		// will always be in the same order...
//		list($returnlDate,$maxB, $filledB, $maxP, $filledP, $Counter) = $find;
		
		// better to not assume the same order, just in case it changes then
		// your script will have a value it probably shouldn't have
		$return_date = $row['KillDate'];
		$maxb = $row['maxB'];
		$filledb = $row['filledB'];
		$maxp = $row['maxP'];
		$filledp = $row['filledP'];
		$counter = $row['Counter'];
		
		// use the returned date
		$date_ymd = date('Y-m-d', $return_date);
		$date_mdy = date('m-d-Y', $return_date);
		$date_dmy = date('d-m-Y', $return_date);
		
		echo 'Date [Y-m-d]: ' . $date_ymd . '<br />';
		echo 'Date [m-d-Y]: ' . $date_mdy . '<br />';
		echo 'Date [d-m-Y]: ' . $date_dmy . '<br />';
	}
}