Welcome Guest, Not a member yet? Register   Sign In
$query->result() and Mysql TIME_FORMAT queries
#1

[eluser]Unknown[/eluser]
Hey there,

Got a question. Writing a calendar where data is populated into it from a database, and I'm using TIME_FORMAT to format the time returned from the database. Here is my query:

Code:
SELECT
`hash`,
`title`,
`description`,
`tix_link`,
TIME_FORMAT(`start_time`, '%Y-%m-%d %r'),
TIME_FORMAT(`end_time`, '%Y-%m-%d %r')
FROM `events`
WHERE
(`start_time` BETWEEN '$start_low' AND '$start_high')
AND
(`end_time` BETWEEN '$end_low' AND '$end_high')
AND
(`published` = 1)

The query executes fine, and everything works as it should except the return value of the query. Let me give you an example of the var_dump() of one of these results:

Code:
object(stdClass)#19 (6) { ["hash"]=> string(9) "hash_sample" ["title"]=> string(11) "Sample Title" ["description"]=> string(33) "Sample event that is super sample" ["tix_link"]=> string(23) "http://www.sample.com" ["TIME_FORMAT(`start_time`, '%Y-%m-%d %r')"]=> string(22) "0000-00-00 03:38:42 PM" ["TIME_FORMAT(`end_time`, '%Y-%m-%d %r')"]=> string(22) "0000-00-00 03:38:47 PM" }

Notice how the index for the date is: TIME_FORMAT(`end_time`, '%Y-%m-%d %r')

I was wondering how to access this index in the object, since I can't just do $object->TIME_FORMAT(`end_time`, '%Y-%m-%d %r').
#2

[eluser]Hockeychap[/eluser]
The quickest way to get to any function based mysql field (i.e. time_format, date_format etc) is to use an field alias. Have a look http://dev.mysql.com/doc/refman/5.5/en/select.html and search for "A select_expr can be given an alias using AS alias_name" ...

That should help Smile

Basically if you make your query
Code:
SELECT
`hash`,
`title`,
`description`,
`tix_link`,
TIME_FORMAT(`start_time`, '%Y-%m-%d %r') as formattedstart,
TIME_FORMAT(`end_time`, '%Y-%m-%d %r') as formattedend
FROM `events`
WHERE
(`start_time` BETWEEN '$start_low' AND '$start_high')
AND
(`end_time` BETWEEN '$end_low' AND '$end_high')
AND
(`published` = 1)

you can use "formattedstart" and "formattedend" in your array access.

HTH
Justin




Theme © iAndrew 2016 - Forum software by © MyBB