CODEWITHSUNDEEP
phpmysqlcodeigniter
Snm
Snm

Reputation: 435

Mysql query to fetch search results by combining multiple tables with a mapped table

I have four tables namely companies,companies_branch,company_category_map and company_category. enter image description here Here i can add a company head office to the companies table and if there is branches for the company in same country or different country, the branch will be added to the companies_branches table with the company head office id as the foriegn key. Since the main company and branches have multiple categories, the categories are mapped to company_category_map table with the respective ids of each master tables.

When i just search with the companies table without the branch table joined everything works fine.

The search is as follows. 1. Search with only company name 2. Search with only category 3. Search with only country 4. Search with any combination of the above 3

what i need is

I should be able to display the all the company names from the companies table if the country or category falls in either of the table and the company name should not repeat.

Any ideas or help would be highly appreciated. I afraid i can't alter the table structure since there are thousands of records already inserted. But still if there is a need to alter the table i can give it a try without losing the data.

what i have tried without joining the company_branches table is as follows.

<?php 
function search_companies($limit,$start,$companyname='',$category_id='',$country_id='') 
    {
        if($category)
        {
            $qry = "SELECT `companies`.* FROM `companies` INNER JOIN `company_category_map` ON (`companies`.`id` = `company_category_map`.`company_id`) INNER JOIN `company_category` ON (`company_category_map`.`category_id` = `company_category`.`id`) WHERE `companies`.`status` =1";
            $where = " and `company_category_map`.`category_id` = $category";
            if($companyname) $where .= ' and `companies`.`company_name` like "%'.$companyname.'%"';
            if($country_id) $where = " and `companies`.`country_id` = $country_id";
        }
        else
        {
            $qry = "SELECT * FROM `companies` WHERE `status` = 1";
            if($companyname) $where .= " AND `heading` LIKE '%".$companyname."%'";
            if($country_id) $where .= " AND `country_id` = $country_id";
        }

        $qry = $qry.$where." limit  $start, $limit";

        $query = $this->db->query($qry)->result();

        if (count($query) > 0) {
            foreach ($query as $row) {
                $data[] = $row;
            }
            return $data;
        }
        return false;
   }
?>

Upvotes: 0

Views: 255

Answers (1)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

In the table company_category_map, there is a column type. Is this used to determine if the row matches a company versus branch record?

If so, the join on company_category_map would need to be changed a bit.

This SQL doesn't handle the other searches, but they would be easily added to the where clause. The point is to show how to match the category on either a main office or branch.

UPDATE: Added check for empty category to eliminate rows that do not have a matching category.

UPDATE: Added test for type of company (main or branch) to join on company_category_map.

SELECT 
    a.`id`,
    a.`country_id`,
    a.`company_name`,
    a.`logo`,
    a.`address`,
    a.`phone`
FROM `companies` a
LEFT JOIN `companies_branches` a1
ON a1.`company_id` = a.`id`
LEFT JOIN `company_category_map` b
ON  (a.`id` = b.`company_id` AND b.`type` = 'm') OR 
    (a1.`id` = b.`company_id` AND b.`type` = 'b') AND 
    `b.status` = 1
LEFT JOIN `company_category` c
ON b.`category_id` = c.`id`
WHERE c.`id` = @category_id AND
    (NOT c.`id` IS NULL)
GROUP BY a.`id`;

Be sure to convert your code to use prepared statements with either mysqli or PDO.

Upvotes: 1

Related Questions