468,727 Members | 1,639 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,727 developers. It's quick & easy.

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");
  2.  
  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.   }
  11.  
Oct 2 '10 #1
2 3452
serdar
88
Use this following query:

SELECT *, DAYOFMONTH(datetime_column) AS formatted_date FROM Events

Now you can get it with your new key as:

$row['formatted_date']
Oct 3 '10 #2
kovik
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");
  2.  
  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
1 post views Thread by CARIGAR | last post: by
9 posts views Thread by bryonone | last post: by
xarzu
1 post views Thread by xarzu | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.