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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.