Chapter 16 forum - SQL error

[petefernando]

Hi

I've got a copy of Beginning PHP5, Apache, and MySQL Web Development.

I've been working through Chapter 16 and get this error when loading the index page:

--------begin cut---------
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 'mod FROM forum_forum f LEFT JOIN forum_posts p ON f.id = p.forum_id AND ' at line 3
-------end cut--------

Here is the offending bit of code:


-------Begin cut--------
$sql = <<<EOS
  SELECT f.id as id, f.forum_name as forum,
    f.forum_desc as description,
    count(forum_id) as threads, u.name as mod
  FROM forum_forum f
  LEFT JOIN forum_posts p
  ON f.id = p.forum_id
  AND p.topic_id=0
  LEFT JOIN forum_users u
  ON f.forum_moderator = u.id
  GROUP BY f.id,
EOS;
--------end cut----------

Somone on another forum suggested I added "f.forum_name, f.forum_desc, u.name" on the end of the group by statement. I've tried this and it doesn't work.

Can anyone help????

Cheers
Pete Fernando

[wildteen88]

chnage u.name as mod to u.name as `mod`. The word mod is a MySQL reserved word and so MySQL gets confused. When you place blackticks or quotes around it, it tells MySQL you are not using a MySQL reserved word

[shek_lko]

Don't Use mod Because it's a mysql built in function,
another thing, U r using aggregate function count
therefore other colum should be in group by clause
your group by should look like this and should use id in place of f.id
and so on.
GROUP BY id,forum,description