Jump to content

[SOLVED] Datetime in sql db


ababmxking

Recommended Posts

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'"));

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 :)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.