using mysql date conversion in SELECT

willl

Hi,

I am trying to pull dates out of a table and convert them to a different format.

However it's not working:

$query = mysql_query("SELECT link_log.date_format(date,'%W, %M %D, %Y.') 
FROM link_log INNER JOIN link_company ON  link_log.company_id = link_company.id 
WHERE 1") or exit(mysql_error());

It is simply outputing nothing.

Do you think that it's not working becase it is in a join select?

Thanks

Will

Roger_Ramjet

date_format is an inbuilt function, not a column name which is how you are using it. Try

$query = mysql_query("SELECT date_format(link_log.date,'%W, %M %D, %Y.')
FROM link_log INNER JOIN link_company ON  link_log.company_id = link_company.id
WHERE 1") or exit(mysql_error());

Another point: DON'T USE RESERVED WORDS FOR TABLE OR COLUMN NAMES: and 'date' is a reserved word. It is also meaningless as a column name: what date? date_entered, date_updated, birth_date, etc, etc?

willl

ahh of course.

And point taken.

Thanks!!

willl

Spoke too soon:

Still outputing nothing.

Maybe theres soomething wrong in the rest of the code:

<?php 
include 'connect.php';

$company = $_POST['company'];

echo "<h1>Results for ".$company."</h1><br /><br />";

$query = mysql_query("SELECT date_format(link_log.added,'%W, %M %D, %Y.') FROM link_log INNER JOIN link_company ON  link_log.company_id = link_company.id WHERE 1") or exit(mysql_error());  

echo "<table cellspacing=\"2\" cellpadding=\"2\">\n";

while($row = mysql_fetch_array($query)) {
echo "<tr><td>".$company."</td><td>$row[added]</td></tr>\n";
}

echo "</table>\n";

Roger_Ramjet

2 points

Take out the ' WHERE 1 '

change the code to

echo "<tr><td>".$company."</td><td>$row['added']</td></tr>\n";
} 

// or better yet

echo "<tr><td>".$company."</td><td>".$row['added']."</td></tr>\n";
}

if you want to see anything

But, debugging101,
1. check your query syntax by echoing the query string to the screen BEFORE running it
2. run the same query string in phpmyadmin or console or whatever you use to check that there are any results in the first place
3. echo mysql_num_rows($result) to find out if you have any rows to display - an empty resultset is not an error.

willl

Ok,

i have taken out "WHERE 1" and also changed the code to ".$row['added']." with no luck still.

I ran the query from mysql-front which is what i'm using and the dates were displayed and converted.

I echo'd the query and "Resource id #2" was printed.

Any other thoughts?

Thanks

Will

willl

Ok,

i have taken out "WHERE 1" and also changed the code to ".$row['added']." with no luck still.

I ran the query from mysql-front which is what i'm using and the dates were displayed and converted.

I echo'd the num rows and it counted 32.

Any other thoughts?

Thanks

Will

Roger_Ramjet

Whoops, sorry. I was not paying attention. There is no column in the resultset called 'added' so the call to $row['added'] fails. Should have spotted that earlier. What the column name ends up being in mysql once you apply the date_format() I don't know - you could print the $row array keys to find out if you're interested.

Simple fix is to alias it in the query

$query = mysql_query("SELECT date_format(link_log.added,'%W, %M %D, %Y.') AS added FROM link_log INNER JOIN link_company ON  link_log.company_id = link_company.id") or exit(mysql_error()); 

// or address the data by it's array index is another way to fix this

echo "<tr><td>".$company."</td><td>".$row[0]."</td></tr>\n";

willl

Well it works now!

Thanks for the help.