A Database Error Occurred Error Number 1064

Question

Error Number: 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 'groups ON groups.id = user_group.group_id WHERE user_group.user_id = '1'' at line 2

SELECT * 
FROM user_group 
    INNER JOIN groups ON groups.id = user_group.group_id 
WHERE user_group.user_id = '1'

Filename: models/Model_groups.php

Line Number: 54

From the manual GROUPS (Reserved word); added in 8.0.2 (reserved) so it must be wrapped in backticks if you really must use a reserved word as a table or any other name — RiggsFolly, Jan 14 '21 at 15:55

score 1 · Accepted Answer · answered Jan 14 '21 at 15:52

This query raises a number of questions but, sticking to the point, it would be good practice to wrap tables and columns with the ` character:

SELECT * 
  FROM `user_group` INNER JOIN `groups` ON `groups`.`id` = `user_group`.`group_id`
 WHERE `user_group`.`user_id` = '1';

You could also make this easier to type with aliases:

SELECT * 
  FROM `user_group` ug INNER JOIN `groups` grp ON grp.`id` = ug.`group_id`
 WHERE ug.`user_id` = '1';

The syntax for both of these queries is valid

A Database Error Occurred Error Number 1064

1 Answers1