more group/sum??

[nikko50]

Sharon here🙂 I have 2 tables that I would like to print a summary.
Table 1...

Equipment Received
flash cards 50
copier 50
keyboard 50
printer 50

Table 2

Equipment Shipped
copier 1
copier 2
printer 1
printer 3
usb cable 4
128MB MEM 4
keyboard 3
keyboard 3

I would like a sql statement to print a table that shows all equipment items, shipped, received.
So it should like like this........

Equipment Received Shipped
flash cards 50 0
copier 50 2
keyboard 50 3
printer 50 2
usb cable 0 4
128mb mem 0 4

All I have so far is this
$equiplist="SELECT item, sum(quantity) as quantity FROM shiplist GROUP BY item";

[Steveo31]

This is the best that I can come up with at the moment.. its creepin up on 1:40am...

<?php

mysql_connect('localhost', 'root');
mysql_select_db('shipped');

$s = mysql_query("SELECT name, qty, status FROM `stuff` AS shipped;");
$r = mysql_query("SELECT name, qty, status FROM `stuff_received` AS received;");

while(($row_shp = mysql_fetch_assoc($s)) && ($row_rec = mysql_fetch_assoc($r))){
?>
<table width="40%">
    <tr>
    	<td>Item</td>
    	<td>Qty Shipped</td>
    	<td>Status</td>
    </tr>
   <tr bgcolor="EEEEEE">
   	<td><?php echo $row_shp['name']?></td>
   	<td><?php echo $row_shp['qty']?></td>
<td>
<?php
echo $row_shp['status'];
if($row_shp['status'] != ''){
?>
	<td><?php echo $row_rec['qty'].' recieved';?></td>

<?php
	}
?>

</td>
</tr>
</table>
<br />
<?php
}
?>

Here's how my db is set up:

#
# Table structure for table `stuff_received`
#

CREATE TABLE stuff_received (
  name varchar(255) NOT NULL default '',
  qty bigint(255) NOT NULL default '0',
  status enum('received') NOT NULL default 'received'
) TYPE=MyISAM;

#
# Dumping data for table `stuff_received`
#

INSERT INTO stuff_received VALUES ('flash cards', 40, 'received');
INSERT INTO stuff_received VALUES ('copier', 0, 'received');
INSERT INTO stuff_received VALUES ('printer', 2, 'received');
INSERT INTO stuff_received VALUES ('stapler', 3, 'received');

And for the other DB (shipped values) all the 'recieved' at the bottom there are 'shipped'.

Not sure if this is what you wanted...

[nikko50]

Thanks so much Steve. Will give that a try
Sharon🙂