Problem with display data

[saraa]

Hi all..can somebody tell me how to do this..

I have two tables, po and po_item..
There is one to many relationship.

PO
po_id
po_no
po_date

po_item
item_id
po_no
item
qty
unit_price
total

Now i want to display all data from po and a total price for every po that i sum from po_item.I mean sum total from po_item group by po_no.

This is what i have done so far..

<?
$query = "SELECT * FROM po";
$result=mysql_query($query);
    if (!$result)
            die("<p>ERROR number". mysql_errno(). ": ".mysql_error() ."</p>");
            $num_results=mysql_num_rows($result);                             
?>

<?
// Display data

<table width="95%" border="1" align="center" cellpadding="3" cellspacing="0" class="title" bordercolor="#CCCCCC">
 <tr>
      <td align="center" colspan="9">&nbsp;&nbsp;</td>
  
  </tr>
  <tr> 
  <td width="19%" align="center"> P.O NO </td>
  <td width="11%"><font face="Tahoma">DATE</font></td><td> TOTAL PRICE </td>               
</tr>                
                
    <? for ($i=0; $i < $num_results; $i++){
    $row=mysql_fetch_array($result);
    ?>        
    <tr>                                        
            <td><? echo $row['po_no']?></td>
           <td><? echo $row['po_date']</td> 
           <td>&nbsp;</td>                                                         
    </tr>
    <? } ?>
    </table>

Can someone give me an idea or way how to do this..thanks in advance

[kows]

well, I don't exactly get what you want :/ you should try to explain it a little clearer. but, you probably want to use a JOIN statement in your SQL query.

use a select query statement like:

SELECT po.po_id, po.po_no, po.po_date, pi.qty, (pi.unit_price * pi.qty) as totalprice 
FROM po, po_item pi 
LEFT JOIN pi ON pi.po_no = po.po_no 
ORDER BY po.po_no, pi.item_id ASC

still, though, not sure what exactly you want so I don't know if that would work for you. plus, I might have mixed the JOIN up and maybe it should be joining on po.po_no = pi.po_no. if you can give me some more information about what you're really looking to do, I can make up a mock database to figure out something that should work for you.

[saraa]

Thanks a lot Mr.Kow..

Sorry for my bad english..i have tried query in Access..and this is a query.

1)I combined po and po_item to get the sum of total price for every PO.

Query1
SELECT po.po_no, Sum(po_item.total) AS SumOftotal
FROM po INNER JOIN po_item ON po.po_no = po_item.po_no
GROUP BY po.po_no;

2)I combined po table with Query1 to get all PO details. So that i can view all PO information together with total price.

Query2
SELECT po.*, Query1.SumOftotal
FROM Query1 INNER JOIN po ON Query1.po_no = po.po_no;

the figure is somethinglike this..

PO NO | PO DATE | TOTAL PRICE | DETAILS |
1 | 12/4/07 | $500 | Item Details |

Hope you can understand me Mr.kow. Please tell me how can i combined these queries in php.Or there is the simple way to do this.Thanks a lot