MySQL DATE & TIME conversion

[m8fyu]

I have a slight problem and can't work out how to resolve the issue. I've read up on PHP functions and MySQL functions that deal with time and date but I still can't figure out how to deal with this. I need to display a couple of field from my table (date and time) as strings

extracts from my PHP code are:

$sql="SELECT * FROM zt_schedules WHERE fk_service_id='$servid' ORDER BY dep_date";

&

$str .= "<tr>";
$str .= "<td>".$sched["dep_date"]."</td>";
$str .= "<td>".$sched["ret_date"]."</td>";
$str .= "<td>".$sched["dep_time"]."</td>";
$str .= "<td>".$sched["ret_time"]."</td>";
$str .= "</tr>";

I get the required output in my table but...
the date displays as: 2010-09-14 and the time as: 09:00:00

I would like to convert is to a string so it reads: 14-Sep-2010 (or something similar) and 09:00 for the time. Can anyone help me out please as something I think I'm making something quite simple into a big deal. Some helpful advice and a push in the right direction would be greatly appreciated. :)

[Matthew1980]

Hi there m8fyu,

To format the date, you would probably need to specify the format in the sql query, here is a link to the DATE_FORMAT function used in mysql:[dev.mysql.com ] The link to the function is half way down the page, some useful things in that ;)

So I think as your query would look something like this (type on-the-fly, so could have bugs):-

$sql = "SELECT DATE_FORMAT('dep_date', %D %b %Y) AS dep_date_formatted, DATE_FORMAT('ret_date', %D %b %Y) AS ret_date_formatted, DATE_FORMAT('dep_time', %D %b %Y) AS dep_time_formatted, DATE_FORMAT('ret_time', %D %b %Y) AS ret_time_formatted FROM `zt_schedules` WHERE `fk_service_id` = '".$servid."' ORDERBY `dep_date` ";

I think you get the idea, then reference the $sched['dep_date_formatted'] vars like that, so that it matches the names reference in the query.

Hopefully that will point you in the right direction - unless I have done it wrong somewhere, there is more than likely an easier way, but that's similar to how I do that sort of thing.

Also, just for reference, you don't need double quotes around the array keys :$sched["dep_date"] use single ones: $sched['dep_date'] instead ;)

For your query, try to format it correctly too as you are using double quotes:-

$sql = "SELECT * FROM `zt_schedules` WHERE `fk_service_id` ='".$servid."' ORDER BY `dep_date`";

I know people will argue about that last point, as majority of that is down to preference, but IMHO it makes the code easier to read & understand ;)

Hope this helps,

Cheers,
MRb

[m8fyu]

Thanks Matthew

That's a great help. I really appreciate your time and comments and have learnt some valuable new stuff from you. Unfortunately we only have PHP4 installed right now so the DATE_FORMAT function is throwing up an error.

I'll keep reading the php.net pages and see what else I can learn.

Thanks again

[Matthew1980]

Hi there m8fyu,

What error is being thrown up? The function is mysql not php, its used directly in the sql query - where you can, give the workload to the server sometimes negates the need for doing extra php ;)

It should have worked, I'm now intrigued as to why its throwing an error, post the relevant parts of the code and we shall see if there is anything else that could be to blame.

Cheers,
MRb

[m8fyu]

Hi Matthew

