php date problem

Hi all,
I am trying to create an archive system for a news section on a website.

I have news.php which sends a value via a variable archiveDate to a page called archive_news.php

 
<?php do { ?>
          <a href="archive_news.php?archiveDate=<?php echo $row_archive['formatted_date']; ?>"><?php echo $row_archive['formatted_date']; ?></a><br />
        <?php } while ($row_archive = mysql_fetch_assoc($archive)); ?>

Open in new window


And the sql on the archive_news.php:
 
$archiveDate = mysql_real_escape_string($_REQUEST['archiveDate']);
mysql_select_db($database_db, $db);
$query_newsItem = "SELECT *, date_format(news_date,'%M %Y') as myDate FROM news WHERE news_date = $archiveDate";
$newsItem = mysql_query($query_newsItem, $db) or die(mysql_error());
$row_newsItem = mysql_fetch_assoc($newsItem);
$totalRows_newsItem = mysql_num_rows($newsItem);

Open in new window


I keep getting SQL error:
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 '2011' at line 1

The value that it takes is 'March 2011' as I am formatting the date in the SQL query
LVL 4
cataleptic_stateAsked:
Who is Participating?
 
Erdinç Güngör ÇorbacıPHP Development Team LeaderCommented:
Just change third line from

$query_newsItem = "SELECT *, date_format(news_date,'%M %Y') as myDate FROM news WHERE news_date = $archiveDate";

to

$query_newsItem = "SELECT *, date_format(news_date,'%M %Y') AS myDate FROM news WHERE DATE_FORMAT(news_date,'%M %Y')='".$archiveDate."';";
0
 
Lukasz ChmielewskiCommented:
The default date format is yyyy-mm-dd so you need to modify your query to take that format as an argument here:
WHERE news_date = $archiveDate
0
 
cataleptic_stateAuthor Commented:
but I want to get the month and year only not the day
0
 
Ray PaseurCommented:
Here is an article that will tell you everything you need to know about how to handle dates in PHP and MySQL.  Please read it carefully and post back with any specific questions.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

Executive summary: Keep your internal dates in ISO-8601 format.  Reformat your "display dates" to anything you want using a combination of strtotime() and date().  The formatting guidelines are documented on the PHP web site here:
http://php.net/manual/en/function.date.php
0
 
cataleptic_stateAuthor Commented:
Hi erdincgc,
That works, I thought that may not have worked, but it did, I had to change your query slightly

SELECT *, date_format(news_date,'%M %Y') as myDate FROM news WHERE DATE_FORMAT(news_date,'%M %Y')='".$archiveDate."'"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.