How to display value from 3 mysql tables

Question

I have to display values from three mysql tables(package,supplier,requests).

table :package

Package table

Table : Supplier

Table : supplier

Table: requests

i want to display final result like this

Final Result

this is the query what i have tryed.

$query= "SELECT package.p_name, supplier.cat, supplier.cat_action,requests.cat_action, package.cost_for_cat, package.leads_per_month, package.package_price 
 FROM package
 LEFT JOIN supplier
 ON package.category = supplier.cat
 AND supplier.email='".$_SESSION["mail"]."'
 LEFT JOIN requests
 ON package.package_id = requests.package_id
 GROUP BY requests.package_id
 ORDER BY package.package_id DESC
"; 

            $result= mysql_query($query);
            $sum = 0;
        while($row = mysql_fetch_assoc($result))
            {
            echo '<tr >';
                    echo '<td>'.$row['p_name'].'</td>';
                    echo '<td>'.$row['cat'].'</td>';
                     echo '<td>'.'<I>'.$row['cat_action'].'</I>'.'</td>';
                    echo '<td>'.'£ '.$row['cost_for_cat'].'</td>';
                    echo '<td>'.$row['leads_per_month'].'</td>';
                    echo '<td style="text-align:right;">'.'<label id="pprice">'.'£ '.$row['package_price'].'</label>'.'</td>';

                echo '</tr>';

                echo '<tr>';
                    echo '<td>&nbsp;</td>';
                    echo '<td>&nbsp;</td>';
                     echo '<td>&nbsp;</td>';
                    echo '<td>&nbsp;</td>';
                    echo '<td>TOTAL</td>';
                    echo '<td style="text-align:right;">'.'£ '.'<label id="pprice">'.$sum += $row['package_price'].'</label>'.'</td>';

                echo '</tr>';       
            }

but it's display result like this

My Result

possible duplicate of [Displaying Data From Multiple MySQL Tables](http://stackoverflow.com/questions/3989119/displaying-data-from-multiple-mysql-tables) — Mike M., Dec 22 '14 at 05:15
@Sean I've edit my question.my query display result like last img. — KT1, Dec 22 '14 at 05:15
You have your total row inside your `while($row = mysql_fetch_assoc($result))` loop. Put it after — Sean, Dec 22 '14 at 05:15
As your 2 joined tables have the same column names, your values will be empty if the second column name (`cat`, `cat_action`) is null — Sean, Dec 22 '14 at 05:18
Also: http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Mike M., Dec 22 '14 at 05:20
i want to echo cat_action and cat from both tables.that's why i have use same column names — KT1, Dec 22 '14 at 05:24
its okay to have the same column names, but you need to specify which one when displaying. Same idea if you have 2 people in the room with the same name, and when you call their name, how do you specify which individual you are actually calling. — Sean, Dec 22 '14 at 05:32
@Sean Total row issue fixed.thanks.i've got your explanation.but i'm stuck on there.i hav checked email like this LEFT JOIN requests ON package.package_id = requests.package_id AND requests.email='".$_SESSION["mail"]."' .then it's filled all the fields in second row.now first row (Category ,package status) empty. — KT1, Dec 22 '14 at 06:09
Try using [`IFNULL()`](http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull) in your query on `supplier.cat`/`requests.cat` AND `supplier.cat_action`/`requests.cat_action` -> `SELECT package.p_name, IFNULL(supplier.cat,requests.cat) as cat, IFNULL(supplier.cat_action, requests.cat_action) as cat_action, package.cost_for_cat, package.leads_per_month, package.package_price FROM package ...rest of your query...` — Sean, Dec 22 '14 at 06:22

Debug Diva · Answer 1 · 2014-12-22T06:23:02.913

0

Try this it will work :

SQL Query :

SELECT t1.`p_name`,t1.`category`,t2.`cat_action`,t1.`cost_for_cat`,t1.`leads_per_month`,t1.`package_price` FROM package t1
JOIN requests t2 ON t2.`package_id`=t1.`package_id`
JOIN Supplier t3 ON t3.`supplier_id`=t2.`supplier_id`

edited Dec 22 '14 at 06:23

answered Dec 22 '14 at 05:53

Debug Diva

26,058
13
70
123

not working.Display error message 'Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\wamp\www.....' – KT1 Dec 22 '14 at 06:01
@KT1, Please make ensure that table names are correct given by you. I edited my answer please check now. – Debug Diva Dec 22 '14 at 06:23

Faiyaz Alam · Answer 2 · 2014-12-24T06:11:24.597

0

hope this will help

SELECT *
 FROM package
 LEFT JOIN request
 ON package.package_id = request.package_id
 LEFT JOIN supplier
 ON supplier.supplier_id = requests.supplier_id

edited Dec 24 '14 at 06:11

answered Dec 22 '14 at 06:48

Faiyaz Alam

1,191
9
27

How to display value from 3 mysql tables

2 Answers2