CODEWITHSUNDEEP
phpmysqlarrays
Sanjeev Shrestha
Sanjeev Shrestha

Reputation: 27

How to add values of multiple table in one array?

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

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

$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

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.

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

$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.

Any ideas would be appreciated.

Thank you

Upvotes: 0

Views: 1191

Answers (5)

Balaji
Balaji

Reputation: 10967

For together two different tables USING(UNION AND JOIN)

1.Union

SELECT columnlist
FROM   table1
UNION
SELECT columnlist
FROM   table2

2.JOIN

SELECT * FROM TABLE1
        LEFT JOIN TABLE2  ON TABLE1.category_id = TABLE2.category_id"

Upvotes: 0

Zafar Malik
Zafar Malik

Reputation: 6854

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:

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

$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.

$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";

Upvotes: 0

GautamD31
GautamD31

Reputation: 28753

You need to GROUP BY your category_id in your query like

$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";

Upvotes: 1

Ja͢ck
Ja͢ck

Reputation: 173642

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)

Upvotes: 0

xdazz
xdazz

Reputation: 160883

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";

Upvotes: 0

Related Questions