CODEWITHSUNDEEP
phpmysqlopencartopencart-3
abdul majeed
abdul majeed

Reputation: 11

Opencart 3 products not fetch data mysql query not showing products

I have this query but it's not showing any data, I'm getting blank results:

SELECT * FROM oc_product p
LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id)
WHERE pd.language_id = '1'
GROUP BY p.product_id
ORDER BY pd.name ASC

I think the problem is in the oc_product_description table (field descriptions).

I try also SELECT * FROM oc_product This working fine, but SELECT * FROM oc_product_description this not working , SELECT name FROM oc_product_description this working fine ...

This is my full code

<?php
// DB
define('DB_DRIVER', 'mysqli');
define('DB_HOSTNAME', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_DATABASE', 'opencart');
define('DB_PORT', '3306');
define('DB_PREFIX', 'oc_');


$db = mysqli_connect(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
if (mysqli_connect_errno()) {
  echo 'Database connection failed with following errors: ' . mysqli_connect_error();
  die();
}


$products = [];
//$sql = $db->query("SELECT * FROM ". DB_PREFIX ."product p LEFT JOIN " . DB_PREFIX . "product_description pd ON pd.product_id = p.product_id WHERE pd.language_id = 1");

$sql = $db->query("SELECT * FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) WHERE pd.language_id = '1' GROUP BY p.product_id ORDER BY pd.name ASC");

while ($row = mysqli_fetch_assoc($sql)) {
  $products[] = $row;
}

echo json_encode($products);

?>

please help me anyone...thank you

Upvotes: 1

Views: 597

Answers (1)

Alberto Martinez
Alberto Martinez

Reputation: 2670

The problem seems to be that you either don't have any matching row for that products in oc_product_description or that the descriptions you have in that table for those products have a value in language_id different of 1.

Although you are using LEFT JOIN for joining the description your WHERE condition is using a column of the table oc_product_description, which is the equivalent of converting it to an INNER JOIN, because the results for all the products without a matching description will have a NULL value for language_id, so the condition WHERE pd.language_id = 1 would be always false.

Check the contents of your description table, also you can rewrite the query in this way to return results also for products without description:

SELECT p.*, IFNULL(pd.name,'(no description found)') as name
FROM oc_product p
LEFT JOIN oc_product_description pd ON p.product_id = pd.product_id AND pd.language_id = 1
GROUP BY p.product_id
ORDER BY pd.name ASC

This works because we moved the condition to the LEFT JOIN so if the condition fails it only affects the join.

Note that if the products table has a column with a default description you could use that column instead of '(no description found)'.

Upvotes: 1

Related Questions