Displaying List of Dates 1

[andy98]

Hi

I have a bunch of records in a table of a mySQL database and one of the fields is a DATE field in the following format (0000-00-00)

Would I am struggling to do is to format these dates on my display page. I am inserting them as NOW() - but how would I pull them out and format them (dd-mm-yyyy) within query result?

This is my query:

$query = "SELECT  * FROM customer, acc_order
WHERE customer.cust_id = acc_order.cust_id
AND acc_order_dispatched_by = ''
ORDER by acc_order.acc_order_id asc";

$result = mysql_query($query) or die (mysql_error());

And this is my output

while($row = mysql_fetch_array($result)){
echo "<td class='blue'>$row[$acc_order_date]</td>";

Does anyone know how I might format this to (dd-mm-yyyy)

 

[kenrbnsn]

Use the date() function:

echo "<td class='blue'>date('d-m-Y',strtotime($row[$acc_order_date]));</td>";

There's probably also a function in MySQL to format the output the way you want.

Ken

 

[DRJ478]

I would recommend to let the MySQL server do the work. Use the date_format function in your queries:

http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

 

[andy98]

Hi

That simply displays...

('d-m-Y',strtotime());

on the results page!

 

[DRJ478]

It should read:

echo "<td class='blue'>".date('d-m-Y',strtotime($row[$acc_order_date]))."</td>";

 

[andy98]

This is my original query:

$query = "SELECT  * FROM customer, acc_order
WHERE customer.cust_id = acc_order.cust_id
AND acc_order_dispatched_by = ''
ORDER by acc_order.acc_order_id asc";

How would I go about using DATE_FORMAT for that specific query?

 

[andy98]

DRJ478
Your last snipet works except it now displays all rows with the same - DATE.

Shame - I thought that was the answer!

 

[DRJ478]

You want to name the date column and encapsulate it with the date_format function. I have no idea what your date column is named, so in the example I just name it myDate and alias it to the result vcar niceDate:

$query = "SELECT *, DATE_FORMAT(myDate,'%W %M %Y') AS niceDate FROM ....

Of course I gave you a silly date format, just to make sure you have a look at the MySQL manual page.

 

[andy98]

Based upon my original query below - how would I expand that query to use the DATE_FORMAT function?

$query = "SELECT  * FROM customer, acc_order
WHERE customer.cust_id = acc_order.cust_id
AND acc_order_dispatched_by = ''
ORDER by acc_order.acc_order_id asc";

Thanks for your assistance!

 

[DRJ478]

Just as shown in my previous post.
Replace the column myDate with your actual column name and append everything after the FROM just as in your own query.

 

[andy98]

So that's how it's done!!! ;o)

Nice one DRJ478!

In case anyone needs an example for the future:

$query = "SELECT  *, DATE_FORMAT(acc_order_date, '%d %m %Y') AS niceDate 
FROM customer, acc_order
WHERE customer.cust_id = acc_order.cust_id
AND acc_order_dispatched_by = ''
ORDER by acc_order.acc_order_id asc";

Many thanks for your help!