472,951 Members | 2,091 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,951 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");
  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 3615
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

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

Similar topics

3
by: Eric Linders | last post by:
Hi, Pretty new to PHP/MySQL, but I have some programming experience in a past life. Hopefully you can help me out as I learn PHP/MySQL! :-) I have a MySQL database that includes customer...
14
by: windandwaves | last post by:
Hi Folk This question has been bugging me for month. I have a website where people can enter stuff into a mysql database. Some of this information will already be shown in some textareas and...
1
by: Sanny | last post by:
I am using a MySql database for chat application. Every 3 seconds the database is searched for various details. I would like to know How many individual can it handle at a time. Say there...
12
by: mistral | last post by:
phpMyAdmin 2.6.2 problem: can no connects to mySQL database: each time shown error #1045 - Access denied for user 'username'@'192.168.1.2' (using password: YES) Is seems, this is most common...
14
by: mistral | last post by:
Need php script to create mySQL database programmatically; since hosting configuration may not allow create database from script, script also need eliminate/rewrite all restrictions in appropriate...
1
by: bbpitpit | last post by:
hi there, i'm new here, can i know how to display the month name from database's datetime? for example: in database the date column storing 12/03/2006 and I want to display only it's month name...
9
by: christopher_board | last post by:
Hi all. I am trying to write a php page which connects to a MySQL Database which is supposed to get the results from a table within a database and display the results in a table. Below is the...
2
by: =?Utf-8?B?Sm9lIFRhdmFyZXM=?= | last post by:
I am using the DateTime.Parse method to parse user entered dates. The dates can be just the year, Month-year and Month-day-year in a variety of formats in a variety of cultures. I immediately parse...
1
by: mahendravishwa | last post by:
Hi, I want to change DateTime format in MySQL Database Table in dd/MM/yyyy format. Its possible so please provide guidance. Thanks
1
by: mosesdinakaran | last post by:
Hi All, How to find out the last accessed date of a MySql Database. I have around 200 databases in my server some are used and some are not in Use, I want to remove the database that are not...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...

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.