DATE_FORMAT MySQL function for DATETIME entries

PHP
1

Hello,

I am trying to use the MySQL DATE_FORMAT function to change the format of dates saved on the database as DATETIME entries so that when produced on the web page they are displayed in the desired format.

When using the below code I am getting this error message (line 108 being the line that contains the DATE_FORMAT function):
Parse error: syntax error, unexpected ‘’) FROM production’’ (T_CONSTANT_ENCAPSED_STRING) in C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\productions\index.php on line 108

When I use the same bit of code (‘SELECT … FROM production’) using pure MySQL (in Command Prompt), it creates the desired effect but I can’t seem to get it to work in this php file.

Any help gratefully received as I’m starting to pull my hair out on this one!

Andy

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

$result = mysqli_query($link, 'SELECT id, productionname, DATE_FORMAT(prodentrydatetime, '%W %D %M %Y') FROM production');
if (!$result)
{
$error = 'Error fetching productions: ' . mysqli_error($link);
include 'error.html.php';
exit();
}

while ($row = mysqli_fetch_array($result))
{
$productions[] = array('id' => $row['id'], 'productionname' => $row['productionname'], 'prodentrydatetime' => $row['prodentrydatetime']);
}

include 'productions.html.php';
?>

The information is then output on ‘productions.html.php’ like this:


<?php foreach ($productions as $production): ?>
<?php echo htmlspecialchars($production['productionname'], ENT_QUOTES, 'UTF-8'); ?>
<?php echo htmlspecialchars($production['prodentrydatetime'], ENT_QUOTES, 'UTF-8'); ?>
<?php endforeach; ?>
2

It’s because you’re using single quotes to encapsulate the SQL, and to encapsulate the data format.
Change it to this:


$result = mysqli_query($link, "SELECT id, productionname, DATE_FORMAT(prodentrydatetime, '%W %D %M %Y') FROM production");
3

Ah, I knew it would be something ridiculously simple like that.

However, that change alone still generates this error (repeated nine times for each of the nine entries currently on the database):-
Notice: Undefined index: prodentrydatetime in C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\productions\index.php on line 118

But if you use the AS function like this then it works:-

$result = mysqli_query($link, "SELECT id, productionname, DATE_FORMAT(prodentrydatetime, '%W %D %M %Y') AS prodentrydatetime FROM production");

This link is pretty useful and explains how using the AS function is especially useful when ordering date entries.
http://www.geeksww.com/tutorials/database_management_systems/mysql/tips_and_tricks/mysql_dateformat_function_and_order_by.php

Thanks so much for that!

Andy

4

The error message tells you what the problem is. There is no “prodentrydatetime” variable or key with that name in the array.

5

Sorry, I’m still quite new to this so you’ll have to forgive me, but I thought that the below section of code from the bottom of my index lists ‘prodentrydatetime’ as a variable. Where have I gone wrong so that I don’t need to use the AS function?

Cheers!

while ($row = mysqli_fetch_array($result))
{
$productions[] = array('id' => $row['id'], 'productionname' => $row['productionname'], 'prodentrydatetime' => $row['prodentrydatetime']);
}

include 'productions.html.php';
?>