Here is the full function (I split the sql just to make sure I got everything in)...
function show_schedule($arr)
{
$servid = adminTools::get_passed_var("servid");

//get all countries
$sql = "SELECT DATE_FORMAT('dep_date', %D %b %Y) AS dep_date_formatted,
DATE_FORMAT('dep_time', %D %b %Y) AS dep_time_formatted,
dep_port,
DATE_FORMAT('ret_date', %D %b %Y) AS ret_date_formatted,
DATE_FORMAT('ret_time', %D %b %Y) AS ret_time_formatted,
ret_port, short_descr, no_days, no_night, no_dives
FROM zt_schedules
WHERE fk_service_id = '".$servid."'
ORDER BY dep_date ";

/*$sql="SELECT * FROM zt_schedules WHERE fk_service_id='$servid' ORDER BY dep_date";*/
$this->_parent->db->readx($sql); //read
$rc=$this->_parent->db->resultcount(); //count
//$data=$this->_parent->db->data; //data
$str = "";
$str .= "<table width='100%' border='0'>";
$str .= "<tr>";
$str .= "<td>Dep Date</td>";
$str .= "<td>Dep Time</td>";
$str .= "<td>Dep Port</td>";
$str .= "<td>Ret Dat</td>";
$str .= "<td>Ret Time</td>";
$str .= "<td>Ret Port</td>";
$str .= "<td>Route</td>";
$str .= "<td>Days </td>";
$str .= "<td>Nights</td>";
$str .= "<td>Dives</td>";
$str .= "</tr>";

if($rc>0)
{
for($i=0;$i<$rc;$i++)
{
$sched = $this->_parent->db->data[$i];
$str .= "<tr>";
$str .= "<td>".$sched['dep_date_formatted']."</td>";
$str .= "<td>".$sched['dep_time']."</td>";
$str .= "<td>".$sched['dep_port']."</td>";
$str .= "<td>".$sched['ret_date']."</td>";
$str .= "<td>".$sched['ret_time']."</td>";
$str .= "<td>".$sched['ret_port']."</td>";
$str .= "<td>".$sched['short_descr']."</td>";
$str .= "<td>".$sched['no_days']."</td>";
$str .= "<td>".$sched['no_night']."</td>";
$str .= "<td>".$sched['no_dives']."</td>";
$str .= "</tr>";

}
$str .= "</table>";
return $this->_parent->_parse($str);
}
return $str;
}


The error message I get is:
Failed to Execute SQL Command.
Error Number: 1064
Error Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%D %b %Y) AS dep_date_formatted, DATE_FORMAT('dep_time', %

I'm using PHP4 if that makes a difference.

Thanks for the help. It's much appreciated. I'm pretty new to server side programming so this is all a tad beyond me right now.

Matt

[Readie]

DATE_FORMAT('dep_date', %D %b %Y) AS dep_date_formatted,
DATE_FORMAT('dep_time', %D %b %Y) AS dep_time_formatted,
dep_port,
DATE_FORMAT('ret_date', %D %b %Y) AS ret_date_formatted,
DATE_FORMAT('ret_time', %D %b %Y) AS ret_time_formatted,

Try replacing these, with these:

DATE_FORMAT(`dep_date`, %D %b %Y) AS dep_date_formatted,
DATE_FORMAT(`dep_time`, %D %b %Y) AS dep_time_formatted,
dep_port,
DATE_FORMAT(`ret_date`, %D %b %Y) AS ret_date_formatted,
DATE_FORMAT(`ret_time`, %D %b %Y) AS ret_time_formatted, 

I think your use of single quotes rather than backticks are what's tripping you here.

[Matthew1980]

Hi all,

Also, in the HTML code that you have, you haven't changed all of the $sched['dep_date_formatted'] to reference the names in the sql. Because you are changing the format you temporarily change the field name with the AS clause from dep_date to dep_date_formatted, so you need to reflect this in the HTML too, otherwise, you will be calling the data held in the format you don't want.

Readie: Yes you could well be right there, I should have used backticks there, but as I said TOTF - it's normally me who pulls you up on the quotations ;)

See how that gets you, but I think as the error is just down to the quotes. I have something very similar on one of my own projects, double checked it so the syntax is correct. Though the difference in my own code is the quotes/backticks are omitted, maybe I was lazy the day I did it :/

PS: Patience is they key with programming & caffine!

Cheers,
MRb

[rocknbil]

Just tossing in. :-)

DATE_FORMAT('ret_time'...
DATE_FORMAT(`dep_date`...

The identity of the problem is you don't quote field names. Backtics fix it, but most of the time they are not required. Where they are definately required is if the field name conflicts with a mySQL internal command, data type, or other reserved word, and if you don't know those, it's a good case for backticking all field and table names.

[Matthew1980]

Hi all,

Just a thought, but since I have looked over some different things I have done in the past, I have both

DATE_FORMAT(`column_name`, %D %b %Y)

and

DATE_FORMAT(column_name, '%D %b %Y')

both seem to function fine, so wouldn't hurt to do this then:

DATE_FORMAT(`column_name`, '%D %b %Y')

would it, seeing as I always seem to complain about the lack of backticks in other threads ;)

Where they are definately required is if the field name conflicts with a mySQL internal command, data type, or other reserved word, and if you don't know those, it's a good case for backticking all field and table names.

This is the exact reason I always do this, better than retro-fitting hindsight, would save some work ;)

