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 }