CODEWITHSUNDEEP
phpmysqlopencart
John Magnolia
John Magnolia

Reputation: 16793

Modify SQL to include extra table

How would I get the banner name? If you look at the DB below you will see that this bring back everything apart from the actual banner.name?

Also I presume that it should check that the banner status to check it is enabled.

BEFORE:

SELECT * 
FROM banner_image bi 
LEFT JOIN banner_image_description bid ON (bi.banner_image_id  = bid.banner_image_id) 
WHERE
   bi.banner_id = '".$banner_id."' 
   AND bid.language_id = '".$this->config->get('config_language_id')."'

Array ( 
    [0] => Array ( 
        [banner_image_id] => 1 
        [banner_id] => 1 
        [link] =>
        [image] => data/banners/test.jpg 
        [language_id] => 1 
        [title] => Test banner 
    ) 
)

AFTER:

SELECT
   bi.*,
   b.name 
FROM
   banner b,
   banner_image bi 
LEFT JOIN banner_image_description bid ON (bi.banner_image_id  = bid.banner_image_id) 
WHERE
   b.banner_id = '".$banner_id."' 
   AND bi.banner_id = '".$banner_id."' 
   AND bid.language_id = '".$this->config->get('config_language_id')."'

Array ( 
    [0] => Array ( 
        [banner_image_id] => 1 
        [banner_id] => 1 
        [link] => 
        [image] => data/banners/test.jpg 
        [name] => Banner heading 
    ) 
)

DB Structure:

CREATE TABLE IF NOT EXISTS `banner` (
  `banner_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8_bin NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`banner_id`)
);

CREATE TABLE IF NOT EXISTS `banner_image` (
  `banner_image_id` int(11) NOT NULL AUTO_INCREMENT,
  `banner_id` int(11) NOT NULL,
  `link` varchar(255) COLLATE utf8_bin NOT NULL,
  `image` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`banner_image_id`)
);

CREATE TABLE IF NOT EXISTS `banner_image_description` (
  `banner_image_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `banner_id` int(11) NOT NULL,
  `title` varchar(64) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`banner_image_id`,`language_id`)
);

Upvotes: 0

Views: 130

Answers (1)

Travesty3
Travesty3

Reputation: 14469

I think this will do what you want:

SELECT *
FROM
    banner b
    INNER JOIN banner_image bi ON b.banner_id = bi.banner_id
    INNER JOIN banner_image_description bid ON bi.banner_image_id = bid.banner_image_id
WHERE
    b.banner_id = '". $banner_id ."'
    AND b.status = TRUE
    AND bid.language_id = '". $this->config->get('config_language_id') ."'

I would avoid using SELECT * and instead, explicitly list out each column you actually want to fetch.

One reason I think you were having trouble is that you used a comma (implicit join) to join in the banner table, but you didn't specify a join condition. You would have needed a condition in your WHERE clause like b.banner_id = bi.banner_id. But it would be better to use explicit INNER JOIN syntax.

I don't see a reason for using a LEFT JOIN instead of an INNER JOIN in this query. In the WHERE clause, you specify a condition that must be met in the banner_image_description table in order for a row to be returned. If there is no corresponding row in that table (which is the purpose of a LEFT JOIN), then there will be no row returned. So I switched them to INNER JOIN.

Upvotes: 1

Related Questions