Cheers,
MRb

[rocknbil]

Right . . . if your names don't conflict with anything, it's two less characters per field. :-P As for quoting the format string, I never knew that, just presumed you needed quoting, especially with delimiters.

[m8fyu]

Hi

I've just got back home and checked up the thread. A big thank you to everyone for all the help. I'll report back once I have implemented the changes.

Thanks again. All the help is very much appreciated

[m8fyu]

Back again.

I've tried with backtics and also without backtics but still get an error message. Could it be that I'm not running PHP5? According to php.net date_format requires (PHP 5 >= 5.2.0)

I've pasted my full code below just in case I'm still making typo's in the syntax

function show_schedule($arr)
{
$servid = adminTools::get_passed_var("servid");

//get all countries
$sql = "SELECT DATE_FORMAT(`dep_date`, %D %b %Y) AS dep_date_formatted,
DATE_FORMAT(`dep_time`, %D %b %Y) AS dep_time_formatted,
dep_port,
DATE_FORMAT(`ret_date`, %D %b %Y) AS ret_date_formatted,
DATE_FORMAT(`ret_time`, %D %b %Y) AS ret_time_formatted,
ret_port, short_descr, no_days, no_night, no_dives
FROM zt_schedules
WHERE fk_service_id = '$servid'
ORDER BY dep_date ";

$this->_parent->db->readx($sql); //read
$rc=$this->_parent->db->resultcount(); //count
//$data=$this->_parent->db->data; //data
$str = "";
$str .= "<table width='100%' border='0'>";
$str .= "<tr>";
$str .= "<td>Dep Date</td>";
$str .= "<td>Dep Time</td>";
$str .= "<td>Dep Port</td>";
$str .= "<td>Ret Dat</td>";
$str .= "<td>Ret Time</td>";
$str .= "<td>Ret Port</td>";
$str .= "<td>Route</td>";
$str .= "<td>Days </td>";
$str .= "<td>Nights</td>";
$str .= "<td>Dives</td>";
$str .= "</tr>";

if($rc>0)
{
for($i=0;$i<$rc;$i++)
{
$sched = $this->_parent->db->data[$i];
$str .= "<tr>";
$str .= "<td>".$sched['dep_date_formatted']."</td>";
$str .= "<td>".$sched['dep_time_formatted']."</td>";
$str .= "<td>".$sched['dep_port']."</td>";
$str .= "<td>".$sched['ret_date_formatted']."</td>";
$str .= "<td>".$sched['ret_time_formatted']."</td>";
$str .= "<td>".$sched['ret_port']."</td>";
$str .= "<td>".$sched['short_descr']."</td>";
$str .= "<td>".$sched['no_days']."</td>";
$str .= "<td>".$sched['no_night']."</td>";
$str .= "<td>".$sched['no_dives']."</td>";
$str .= "</tr>";

}
$str .= "</table>";
return $this->_parent->_parse($str);
}
return $str;
}


The error message in browser:

backtics:
Failed to Execute SQL Command.
Error Number: 1064
Error Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%D %b %Y) AS dep_date_formatted, DATE_FORMAT(`dep_time`, %

no backtics:
Failed to Execute SQL Command.
Error Number: 1064
Error Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%D %b %Y) AS dep_date_formatted, DATE_FORMAT(dep_time, %D

[m8fyu]

Yes. Please ignore my comment about php5. I had a momentary blip and forget that Matthew has already kindly pointed out to me that the function is sql not php. :)

[m8fyu]

I managed to sort out the date by enclosing the %D %b %Y in single quotes. The time isn't displaying at all though.

[m8fyu]

It's all working properly and now my html table is looking much nicer. I'd just like to say a big thank you to you all again.

[Matthew1980]

Hi there m8fyu,

Firstly, glad your where you wanted to be :)

Secondly, what was the fix then in the end?

Yes. Please ignore my comment about php5

Lol, I'm glad as I saw that, I was going to quote my reminder to you! As I said though, where you can place some workload on the mysql_query() as this will make it less code in the longrun, RAND() is a prime example of this, using RAND() in the sql query means that you don't need to use more lines of code to produce random results from a query that comes from the database, its already done! Cool!

Cheers,
MRb