[SOLVED] What is wrong with this query??

[studgate]

I keep getting error message for this query:

 

SELECT COUNT(blogid) total, blogid, MONTHNAME(date) month, YEAR(date) year, date_format(date, '%m/%Y') dated FROM blog WHERE dated = $month

 

can anybody assist me in figure out what is the problem, please?

 

Explanation: I am trying to get archives for a little blog system that I am building, I want to check for a month like 01/2009 and find the post entries for that month.

 

thanks in advance!

[rhodesa]

#1) This should be in the MYSQL forum

#2) use mysql_error() next time to get the error, and show that error with your post next time

 

I think the problem is you need single quotes around $month:

SELECT COUNT(blogid) total, blogid, MONTHNAME(date) month, YEAR(date) year, date_format(date, '%m/%Y') dated FROM blog WHERE dated = $month

 

The problem probably may also stem from using 'date' as a column name. this is a reserved word in MySQL. i would recommend changing the name of that column. here is a list of reserved words: http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

 

if you post the output of mysql_error() it will be a lot easier to diagnose the problem

 

 

[studgate]

Thanks rhodesa, but that's not the problem, the

date I am referring to in the query is date_posted

and added quotes to $month without success,

 

I keep getting an error and it seems that the result has wrong argument.

 

"SELECT COUNT(blogid) total, blogid, MONTHNAME(posted_date) month, YEAR(posted_date) year, date_format(posted_date, '%m/%Y') dated FROM blog WHERE dated = '$month' GROUP BY MONTH(posted_date), YEAR(posted_date)"

[rhodesa]

you code probably looks something like:

 

$sql = "SELECT COUNT(blogid) total, blogid, MONTHNAME(posted_date) month, YEAR(posted_date) year, date_format(posted_date, '%m/%Y') dated FROM blog WHERE dated = '$month' GROUP BY MONTH(posted_date), YEAR(posted_date)";
$result = mysql_query($sql);

add some error reporting so it looks like this:

$sql = "SELECT COUNT(blogid) total, blogid, MONTHNAME(posted_date) month, YEAR(posted_date) year, date_format(posted_date, '%m/%Y') dated FROM blog WHERE dated = '$month' GROUP BY MONTH(posted_date), YEAR(posted_date)";
$result = mysql_query($sql) or die("Query Error: ".mysql_error());

and post what the error is

 

[studgate]

I figured it out, there was a problem with the WHERE statement,

apparently you can't use aliases in the where statement and that's why i kept getting

error messages.

 

Here is the correct query:

SELECT MONTHNAME(posted_date) as month, blogid, YEAR(posted_date) as year FROM blog WHERE date_format(posted_date, '%m/%Y') = '$month'

 

Thanks to all!