Extract Part of a Record from a Query Result

rsmith

Hey all. I'm writing a small program that needs to be able to extract the month from a field called "entered" that stores the date and time entered in the following format:

December 21st 2006 10:22:04 AM

What I need to know is how to extract the month, regardless of which month is selected from a list. I could pass the month to the script as a variable then switch($month) and perform a query using the SUBSTRING extraction method depending on the length of characters that comprise the full spelling of the month. Would this be the best way to go about it and just switch out the queries depending on the month variable?

Thanks in Advance!
Ryan

devinemke

you are storing your dates as strings. don't. store your dates as dates and use your RDBMS's native date/time functions to extract parts of the date. if you are using mySQL look at Date and Time Types and Date and Time Functions.

rsmith

Thanks for the response. I've switched all my date fields to either date or datetime depending on the information i want to store and have updated my scripts to store the information properly. I'm still a little confused no how to extract the month and convert it to it's textual representation. Any guidance is greatly appreciated. I read through the links above but all I get is a blank output.

Thanks again!

devinemke

again, i suggest that you not use PHP at all for this. use the built in Date and Time functions of mySQL.

rsmith

I'm trying to. My query is returning a blank translation when I echo it out in PHP. I'm using the

$query = "SELECT sid, make, model, issue, DATE_FORMAT('entered', '%W %M %D %Y') FROM service WHERE cust_id = ".$cid."'";
$result = mysql_query($query);
$svc_info = mysql_fetch_assoc($result);

I get a blank result when I try to echo $svc_info['entered']. More importantly I need to extract the month and translate it to the English equivalent.

devinemke

if your "entered" field is still some sort of string field (VARCHAR) then the DATE_FORMAT() will not work. you need to parse through your table and convert all of the "entered" strings into dates.

backup your database. create a new DATETIME field into your table called "entered_datetime" and run the following code. (i'm assuming here that "sid" is your primary key field)

$result = mysql_query('SELECT sid, entered FROM service') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
	$date_time = date('Y-m-d H:i:s', strtotime($row['entered']));
	mysql_query("UPDATE service SET entered_datetime = '$date_time' WHERE sid = '" . $row['sid'] . "'") or exit(mysql_error());
	echo $row['sid'] . ' successfully updated<br>';
}

then you can delete the "entered" field and rename the "entered_datetime" to "entered".

Roger_Ramjet

You can actually do the same thing completely in sql, so you can do it all in 1 query using str_to_date

$sql = "UPDATE service SET iso_entered = STR_TO_DATE(entered, '%M %D %Y %r')";

STR_TO_DATE reverses the process of DATE_FORMAT and uses the same format specifiers.

rsmith

devinemke wrote:
if your "entered" field is still some sort of string field (VARCHAR) then the DATE_FORMAT() will not work. you need to parse through your table and convert all of the "entered" strings into dates.

backup your database. create a new DATETIME field into your table called "entered_datetime" and run the following code. (i'm assuming here that "sid" is your primary key field)
$result = mysql_query('SELECT sid, entered FROM service') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
	$date_time = date('Y-m-d H:i:s', strtotime($row['entered']));
	mysql_query("UPDATE service SET entered_datetime = '$date_time' WHERE sid = '" . $row['sid'] . "'") or exit(mysql_error());
	echo $row['sid'] . ' successfully updated<br>';
}
then you can delete the "entered" field and rename the "entered_datetime" to "entered".

I have already renamed my table fields and cleared the data then re-added the data using the proper date() PHP function and DATE or DATETIME field types. My new query looks like this:

$squery = "SELECT sid, make, model, DATE_FORMAT('sentered', '%W %M %D %Y'), active, FROM service WHERE cust_id = '".$cid."' ORDER BY active DESC";
		$sresult = mysql_query($squery);
$svc_info = mysql_fetch_assoc($sresult);

But when I try to run the script I get an error stating that the query produces an invalid MySQL Resource.