formatting date and queries

ztron

i'm getting more familiar with php, but i ran into a problem today while running a query.

the table i am using has a date field with all the dates in a format like 07/31/03 (m/d/y). when i run a query to print data from that field it prints it like 2003-07-31.

is there anyway i can format it so that it prints it exactly how it is in the table?

also, i eventually want to run a query that lists the top 10 enroller distributors for the current month. there are fields that have the information for me to do this, but what i am unsure of how to do is compare the date field in the database to the current month. distributors sign up ever day and i just want it for the month not one date. is it possible to do this?

the date field is dm_start_date so i just want to compare it to the current month. i figure i will probably have to create a variable and set it to some value for the current month and change it on a monthly basis. i don't know. my mind is a little taxed now. any help or suggestions are appreciated.

weevil

If you are using MySQL, take a look at the date_format function here:
http://www.mysql.com/doc/en/Date_and_time_functions.html
and use that in your select.

ztron

thanks, that should help me complete the script i am working on. the php book i have is really old and a lot of the newer functions aren't mentioned or they just don't completely explain them for use in a query.

Norman_Graham

Hi Ztron

A book, even an old one, is invaluable when learning. However, you also want to bookmark both the mysql manual and the PHP manual. Incidentally, if you append a function name straight to the PHP address thus: http://www.php.net/mysql_connect, it takes you straight to the explanation for that function. Neat, huh?

Best o luck

Norm

ztron

Still having trouble with this. The dates in the database are entered like 11-06-03. I know there are entries for this, so why aren't they being displayed. Do I have to format the date (dm_start_date) first? Also, how would I compare it to an entire month other than linking with OR for every date of the month and then changing the number of the month for each one every time it's a new month? Seems like there would be an easier way to do that, but I don't know how. Any help or suggestions are appreciated.

               $query = "Select TOP 50 dm_id, dm_list, dm_phone, dm_email, dm_start_date 
		 		  FROM distributor_main
				   ORDER BY dm_start_date DESC
				   WHERE dm_start_date = '11-06-03'";
display_invoice_search_results($query);

ztron

actually the dates are entered as 11/06/03, but when i try to compare it to that it doesn't work either

ztron

alright, i tried this now with the date_format function and it still doesn't work. any suggestions?

/* Monthly enrollment search */ 
         $query = "Select dm_id, dm_list, dm_phone, dm_email, dm_sponsor_id, dm_start_date
		 		   FROM distributor_main
				   ORDER BY dm_sponsor_id DESC
				   WHERE DATE_FORMAT(dm_start_date, '%m/%d/%y') != '11/01/03' && <='11/30/03'";
         display_search_results($query);