php and mysql how to join a table

Question

PHP & MySQL How to retrieve multiple records from one table and corresponding records from another table and display them together.

I have a code which sums all the purchase value of all firms in my table (trades) and displays the value against the firm code(buy_firm_code). I have another table (client_firm) which holds all my firm and their codes.The firm name field is called firm_name while the firm code field is called firm_code which is a primary field for buy_firm_code field of trades table. I want to retrieve the firm_name from the client_firm table and display them against their corresponding buy_firm_code which I retrieved from trades table. How do I write this second part of the PHP code. Thanks in advance.

<?php

//code that sums all the purchase value of all firms in my table (trades) and displays the value against the firm code(buy_firm_code)

$con=mysqli_connect("localhost","user","password","database");

// Check connection

if (mysqli_connect_errno())

{

echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$buy_firm_code="";         
$buy_value="";

$sql="SELECT buy_firm_code, SUM(trade_value) value_bought FROM trades GROUP BY buy_firm_code ORDER BY value_bought DESC";

if ($result=mysqli_query($con,$sql))
{

// Fetch one and one row

  while($row=mysqli_fetch_array($result)){

  $buy_firm_code=$row['buy_firm_code']; 
  $buy_value = $row['value_bought'];

  echo $buy_firm_code.'<-->'.$buy_value .'<br>';

}

// Free result set
 mysqli_free_result($result);
}
mysqli_close($con);

?>

Why do you not want to join the results in the SQL query? – Marshall Davis Jul 02 '15 at 21:07 — Marshall Davis, Jul 02 '15 at 21:07

score 1 · Accepted Answer · answered Jul 02 '15 at 20:41

1

change your sql query to this

$sql="SELECT t.buy_firm_code, f.firm_name, SUM(t.trade_value) value_bought 
    FROM trades t,client_firm f
    WHERE f.firm_code = t.buy_firm_code
    GROUP BY buy_firm_code ORDER BY value_bought DESC";

and the while block to this

while($row=mysqli_fetch_array($result)) {
    $buy_firm_code=$row['buy_firm_code']; 
    $buy_value = $row['value_bought'];
    $firm_name = $row['firm_name'];
    echo $buy_firm_code.'<-->'.$firm_name.'<-->'.$buy_value .'<br>';
}

and let me know if it displays what you need

answered Jul 02 '15 at 20:41

Alex Andrei

7,315
3
28
42

Thanks a lot guys. I will try both the 'WHERE' and 'JOIN' options later today and let you know how it went. – Gyne Jul 06 '15 at 03:23
Thanks a lot again guys . Both the "Where" and "JOIN" worked perfectly well. I am facing a new challenge though which I hope to post after trying my best. – Gyne Jul 07 '15 at 19:21
You are welcome, please mark the answer as accepted if it worked out for you – Alex Andrei Jul 07 '15 at 20:03

php and mysql how to join a table

1 Answers1