Adding DATE_FORMAT to my query bombs it...I want to know why

[Guest]

Hello All,

Well, I'm writing a VERY simple page that shows the user a lidt of recent updates from data in a database set for timed release.

This query works absolutely perfect:

$conn = dbConnect('query');
$sql = "SELECT *
        FROM schedule
        WHERE start_date <= CURDATE()
        AND end_date > CURDATE()
        ORDER BY store_number ASC";
$result = $conn->query($sql) or die(mysqli_error());
$upDates = $result->fetch_assoc();

BUT...

...when I add a DATE_FORMAT command to it as shown below...

$conn = dbConnect('query');
$sql = "SELECT *
        FROM schedule
        WHERE start_date <= CURDATE()
        AND end_date > CURDATE()
        ORDER BY store_number ASC
        DATE_FORMAT(start_date, '%M/%e/%D/%Y') AS start_date";
$result = $conn->query($sql) or die(mysqli_error());
$upDates = $result->fetch_assoc();

...it bombs.

This query is selecting all data from one 4-column table and displays the data perfectly in the first example, as I mentioned. I don't like the date format, and I wanted to change it, thus the addition of the DATE_FORMAT in the second example. start_date is one of the columns in my table called 'schedule'.

Please tell me why this is bombing. I have not a clue and have analyzed and re-tested the code to no avail.

Many thanks in advance for your help!

Sincerely,

wordman

[bregent · Accepted solution]

The SQL is invalid. You can't just add a select column to the end of a SQL query. You must obey the basic rules of SQL structure. Select columns must appear before the FROM clause.

$conn = dbConnect('query');
$sql = "SELECT *,  DATE_FORMAT(start_date, '%M/%e/%D/%Y') AS start_date1
        FROM schedule
        WHERE start_date <= CURDATE()
        AND end_date > CURDATE()
        ORDER BY store_number ASC";
$result = $conn->query($sql) or die(mysqli_error());
$upDates = $result->fetch_assoc();

I've aliased to start_date to start_date1 to avoid ambiguity.  I don't know MySQL so I'm just taking your DATE_FORMAT function as supplied.

[Guest]

Bregent,

Once again, you saved the day! Many, many thanks for your help, it worked!

Sincerely,

wordman