SQL date format in php

wiccanwoman1103

I have a search page with two javascript calendars. However when i enter in dates they are not searching correctly and outputting the right query information. In my database the date displays Y/m/d the calendar displays it like 10/11/2005. So it is not searching my dbase properly. How to a reformat the date on my sql query so it will sear right. here is my code

<?php
	$num = trim($_POST["num"]);
	$user = addslashes(trim ($_POST["user"]));
	$start_date = trim ($_POST["start_date"]);
	$end_date = trim ($_POST["end_date"]);
	$campus = trim ($_POST["campus"]);
	$building_alfred = addslashes(trim ($_POST["building_alfred"]));
	$building_wellsville = addslashes(trim ($_POST["building_wellsville"]));
	$room = addslashes(trim ($_POST["room"]));
	$problem = trim ($_POST["problem"]);
	$summary = addslashes(trim ($_POST["summary"]));
	$description = addslashes(trim ($_POST["description"]));
	$status = trim($_POST["status"]);
?>

<?php 
$sql = "";

if(!empty($num)){
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
	} 
	$sql = $sql." id LIKE '%$num%' ";
}

if(!empty($user)){
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 
		$sql = $sql." Requestor_Name LIKE '%$user%' ";
}	

if(!empty($campus)){
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 
		$sql = $sql." Campus LIKE '%$campus%' ";
}	

if(!empty($building_alfred)){
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 
		$sql = $sql." Building LIKE '%$building_alfred%' ";
}	

if(!empty($building_wellsville)){
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 
		$sql = $sql." Building LIKE '%$building_wellsville%' ";
}			

 if(!empty($room)){
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 
		$sql = $sql." Room LIKE '%$room%' ";
}	

 if(!empty($problem)){
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 	
		$sql = $sql." Problem LIKE '%$problem%' ";
}

if(!empty($summary)){
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 	
		$sql = $sql." Summary LIKE '%$summary%' ";
}

if(!empty($description)){
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 	
		$sql = $sql." Description LIKE '%$description%' ";
}

if(!empty($status)){
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 
		$sql = $sql." Status LIKE '%$status%' ";
}

if(!empty($start_date)){
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 	
		$sql = $sql." Date_Submitted  >= '$start_date' ";
}echo("start date: ".$start_date."<br>");

if(!empty($end_date)){
	if (empty($sql)){
		$sql = $sql."WHERE";
	}else{
		$sql = $sql."AND";
		} 
		$sql = $sql." Date_Submitted  <= '$end_date' ";
}echo("end date: ".$end_date."<br>");		

$sql = "SELECT * FROM workorder ".$sql.";";

?>
<?php
$link = mysql_connect("localhost", "uname", "pass");
mysql_select_db("helpdesk", $link);

$results = mysql_query($sql, $link);

$num_rows = mysql_num_rows($results);

echo "$num_rows Records Found\n";

?> 

<div id="content">
	<table align="center" class="styled_results">
    <tr>
		<th class="clean">Edit</th>
		<th>W.O.#</th>
		<th>Requestor</th>
		<th>Date Submitted</th>
		<th>Location</th>
		<th>Problem Type</th>
		<th>Summary</th>
		<th>Status</th>
	</tr>
<?php
$myRow = 0;  

while ($row = mysql_fetch_assoc($results)) {
   $myClass = ($myRow % 2) ? "" : " class='odd'";   

   echo '<tr align="center">';
   echo '<td'.$myClass.'> <img src="../common/images/edit.gif"> </td>'; 
   echo '<td'.$myClass.'>'.$row["id"].'</td>';
   echo '<td'.$myClass.'>'.$row["Requestor_Name"].'</td>';
   echo '<td'.$myClass.'>'.$row["Date_Submitted"].'</td>';
   echo '<td'.$myClass.'>'.$row["Campus"].'<br>'.$row["Building"].'<br>'.$row["Room"];
   echo '<td'.$myClass.'>'.$row["Problem"].'</td>';
   echo '<td'.$myClass.'>'.$row["Summary"].'</td>';
   echo '<td'.$myClass.' class = "wrap">'.$row["Status"].'</td>';
   echo '</tr>';

   $myRow++; 
   }
mysql_free_result($results);
?>
	</table>
	<br>
<div align="center"><INPUT TYPE="button" onClick="parent.location='admin_search.php'" value="Start a New Search"></div>
</div>

Please help me out!!!!! :eek:

toplay

If your column in your table is of a date or data/time data type, then you can retrieve/search the date directly from MySQL in the format you want. Always, store dates and times in some form of MySQL Date and time Types.

What data type did you use for your date column?

See:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

🙂

wiccanwoman1103

Date time was the datatype that I used and that link you sent me did not help. Please look at the way the if blocks with the SQL is set up.

Weedpacket

I'm surprised that there's nothing in MySQL's documentation on date and time functions that wouldn't help with date and time functions.

What is the format of $start_date and $end_date? Since you do nothing to them before putting them in the query, they'll need to be in the same format that MySQL uses.

EWaveDev

I also fought with this when my site was using MySQL. I wrote two functions to convert the date either from MySQL to what my users want to see and the reverse. You may need to do the same. My users preference is month day year; if you/your users prefer something different these functions can be modified.

<?php
/*********************************************************************
File: DateFmtDspl2MyS.php
Purpose: This function converts an incoming dd Mon YYYY formated date
into the YYYY-MM-DD formated date for MySQL
History:
Mary Self, 6 July 2005, initial creation
I/O:
input parameter:
$Date: a display formatted date (dd Mon YYYY)
Calls:
none
Returns:
default MySQL formatted date (YYYY-MM-DD)
Tables Used/Modified:
none
Tables Referenced:
none
Comments:

********************************************************************/

function DateFmtDspl2MyS($Date)
{ // convert a date formatted for display into a MySQL date
$Mon = array (Jan => '01',
Feb => '02',
Mar => '03',
Apr => '04',
May => '05',
Jun => '06',
Jul => '07',
Aug => '08',
Sep => '09',
Oct => '10',
Nov => '11',
Dec => '12');

$Yr = substr($Date,-4, 4);		// extract the year
$Mn = substr($Date, 3, 3);		// extract the month
$Dy = substr($Date, 0, 2);		// extract the day
$NewDate = $Yr.'-'.$Mon[$Mn].'-'.$Dy;
return $NewDate;

}
?>

<?php
/*********************************************************************
File: DateFmtMys2Dspl.php
Purpose: This function converts a MySQL data format to a
Day Month Year format for display.
History:
Mary Self, 6 July 2005, initial creation
I/O:
none
Calls:
?
Returns:
dd Mon YYYY formated date
Tables Used/Modified:
none
Tables Referenced:
none
Comments:

*********************************************************************/

function DateFmtMyS2Dspl($Date)
{ // convert a MySQL date into a date formatted for display
$Mon = array ("","Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");
$Yr = substr($Date, 0, 4); // extract the year
$Mn = substr($Date, 5, 2); // extract the month
$Dy = substr($Date, -2,2); // extract the day
$NewDate = $Dy.' '.$Mon[ltrim($Mn,'0')].' '.$Yr;
return $NewDate;
}
?>

I put each in a separate files that can be easily included and the function used.