How to join two table column with one?

Question

I have a mysql table user

username     name
---------   ------

u1           abc
u2           xyz
u3           mrz 

and another is trading

product     price      buyer seller
---------   ------    ------ -------

antivirus   20           u1    u3     
e-book      10           u2    u1

I want to show as

product     price      buyer seller
---------   ------    ------ -------

antivirus   20           abc    mrz     
e-book      10           xyz    abc

My question is how to join these two tables ?and how to show data as if i echo as row['name'] it will show the same name both for buyer and seller ?

Answer 1

Try this it will work :

Use Inner Join

 SELECT t1.`product` AS `product`,t1.`price` AS `price`,t2.`name` AS `buyer`,t3.`name` AS `seller` FROM trading t1
JOIN user t2 ON t2.`username`=t1.`buyer`
JOIN user t3 ON t3.`username`=t1.`seller`

Output :

Answer 2

I ll answer here at your last reply as it won't indent properly.

$result=mysql_query($query); // get the query

if(!$result){
    // if fails, do your things..
}

while($row=mysql_fetch_assoc($result)){
    // get data, i.e: print it ALL ROWS!
    echo $row['seller'];
    echo $row['price'];
    // and more...
}
mysql_free_result($result); // free the resource.

If i did not misunderstand you, that way you can work with them values.

Answer 3

SELECT t.product AS product, t.price AS price, u1.name AS buyer, u2.name AS seller FROM trading t JOIN user u1 ON(t.buyer=u1.username) JOIN user u2 ON(t.buyer=u2.username);