Member Avatar for mtho

Hi Guys Im a newbie and a somewhat beginner in PHP
Here is my problem

I want to insert this date format - DATE_FORMAT(news.updated, '%%M %%e, %%Y') AS formatted FROM news


into this already existing sql statement - $sql = "SELECT * FROM authors aut, news new WHERE aut.aurthor_id=new.arthor_id AND article_id=1 ORDER BY updated DESC LIMIT 15";


I have tried all sorts of way but keep getting this (or similar) error message - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(news.updated, '%%M %%e, %%Y') AS formatted from news WHERE aut.aurthor_id=new.a' at line 1

  • 6 Contributors
  • 5 Replies
  • 229 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by rajeevphp2011

Dani AI

Generated

Several small, independent problems explain the syntax error that appears in the thread. The DATE_FORMAT fragment needs to sit in the SELECT list and must reference the same table alias used in FROM; column names must match the schema (the inconsistent spellings flagged by are a real cause); and percent sequences can be mangled by PHP string functions if the SQL is assembled with printf/sprintf. ’s note about percent tokens is on point, and the PHP-side formatting suggestions from and are valid alternatives.

A working pattern puts the format expression into the SELECT, uses an explicit JOIN, and fully qualifies columns. Example (adapt aliases/names to the real schema):

$sql = "
SELECT aut.*, new.*, DATE_FORMAT(new.updated, '%M %e, %Y') AS formatted_date
FROM authors AS aut
INNER JOIN news AS new ON aut.author_id = new.author_id
WHERE new.article_id = 1
ORDER BY new.updated DESC
LIMIT 15
";

Notes on PHP-side pitfalls and an alternative: if the query string is built with sprintf/printf, those functions treat % as format markers—escape them as %% inside the sprintf format string so the final SQL has single % tokens. A safer and clearer approach is to use prepared statements (mysqli/PDO) and, when formatting in PHP, use the DateTime class to avoid locale/timezone surprises:

$dt = new DateTime($row['updated']);      // $row from fetch
$formatted = $dt->format('F j, Y');       // e.g. March 13, 2012

Quick checklist: verify exact column names in the DB, avoid ambiguous SELECT * when columns overlap, always qualify ORDER BY with the table alias, and print the final SQL string before execution to spot stray keywords or doubled percent signs.

Recommended Answers

All 5 Replies

Member Avatar for Member #120589

Perhpas you want to check your spelling too:

authors aut, news new WHERE aut.aurthor_id=new.arthor_id

They may be right in your case, but 3 different ways of spelling author?

Member Avatar for Que336

Your question doesn't make sense...I think you have a field with a timestamp and you want it to be displayed as a normal date in PHP. Is that what you mean. I don't know how to do it but I'm sure ARDAV can help you.

Member Avatar for weekendrockstar

The link that StephNicolaou provided should help you out. You can also look at http://www.tizag.com/phpT/phpdate.php which gives much of the same information but Tizag is a good beginners site and they tend to explain things in as simplified way as they can.

PHP has the built-in function 'date' which gets the timestamp from the server and within the parenthesis of the date function you can specify how you want the date to be displayed.

SO you could do something like:

<?php
$date_time = date("m/d/y");
?>

Then you have the date and time in the format that you want it displayed and can send the variable with your query to have it saved in the database.

Now at the time of this posting the value of $date_time would be "03/13/12" which doesn't show the name of the day of the week or the time but you can use either of the function links we've given you to format it how you want.

To test the format you can make your script print out the value of $date_time without saving it to the database so you can see how the format would actually look. And then you can change the parameters of 'date' to get it to look how you want.

Member Avatar for rajeevphp2011

echo date('d-m-Y', strtotime($date));

for change date format in php

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.