CODEWITHSUNDEEP
phpmysqlsqlleft-joinopencart
Karuppiah RK
Karuppiah RK

Reputation: 3964

opencart 1.4.9.3 mysql unknown column error

I'm using opencart 1.4.9.3 version. I have downloaded the banner manager from here http://www.opencart.com/index.php?route=extension/extension/info&extension_id=4886 . I follwed that installation steps.

When I click EXTENSIONS > BANNERS it returns this error 

Error: Unknown column 'jo_bts.store_id' in 'field list'
Error No: 1054
SELECT jo_bts.store_id, s.name FROM jo_banner_to_store bts LEFT JOIN jo_store s ON s.store_id = bts.store_id WHERE banner_id = 1 ORDER BY store_id

But In my database table I have store_id column

jo_banner table

enter image description here

jo_banner_to_store table

enter image description here

public function getBannerStores($banner_id) {
        $query = $this->db->query("SELECT " . DB_PREFIX . "bts.store_id, s.name FROM " . DB_PREFIX . "banner_to_store bts LEFT JOIN " . DB_PREFIX . "store s ON s.store_id = bts.store_id WHERE banner_id = " . (int) $banner_id . " ORDER BY store_id");
        return $query->rows;
    }

Upvotes: 1

Views: 329

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

According to the given query you are using undefined alias jo_bts and i guess where the alias is defined the creator of has not used the table prefix,change bts to " . DB_PREFIX . "bts

public function getBannerStores($banner_id) {
        $query = $this->db->query("SELECT " . DB_PREFIX . "bts.store_id, s.name 
        FROM " . DB_PREFIX . "banner_to_store " . DB_PREFIX . "bts 
        LEFT JOIN " . DB_PREFIX . "store s 
         ON s.store_id = " . DB_PREFIX . "bts.store_id WHERE banner_id = " . (int) $banner_id . "
         ORDER BY store_id");
        return $query->rows;
    }

So final query will look like 

SELECT 
  jo_bts.store_id,
  s.name 
FROM
  jo_banner_to_store jo_bts 
  LEFT JOIN jo_store s 
    ON s.store_id = jo_bts.store_id 
WHERE banner_id = 1 
ORDER BY store_id

Upvotes: 1

Related Questions