471,049 Members | 1,449 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,049 software developers and data experts.

Getting just day of month from DATETIME mysql database

Hi, I have a mysql database that stores events and information about them. There is a column with a DATETIME value used to sort the events by the time they start. I want to get the day of the month number from this column and then insert it into the HTML so that a picture file will be correctly selected based on what day of the month the event is happening on. I know I need to use SELECT DAYOFMONTH(datetime_column) somehow, but I can't figure out how to code this properly. This is my first php/mysql project and I am not a programmer. Below is the code I have been using that displays the events in order. Any links to relevant things to read would also be appreciated.

Expand|Select|Wrap|Line Numbers
  1. $result = mysql_query("SELECT * FROM Events");
  3. while($row = mysql_fetch_array($result))
  4.   {
  5.   echo "<div id=event_text>";
  6.   echo "<h3>" . $row['display_date'] . "</h3>";      
  7.   echo "<h4>" . $row['title'] . " </h4>";
  8.   echo "<p id=event_text>" . $row['short_description'] . "</p>";
  9.   echo "</div>";   
  10.   }
Oct 2 '10 #1
2 3507
Use this following query:

SELECT *, DAYOFMONTH(datetime_column) AS formatted_date FROM Events

Now you can get it with your new key as:

Oct 3 '10 #2
1,044 Expert 1GB
A much easier method would be to select the entire date, as usual, from the database and then get the day of the month using the date() function.

Expand|Select|Wrap|Line Numbers
  1. $result = mysql_query("SELECT * FROM Events");
  3. while($row = mysql_fetch_array($result)) {
  4.   $day_of_month = (int)date('j', strtotime($row['display_date']));
  5. }
Oct 4 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Eric Linders | last post: by
14 posts views Thread by windandwaves | last post: by
12 posts views Thread by mistral | last post: by
9 posts views Thread by christopher_board | last post: by
2 posts views Thread by =?Utf-8?B?Sm9lIFRhdmFyZXM=?= | last post: by
1 post views Thread by mosesdinakaran | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.