MySQL join problem

Question

I want to join a pages table and menu table.

CREATE TABLE IF NOT EXISTS `pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `keywords` varchar(255) NOT NULL DEFAULT '',
  `description` varchar(255) NOT NULL DEFAULT '',
  `path` varchar(255) NOT NULL DEFAULT '',
  `content` text NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;



CREATE TABLE IF NOT EXISTS `menus` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `shortdesc` varchar(255) NOT NULL,
  `page_id` varchar(60) NOT NULL,
  `status` enum('active','inactive') NOT NULL,
  `parentid` int(11) NOT NULL,
  `order` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=79 ;

I have errors with the following SQL.

function generateTree(&$tree, $parentid = 0) {
$res = $this->db->query('SELECT M.*, P.name AS PageName
    WHERE M.parentid = $parentid
    ORDER BY M.order asc, M.parentid asc
    FROM menus AS M
    LEFT JOIN pages AS P
    ON P.id = M.page_id');
...
...

Can you tell what I am doing wrong?

Thanks in advance.

Answer 1

You've got your SQL syntax mixed up

$res = $this->db->query('
SELECT 
 M.*, P.name AS PageName
FROM
 menus AS M 
 LEFT JOIN pages AS P ON P.id = M.page_id
WHERE 
 M.parentid = $parentid
ORDER BY 
 M.order asc, M.parentid asc
');

BTW, you should bot be using variables in the SQL string. Use parameterized queries instead (mysyqli*).