Getting just day of month from DATETIME mysql database

[Julian Evans]

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.

$result = mysql_query("SELECT * FROM Events");
 
while($row = mysql_fetch_array($result))
  {
  echo "<div id=event_text>";
  echo "<h3>" . $row['display_date'] . "</h3>";      
  echo "<h4>" . $row['title'] . " </h4>";
  echo "<p id=event_text>" . $row['short_description'] . "</p>";
  echo "</div>";   
  }
 

[serdar]

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']

[kovik]

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.

$result = mysql_query("SELECT * FROM Events");
 
while($row = mysql_fetch_array($result)) {
  $day_of_month = (int)date('j', strtotime($row['display_date']));
}