Query Error

[B. Ellis]

I made a chart with the wizard, uploaded it to my site, looked at the page and it just shows a chart with not data. I opened the data1.php file and took a look at the query. When I tried to test the query it gave me an error. I then loaded up the db in Navicate and tried to run the query against the db, again and error for the same thing DW was giving me an error. I don't see anything obvious as to why this is throwing an error ....


SELECT CONCAT(LEFT(MONTHNAME(creationDate), 3), ' ', DAY(creationDate), ',', YEAR(creationDate)) AS XLABEL, SUM(amountFound) AS YVALUE FROM tombsdb GROUP BY CONCAT(LEFT(MONTHNAME(creationDate), 3), ' ', DAY(creationDate), ',', YEAR(creationDate)), DAYOFWEEK(creationDate) ORDER BY creationDate ASC, DAYOFWEEK(creationDate) ASC


Any ideas as to what might be causing this error?

1064-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 '(creationDate), ',', YEAR(creationDate)) AS XLABEL, SUM(amountFound)

[Ray Borduin]

What is the exact error?

I can't spot the problem.

[B. Ellis]

1064-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 '(creationDate), ',', YEAR(creationDate)) AS XLABEL, SUM(amountFound)

[Ray Borduin]

I copied and pasted that SQL statment and only changed table and column names and it works.

What version of mySQL are you running? What is the data type of creationDate adn amountFound? do either allow null? Do you get the error when you remove the SUM(amountFound) line?

[B. Ellis]

I believe the SQL version is 4.1.

AmountFound is an integer, can be null

creationDate is a timestamp, can be null, but it auto-enters the creation timestamp

I will test removing the Sum(amountFound) and see what happens

[B. Ellis]

Fixed, but not really :)

I copied the hosted database to my local machine, which is running Wamp 2.0 with MySQL 5, and the query worked fine. So I created a new database with 1and1 http://www.webassist.com/go/signup/1and1, only this time with MySQL 5 instead of MySQL 4, and it works fine. Not too sure why it wouldn't work with 4, maybe some syntax change or something? I guess that brings me to the question, when Webassist designs their products, are they designed around Mysql 5 or 4?

Thank you for the help and insight!
Bob

[Ray Borduin]

Usually we intend to work on both MySQL 4 and MySQL 5 and test on both. If we do have something that only works on MySQL 5 it would be listed in the product requirements section of the details page on our web site for that product.