phpmysqlcodeigniterjoinactiverecord

How to correctly write a JOIN query with CodeIgniter's active record?

The following line is giving an error message.

$query = 'SELECT * FROM products AS p LEFT JOIN categories AS c USING ON c.id = p.category_id WHERE c.name = "Galleri1"
 AND p.status = "active"' ;
 $Q = $this->db->query($query);

Database structure:

CATEGORIES

 CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `shortdesc` varchar(255) NOT NULL,
  `longdesc` text NOT NULL,
  `status` enum('active','inactive') NOT NULL,
  `parentid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;
...
...

PRODUCT

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `shortdesc` varchar(255) NOT NULL,
  `longdesc` text NOT NULL,
  `thumbnail` varchar(255) NOT NULL,
  `image` varchar(255) NOT NULL,
  `class` varchar(255) DEFAULT NULL,
  `grouping` varchar(16) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL,
  `category_id` int(11) NOT NULL,
  `featured` enum('true','false') NOT NULL,
  `price` float(4,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

Error message

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 'ON c.id = p.category_id WHERE c.name = "Galleri1" AND p.status = "active"' at line 1

SELECT * FROM products AS p LEFT JOIN categories AS c USING ON c.id = p.category_id WHERE c.name = "Galleri1" AND p.status = "active"

Can anyone tell me how to fix my query and write it in CodeIgniter's Active Record?

Solution

  • Use single quotes, get rid of USING and make the JOIN inner:

    SELECT  *
FROM    products AS p
JOIN    categories AS c
ON      c.id = p.category_id
WHERE   c.name = 'Galleri1'
        AND p.status = 'active'