如何在一个数组中添加多个表的值？

I am using opencart database to make a simple product app which shows product with it's category.

我正在使用opencart数据库制作一个简单的产品应用程序，显示带有它的产品类别。

here i got the problem i already made an array by looping the values to an array now the database contains 3 or more tables which contains category_id in common 1 contains id with image link another contains name. these two are important to me. here is code to understand

这里我得到的问题我已经通过将值循环到一个数组已经创建了一个数组现在数据库包含3个或更多的表，其中包含class_id共同1包含id与图像链接另一个包含名称。这两个对我很重要。这是要理解的代码

$sql = "SELECT * FROM oc_category";
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
    $categories[] =$row;
}

this code makes an array which contains category_id, category_image which i can further use by

这段代码创建了一个包含category_id，category_image的数组，我可以进一步使用它

foreach ( $categories as $category){
    echo "the category id is $category['category_id']";
}

now the another table is oc_category_description which contains name of category. how can i make it to be in one array so i can use $category['name']; to echo it.

现在另一个表是oc_category_description，其中包含类别名称。我怎么能让它在一个数组中，所以我可以使用$ category ['name'];回应它。

i already tried many ways among which i queried 2 tables in one

我已经尝试了很多方法，其中我在一个中查询了2个表

$sql = "SELECT * FROM oc_category, oc_category_description";
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
    $categories[] =$row;
}

this method worked but it slowed the app and shown one result for four times

这种方法有效，但它减慢了应用程序并显示了四次结果

here is what i think every table has category_id which is common in every table i would like to use it to add name and other description from oc_category_description.

这是我认为每个表都有category_id，这在每个表中都很常见，我想用它来添加oc_category_description中的名称和其他描述。

Any ideas would be appreciated.

任何想法，将不胜感激。

Thank you

谢谢

4 个解决方案

#1

You need to GROUP BY your category_id in your query like

您需要在查询中对您的category_id进行GROUP BY

$sql = "SELECT * FROM oc_category, oc_category_description 
        GROUP BY oc_category.category_id";

Or even you can JOIN them like

甚至你可以像他们一样加入他们

$sql = "SELECT * FROM oc_category
        LEFT JOIN oc_category_description 
        ON oc_category.category_id = oc_category_description.category_id 
        GROUP BY oc_category.category_id";

#2

You have to join the table to get the info:

您必须加入表格才能获得信息：

$sql = "SELECT * FROM oc_category a 
        LEFT JOIN oc_category_description b ON a.category_id = b.category_id";

#3

You have to add a join condition between those two tables lest you end up with a Cartesian product:

您必须在这两个表之间添加连接条件，以免最终得到笛卡尔积：

SELECT oc_category.*, oc_category_description.name
FROM oc_category
jOIN oc_category_description USING (category_id)

#4

You are using 2 tables even without telling to mysql how to join them, due to this reason you are getting slowness. You can use below 2 approaches:

您正在使用2个表，即使没有告诉mysql如何加入它们，由于这个原因您变得缓慢。您可以使用以下2种方法：

First Medhod: If it is confirm that oc_category_description table have corresponding category_id against oc_category.

First Medhod：如果确认oc_category_description表对oc_category具有相应的category_id。

$sql = "SELECT oc.column1,oc.column2,des.column1 FROM oc_category cat, oc_category_description des where cat.category_id = des.category_id";

Second Medhod: If some time oc_category_description table have corresponding category_id against oc_category and some time not and you want all rows from master table like oc_category.

第二个Medhod：如果某个时候oc_category_description表对oc_category有相应的category_id，而有些时候没有，你想要主表中的所有行都像oc_category。

$sql = "SELECT oc.column1,oc.column2,des.column1 FROM oc_category cat left join oc_category_description des on cat.category_id = des.category_id";

#1