[SOLVED] Datetime in sql db

[ababmxking]

Ok i want to make a countdown but i need to be able to select the day, hour, minute, and second from a datetime with the dates entered as Y-d-m H:i:m.

 

i tried both of these but neither work.

$day = mysql_fetch_object(mysql_query("SELECT end DAY FROM roundslog WHERE ended='No' AND end>'$date2'"));

 

And

 

$day = mysql_fetch_object(mysql_query("SELECT DAY end FROM roundslog WHERE ended='No' AND end>'$date2'"));

 

 

[DarkWater]

I'm not sure what you're tryign to do with "DAY end" and stuff.  Please elaborate.

[ababmxking]

ok i have a text based game and were trying to incorporate a round feature (so it restarts every week) and i save the end date in the database and im trying to make a countdown (Ex Time left till rounds over: 4 Days, 3 Hours, 34 Minutes, and 43 Seconds.) But first I  need to be able to select the Day, Hour, Minute and Second in seperate querys from a datetime in the database.

[Lodius2000]

also mysql_fetch_object(mysql_query shouldnt be together, pick one or the other

 

query will return $day as a variable, or an array if more than 1 match for the query is found

fetch object turns $day into an object

 

so if you have a field in your table called end (which i assume you do, your sql is a little jumbled)

 

with fetch object you access the object  as $day->end versus an array

 

that could contribute to the problem because you are telling php to return 2 different things

[DarkWater]

@Lodius:  That's not the problem, it's perfectly fine.  He's passing the result of the query to mysql_fetch_object() in one step rather than two.  I just don't understand his goal.

[Lodius2000]

oooooooohhhhhh

 

try

 

$row = mysql_query("SELECT UNIX_TIMESTAMP(`field_name`) AS formatted_time......blah blah blah

 

call it with date('h', $row['formatted_time'])

 

this assumes that field_name is a DATETIME type in your table

 

EDIT:

 

and if your not storing your time in a field like DATETIME or TIMESTAMP or DATE or TIME.... start. life will be much easier

[ababmxking]

ya im using DATETIME

 

so correct me if im wrong please. but i would do

$query = mysql_query("SELECT UNIX_DATETIME(`end`) AS day FROM roundslog WHERE blah blah");

 

correct?

 

 

[Lodius2000]

yeah that looks good

 

then call it with

 

date('h', $query['day']) and that will pull the hours out of datetime

[ababmxking]

what you mean call it with

date('h', $query['day'])

 

like

 

$day = date('h', $query['day']);

 

and if i were to echo $day it will show up as the day stored in the db?

[Lodius2000]

yes and no, in this case $day would be the hour stored in this particular datetime entry

 

http://us3.php.net/date has all the one letter codes that pull out and format time

 

for instance my home page has

 

a query: SELECT UNIX_TIMESTAMP(`timefield`) AS formatted_time

 

and formats it date('h:i A \o\n D. F j, Y.', $row['formatted_time'])

 

and prints it in html as

 

03:08 PM on Sun. July 6, 2008 for example

 

but since you said you wanted to do everything separate just follow through and make variables for all your components of date() that you want and then since they will hold integers most likely, (day of week will hold a string) you an do math on them to do your countdown

[ababmxking]

o ok i see, i thought i would have to do some kind of fetch. sorry im still pretty new to php.

 

Thank you, Lodius2000 and DarkWater for the help .

[PFMaBiSmAd]

Since the datetime is already in a usable format in the database, you are going through a lot of extra processing to convert it to a format only to convert it back.

 

In your original post, I will guess you were attempting to use the mysql DAY() function to get the day of the month 1-31? The proper syntax would have been -

 

SELECT DAY(end) the_rest_of_your_query_here...

 

Since your code nested the query function within a fetch function, there is no way it could have had any error checking, error reporting, or error recovery logic, which would have alerted you to the fact that the query failed and told you what type of problem occurred with the query.

 

Also -

I  need to be able to select the Day, Hour, Minute and Second in separate queries from a datetime in the database.

Do this in a single query so as to not waste processor time doing four separate queries.

[ababmxking]

ok well for some reason none of this is working, im just going to store the end dates "Days, Hours, Minutes and Seconds" separately in the db. and since i dont have that big of a user count right now ill worry about servers later on when i become a better coder.

 

But thanks for all the help guys, I really appreciate it. If it wasn't for people like you guys willing to help me I don't think i would get much done.