CODEWITHSUNDEEP
mysqlpdo
Iva Kobalava
Iva Kobalava

Reputation: 129

How Left join two tables mysql

i have Three tables in database

Category: id, category

Sub_category: id, category(category.id), sub_category

Product: id, p_name, country, category(category.id), sub_category(sub_category.id)

when I execute this query sub_category is empty , but when I delete LEFT JOIN cat ON product.category = cat.id category is empty and sub_category is filled correctly

on my xamp localhost it work correctly but when i upload it on server it has the error above

SELECT * FROM product 
LEFT JOIN subcat ON product.sub_category = subcat.id 
LEFT JOIN cat ON product.category = cat.id 
WHERE product.id = $id


array(1) {
  [0]=>
  array(16) {
    ["id"]=>
    string(1) "1"
    ["p_name"]=>
    string(80) "Agusha snak"
    ["country"]=>
    string(24) "croatia"
    ["category"]=>
    string(37) "baby feed"
    ["sub_category"]=>
    string(0) ""
  }
}

Upvotes: 0

Views: 31

Answers (1)

Barmar
Barmar

Reputation: 781131

The problem is that you have duplicate column names in the tables. The associative array returned by fetch() will only contain the last column with the same name.

Use aliases to distinguish them.

SELECT p.*, c.category AS category_name, s.sub_category AS subcategory_name
FROM product AS p
LEFT JOIN subcat AS s ON p.sub_category = s.id 
LEFT JOIN cat AS c ON p.category = c.id 
WHERE p.id = $id

Also, you shouldn't substitute variables into the query, use a prepared statement and bindParam() to protect against SQL Injection.

Upvotes: 2

Related Questions