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

Question

I have four tables namely companies,companies_branch,company_category_map and company_category. 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;
   }
?>

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Jul 12 '17 at 16:03
I second the comment by @JayBlanchard. Use prepared queries to avoid SQL Injection attacks. That said, to clarify what you want is to be able to show a list of companies where either the main office or any branch is associated with a specific category, correct? — Sloan Thrasher, Jul 12 '17 at 16:09
FWIW, I find this bit a little odd `category_id = $category` - it seems unlikely that $category would be an id (but certainly not impossible) — Strawberry, Jul 12 '17 at 17:07

Sloan Thrasher · Accepted Answer · 2017-07-13T06:18:33.460

1

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.

edited Jul 13 '17 at 06:18

answered Jul 12 '17 at 16:19

Sloan Thrasher

4,953
3
22
40

Ya the type is used to determine if the row matches a company versus branch record. And im using codeigniter and it will be converted to prepared statements. I will check the query and if the query matches my need, defenitely i will mark this as answer. Thanks in advance – Snm Jul 12 '17 at 16:25
Hi Sloan Thrasher, I tried the query that you have provided. But somewhere there is a mistake. The query is returning 3 records when i tried with a category id instead of 2. The problem is when a branch is added the id from the table company_branches is added to the company_id in mapped table with type as 'b'. please check this image https://ibb.co/f5rf2v – Snm Jul 12 '17 at 17:37
Not sure without seeing the query, but if you used it as is, the change above should fix it. Also, as I mention in the answer there should be a way to tell from the company_category_map table if the company_id refers to the companies table or the company_branches table. If that's the case, how is that done. – Sloan Thrasher Jul 12 '17 at 22:50
Hi Sloan Thrasher, thanks for your response.As mentioned in the above link ( ibb.co/f5rf2v ) you can see that company_category_map have a field called type (which will be either 'm' - main or 'b' - branch). I have used your query as it is only by changing the @category_id to 17.Also i tried with the edited query, but the result is same.Thanks – Snm Jul 13 '17 at 05:50
Seems like I asked about that above in the comments, and mentioned something about that in the answer. Would have been nice to know. I'll update the query above to take the type column into account. – Sloan Thrasher Jul 13 '17 at 06:13
Note the change in the SQL in the answer above. – Sloan Thrasher Jul 13 '17 at 06:17
Wowwwww...Thanks that's what i was looking for. you saved my day. And 1 more question, will there be a problem when i pass the country id in the query string, coz in both table (companies,companies_branches) it deals with same country id or sometimes different country id. – Snm Jul 13 '17 at 06:35
Shouldn't be a problem, just remember to compare to both tables (ie. a and a1) in the where clause. Same thing for other columns in the company or branches tables when used in the where. – Sloan Thrasher Jul 13 '17 at 07:00

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

1 Answers1