DATE_FORMAT() Question (some code inc'd)

Anon

Hi All, I asked some of this questions yesterday, and got back an answer from a fella named Alan (he may or may not read this). Anyway, he told me to use MySQL's DATE_FORMAT() to format a DATE string from YYYY-MM-DD into whatever I wanted (Weekday, Month Day, Year). Well, I read MySQL's documentation and it works fine from a MySQL Prompt. But When I put it in PHP, it just shows up on the web as YYYY-MM-DD. I'm putting my code in here to see if anyone can something wrong with what I'm doing. Because I can't figure it out.

It's probably stupid simple :\

<--- Begin Code Snippet --->
$bill_fetch = "SELECT *, DATE_FORMAT('due_date','%W, %M %D, %Y') FROM newbills";
$fetch_query = mysql_query($bill_fetch)
or die("Could Not Fetch Bills.");

if (!$fetch_query) {
echo "Could Not Get Newly Inserted Items From The Database.";

    } else {

$row = mysql_fetch_array($fetch_query);
$id = $row["id"];
$company = $row["company"];
$address = $row["address"];
$phone = $row["phone"];
$account = $row["account"];
$amount_owed = $row["amount_owed"];
$due_date = $row["due_date"];
$balance = $row["balance"];
$status = $row["status"];
$filed = $row["filed"];
$notes = $row["notes"];
$added_on = $row["added_on"];
$added_by = $row["added_by"];

echo "into an HTML table the above variables."
<--- End Code Snippet --->

I of course have the connection/db select, all that stuff and my Braces }{ but exluded for (some) brevity. Any suggestions? Thanks everyone.
./brm

trippyd

Ok, here is your problem, you are selecting *, which includes due_date, and the DATE_FORMAT results. When you set:
$due_date = $row["due_date"];
you are just pulling the due date. change your SQL from:

SELECT *, DATE_FORMAT('due_date','%W, %M %D, %Y') FROM newbills

to:

SELECT *, DATE_FORMAT('due_date','%W, %M %D, %Y') as formatted_date FROM newbills

and your assignment to:

$due_date = $row["formatted_date"];

and you should get what you want. Generally, you will get better performance out of your sql if you list only the column names you need instead of using * (unless you really need ALL the columns, which you don't, you don't need due_date, just the formatted version). Also, look into the php function list(). It will save you a lot of typing, ie:

list($row, $id, $yadda, $somevariable, $x, $etc) = mysql_fetch_array($fetch_query);

instead of each individual assignment, it will make all the assignments at once.

Hope this helps

Anon

You're right it is a simple problem. You are select * and a date_format() item. But you are putting the $row["duw_date"] into you're variable and displaying that.

When you run a function in an SQL query the DB gives that function a column name and you need to use that column name to retrieve the data back out in PHP. Unfortunatly that generated column name is usually not something easy to guess. So you should use the AS operator:

$bill_fetch = "SELECT *, DATE_FORMAT('due_date','%W, %M %D, %Y') <b>AS fmt_date</b> FROM newbills";

The AS operator renames the field before it to the name after it.

Then put the field "fmt_date" into your variable.

mickro

Marti,

You need to select DATE_FORMAT as a field name:.

Try this:

$bill_fetch = "SELECT *, DATE_FORMAT('due_date','%W, %M %D, %Y') as due_date FROM newbills";

Hope this helps

Mike
www.digitalegg.net

Anon

Thanks alot everyone!! 🙂 Your methods are all correct, With one exception, that I just figured out. Where I've got:
SELECT *, DATE_FORMAT('due_date','%W, %M %D, %Y') AS bleh FROM newbills

needs to drop the '' from around 'due_date' But that was my problems, not you folks. Thanks a lot. I'll take a look at the list() function David, I'm still picking this stuff up (the $row[""] thing is how PHP Essentials does it.

Anyway, take care. And thanks again everyone!
./brm