Duplicating information with mysql on group ...

Hello:

I am trying to get MAX, MIN and AVG totals along with the basic information for groups of SupplierIDs with mysql. I know there will be duplicate information and that is the intent. Any help would greatly be appreciated.


CREATE TABLE tblSupplier
(
tblSupplier_ID CHAR(3) NOT NULL,
tblSupplier_Name CHAR(4) NOT NULL,
tblSupplier_Model VARCHAR(7)
)
;
//
CREATE TABLE tblPart
(
tblPart_ID CHAR(3) NOT NULL,
tblPart_Type CHAR(8) NOT NULL,
tblPart_Price DECIMAL(6,2)
)
;
//
//
INSERT INTO tblSupplier (tblSupplier_ID,tblSupplier_Name,tblSupplier_Model) VALUES ('125','Sony','Desktop');
INSERT INTO tblSupplier (tblSupplier_ID,tblSupplier_Name,tblSupplier_Model) VALUES ('136','Sony','Desktop');
//
INSERT INTO tblPart (tblPart_PartID,tblPart_Type,tblPart_Price) VALUES ('125','DRIVE','500.00');
INSERT INTO tblPart (tblPart_PartID,tblPart_Type,tblPart_Price) VALUES ('125','VIDEO','100.00');
INSERT INTO tblPart (tblPart_PartID,tblPart_Type,tblPart_Price) VALUES ('125','KEYBOARD','63.00');
INSERT INTO tblPart (tblPart_PartID,tblPart_Type,tblPart_Price) VALUES ('136','DRIVE','450.00');
INSERT INTO tblPart (tblPart_PartID,tblPart_Type,tblPart_Price) VALUES ('136','VIDEO','300.00');
//
//
//
Result Fields:
tblSupplier_Name,tblSupplier_ID,tblPart_Type, MAX(tblPart_Price), MIN(tblPart_Price), AVG(tblPart_Price)
Sony-125-DRIVE-500.00-500.00-63.00-221.00
Sony-125-VIDEO-100.00-500.00-63.00-221.00
Sony-125-KEYBOARD-63.00-500.00-63.00-221.00

Sony-136-DRIVE-450.00-450.00-300.00-375.00
Sony-136-VIDEO-300.00-450.00-300.00-375.00

 

Following may be helpful..

SELECT
	tblSupplier_Name,
	tblSupplier_ID,
	tblPart_Type,
	tblPart_Price,
	MaxPrice,
	MinPrice,
	AvgPrice
FROM
	tblsupplier
LEFT JOIN(
	SELECT
		tblPart_ID,
		tblPart_Type,
		tblPart_Price,
		MAX(tblPart_Price)AS MaxPrice,
		MIN(tblPart_Price)AS MinPrice,
		AVG(tblPart_Price)AS AvgPrice
	FROM
		tblpart
	GROUP BY
		tblPart_ID
)Z ON tblSupplier_ID = tblPart_ID

Code (markup):

 

Hmmm, how do I explain this...when I copy and paste the query into phpmyadmin I get the results I want with just 1 problem. It gives me "unknown table status: TABLE_TYPE" at the bottom of the screen. I am not sure how important that is, but the results do show up on the screen. The version is 3.2.4 and I think I need a newer version, but it is not all that important to me. Now the real problem. When I try displaying the records via php, I get squat, nada, zilch. This is what I have. I have NO idea whether it has to do with aliases or now, but any help would rock. Thanks.



$num = mysql_num_rows($result);
echo $num;
echo "<br />";
echo "<br />";
//
while ($row = mysql_fetch_array($result))
//
{
//
$SuppName = $row['tblSupplier_Name'];
$SuppID = $row['tblSupplier_ID'];
$PartMax = $row['MaxPrice'];
//
echo $row['$SuppName'] . " " . $row['$SuppID'];
echo "<br />";
//
}

 

Below line

echo $row['$SuppName'] . " " . $row['$SuppID'];

Code (markup):

should be

echo $row[$SuppName] . " " . $row[$SuppID];

Code (markup):

Also, if you could elaborate the problem a bit, I can help!

 

$row['$SuppName'] is looking for the field named $SuppName, it's not looking for the field with the name in the field tblSupplier_Name. PHP evaluates variables enclosed in double quotes, not in single quotes. (And you only quote a literal string like "this is a literal string", you don't quote variables unless you're including literals - "this is a string that would echo the value of $SuppName".)

Try

echo $row[$SuppName] . " " . $row[$SuppID];

 

Thank you both. mastermunj, you say I should elaborate the problem. Hmm, okay, well your answers helped a little, it is displaying a bit, but the information is 90% missing and when does show is wrong, like totally wrong. Seriously I am gonna start drinking in a bit. I am thinking about dumping the parts portion into a table, then updating the fields from supplier table into that. It burns me to have to work this way as it is additional resources that are unnecessary. I tried using the following code to see what information it dumps back and oddly enough, that is all there, but I can not access it, or I have the lack of knowledge on how to do so. Any ideas or what more info do you need?


//iterate trough the query result and fetch each field
while($field=mysql_fetch_object($result))
{
$fields[]=$field;//collect each field into a array
}
//
print_r($fields);//test the fields array
//
foreach($fields as $key=>$field)
{
echo $field->Field.'</br>'; // print each field name
}