query tables

nikko50

OK, I've spent a day on this query. I have 2 tables. One is named shiplist and the other is named receive. Shiplist contains 2 fields, Item and Quantity shipped. Receive contains 2 fields Equipment and Quantity received. I'm trying to print a summary table with three fields Equipment, Received, Shipped. The statement below prints a table but it seems to multiply the fields like if I receive 1 apple, in the summary would show 6 apples received. I'm stumped on this one. Help??? Sharon

$equiplist= "select shiplist.item, sum(shiplist.quantity) as 'shippedsum', sum(receive.quantity) as 'receivedsum'
from shiplist
left join receive on receive.equipment = shiplist.item
group by shiplist.item";

laserlight

When dealing with this kind of thing, it is useful to outline how the various fields are related.

Table: shiplist
item
quantity_shipped

Table: receive
equipment
quantity_received

Now, how does 'item' relate to 'equipment'?

You should also use an index on each table.
This would be a primary key such that each row in the table is uniquely identified.

nikko50

Hi laserlight. I have an autoincrement primary in both tables. The item and equipment fields are both the same. The sql above seems to add values to both the shipped and received columns in my summary.

laserlight

hmm... I still dont get what the summary is supposed to summarise.

I was actually hoping that you would be quick enough to give:
1. The scenario: what are you trying to do?
2. The database schema: what are the tables, the fields, and how do they relate?
in addition to the code snippet that you provided.

nikko50

Hi there. I'm trying to summarize the equipment shipped and equipment received. The shiplist table contains....

item quantity
apples 1
apples 3
oranges 3
oranges 3

The receive table contains...

equipment quantity
apples 10
apples 10
grapes 10

My code is as follows...

//get equipment list
$equiplist= "select shiplist.item, sum(shiplist.quantity) as 'shippedsum', sum(receive.quantity) as 'receivedsum'
from shiplist
left join receive on receive.equipment = shiplist.item
group by shiplist.item";
$equipresult=mysql_query($equiplist) or print (mysql_error());

echo "<p align='center'><font size='4'><b>Inventory </b></font></p>";
     print "<br>";



print "<table border=1 width=70% cellpadding=1 cellspacing=1 align=center>";
print "<tr>
            <td width=35%><font size='2'>Equipment</font></td>
	<td width=8%><font size='2'>Shipped</font></td>
	<td width=8%><font size='2'>Received</font></td>
            <td width=8%><font size='2'>On Hand</font></td>

</tr>
</table>";


  while ($row= mysql_fetch_array($equipresult))
           { extract($row);
             $onhand=$receivedsum-$shippedsum;
             $onhand = ( $onhand <= 0 ) ? '<span style="color: red">'.$onhand.'</span>' : $onhand; 


  	print "<table border=1 width=70% cellpadding=1 cellspacing=1 align=center>";
print "<tr>
            <td width=35%><font size='2'>$item</font></td>
	<td width=8%><font size='2'>$shippedsum</font></td>
	<td width=8%><font size='2'>$receivedsum</font></td>
            <td width=8%><font size='2'>$onhand</font></td>

</tr>
</table>";}

laserlight

Store information on the items themselves in another table.

There's also a problem with the way you are entering data, in that the same item appears more than once.

Table: shiplist
id
item_id
quantity

Table: receive
id
item_id
quantity

Table: items
id
name

Consider an items table:
id name
1 apples
2 oranges
3 grapes

The shiplist table should look like:
item_id quantity
1 4
2 6

and the receive table:
item_id quantity
1 20
3 10

Read up PHP Builder's article on Database Normalization And Design Techniques