SQL statement - driving me nuts!

MrBiscuits

Hi,

PHP
MySQL
IIS

I have a PHP page that connects to a MySQL database.

I get the following error when I view the page in my browser:

"You have an error in your SQL syntax near '(LINKS.ID) AS CountOfLinks, link_category.cat_name FROM LINKS, link_category G' at line 1"

My SQL statement is not exactly complicated, and is:

SELECT COUNT (LINKS.ID) AS CountOfLinks, link_category.cat_name
FROM LINKS, link_category GROUP BY link_category.cat_name

Can anyone see the problem? I am new to PHP/MySQL, and used to program with VBScript/Access so perhaps MySQL is a bit different...
Thanks

mooseday

Runs ok for me on POSTGRESQL.

I would suggest putting a join in though as the current code doesn't return the proper results ( assume link_category is a table of names referenced by links.id )

SELECT COUNT(links.id) AS CountOfLinks, link_category.cat_name FROM links join link_category on (links.id = link_category.id) GROUP BY link_category.cat_name

OctoberAsh

...dropping the GROUP BY from your SQL. Since you're counting the links.id as a function of link_category.cat_name, the query will naturally group by cat_name anyway. I think the MSAccess datagrid has you select "Group By" in queries such as this, but they're really not needed in SQL.

The result will be the same, but dropping the GROUP BY simplifies your SQL, thus making it easier to debug in future.

Good luck!

MrBiscuits

😃

Thanks for looking at this.

My original statement was actually more complex and had a join in it, but because it didn;t work I simplified the SQL.

Now I have put the JOIN back in it now works!!??

My machine is doing seriously weird things....

The end result is that thanks to you pointing me in the direction it now works, anyway!

cheers!