gazfocus Posted December 17, 2008 Share Posted December 17, 2008 I am designing a website that will have event dates on it which will be stored in a database. I am in the early stages of creating the page to call the info from the database so please excuse the basicness of the code, but my question is... When the page pulls the date from the database it's in yyyy-mm-dd format but I would like it to be displayed as (for example) 1st January 2009. Is this possible? Here is my code: <html> <head> <SCRIPT TYPE="text/javascript"> </SCRIPT> <link href="/joomla/templates/at_flexmen/css/template_css.css" rel="stylesheet" type="text/css"> </head> <body> <?php ////////////////////////////////////////// //// MySQL Database Connection /////////// ////////////////////////////////////////// $host = "xxxxx"; $user = "xxxxx"; $db_name= "xxxxx"; $pass= "xxxxx"; $conn = mysql_connect($host, $user, $pass) or die(mysql_error()); mysql_select_db($db_name, $conn) or die(mysql_error()); $sql = "SELECT * FROM kirkbydiarydates" or die(mysql_error()); $result=mysql_query($sql,$conn); while ($newArray = mysql_fetch_array($result)) { $eventTime = $newArray[eventTime]; $eventDate = $newArray[eventDate]; $venue = $newArray[venue]; $title = $newArray[title]; $description = $newArray[description]; } echo $eventTime . "<br />"; echo $eventDate . "<br />"; echo $venue . "<br />"; echo $title . "<br />"; echo $description . "<br />"; ?> </body> </html> Quote Link to comment Share on other sites More sharing options...
trq Posted December 17, 2008 Share Posted December 17, 2008 You can format the dates any which way you like, take a look at mysql's date & time functions. Quote Link to comment Share on other sites More sharing options...
gazfocus Posted December 17, 2008 Author Share Posted December 17, 2008 You can format the dates any which way you like, take a look at mysql's date & time functions. Hi, I did find that when I google it but I couldn't figure out how to tag that on to my code. Could you help? Quote Link to comment Share on other sites More sharing options...
trq Posted December 17, 2008 Share Posted December 17, 2008 Ok. For starters, SELECT * is very inefficient, and your code needs some definite attention. How many records are you expecting your query to retrieve? Because at the moment it will only ever display the last one. Quote Link to comment Share on other sites More sharing options...
gazfocus Posted December 17, 2008 Author Share Posted December 17, 2008 Ok. For starters, SELECT * is very inefficient, and your code needs some definite attention. How many records are you expecting your query to retrieve? Because at the moment it will only ever display the last one. As you can probably tell, I am a beginner at MySQL... I will be refining my query to fetch all events where the date is equal to or after the current date, so I guess it could be upto a 20-30 records. Quote Link to comment Share on other sites More sharing options...
Goldeneye Posted December 17, 2008 Share Posted December 17, 2008 Use the date() function to format a timestring from a database. You'll also want to put it into a function. It may not seem necessary now, but when/if you have multiple pages that use the date() function and you decide you want to change the format, you'll have to change it on every page. If you put it in a function, you only have to change it once. Example <?php function display_time_string($timestamp){ if($timestamp==0) return 'never'; return date('M j Y', $timestamp); } //$timestamp is the timestamp from the database ?> Quote Link to comment Share on other sites More sharing options...
gazfocus Posted December 17, 2008 Author Share Posted December 17, 2008 Use the date() function to format a timestring from a database. You'll also want to put it into a function. It may not seem necessary now, but when/if you have multiple pages that use the date() function and you decide you want to change the format, you'll have to change it on every page. If you put it in a function, you only have to change it once. Example <?php function display_time_string($timestamp){ if($timestamp==0) return 'never'; return date('M j Y', $timestamp); } //$timestamp is the timestamp from the database ?> Thanks for that. For the 'eventTime' in my database, it's a VARCHAR because some people that enter the data in the old site tend to put just '7pm' rather than 19:00, etc. The 'eventDate' is a DATE field. So in your example, do I still use timestring or something else? Quote Link to comment Share on other sites More sharing options...
trq Posted December 17, 2008 Share Posted December 17, 2008 Id'e still recomend retrieving the date in the format you actually want. You'll need to clean your code up a bit too to handle multiple records and not throw warnings. <html> <head> <SCRIPT TYPE="text/javascript"> </SCRIPT> <link href="/joomla/templates/at_flexmen/css/template_css.css" rel="stylesheet" type="text/css"> </head> <body> <?php $host = "xxxxx"; $user = "xxxxx"; $db_name= "xxxxx"; $pass= "xxxxx"; $conn = mysql_connect($host, $user, $pass) or die(mysql_error()); mysql_select_db($db_name, $conn) or die(mysql_error()); $sql = "SELECT eventTime, DATE_FORMAT(eventDate,'M j y') as eDate , venue, title, description FROM kirkbydiarydates"; if ($result = mysql_query($sql,$conn)) { if (mysql_num_rows($result)) { while ($newArray = mysql_fetch_array($result)) { $eventTime = $newArray['eventTime']; $eventDate = $newArray['eDate']; $venue = $newArray['venue']; $title = $newArray['title']; $description = $newArray['description']; echo $eventTime . "<br />"; echo $eventDate . "<br />"; echo $venue . "<br />"; echo $title . "<br />"; echo $description . "<br />"; } } } ?> </body> </html> Quote Link to comment Share on other sites More sharing options...
gazfocus Posted December 17, 2008 Author Share Posted December 17, 2008 Id'e still recomend retrieving the date in the format you actually want. You'll need to clean your code up a bit too to handle multiple records and not throw warnings. Thanks for your help thorpe, i really appreciate it. I copied your code in and I do receive all the records which is great, but instead of getting a date, i get "M j y" (minus the ") Quote Link to comment Share on other sites More sharing options...
Goldeneye Posted December 17, 2008 Share Posted December 17, 2008 That's because you store your dates in a varchar-type field (which I assume they are already formatted). You should use an int-type field, instead and store your dates/times as 10-digit integers. To convert your existing dates, use the strtotime() php function (go to PHP.net and search for it). Quote Link to comment Share on other sites More sharing options...
trq Posted December 17, 2008 Share Posted December 17, 2008 Sorry, your query should be..... $sql = "SELECT eventTime, DATE_FORMAT(eventDate,'%D %M %Y') as eDate , venue, title, description FROM kirkbydiarydates"; Quote Link to comment Share on other sites More sharing options...
gazfocus Posted December 17, 2008 Author Share Posted December 17, 2008 Sorry, your query should be..... $sql = "SELECT eventTime, DATE_FORMAT(eventDate,'%D %M %Y') as eDate , venue, title, description FROM kirkbydiarydates"; A huge thank you to you. I have also (from your code) figured out how to add the day too so my result now reads Sunday 4th January 2009. Many thanks again Quote Link to comment Share on other sites More sharing options...
gazfocus Posted December 17, 2008 Author Share Posted December 17, 2008 Hi, Sorry to be a royal pain in the backside but do you know of an easy way to show all events from todays date onwards? (Do I need to use date_sub or something?) or can I just use something like WHERE eventDate=>curdate() Thanks again Quote Link to comment Share on other sites More sharing options...
gazfocus Posted December 17, 2008 Author Share Posted December 17, 2008 no worries have sussed it Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.