Page 1 of 1

Duplicating mysql information on SUM ...

Posted: Sun May 22, 2011 3:20 pm
by pepe_lepew1962
Hello:

I am having problems getting information from 2 tables and think this is a mysql issue instead of php. I have a MAIN table that lists computer models and another PART table that has it's components. My query result needs to be, list the model, manuf and type ( main ) and all it's components (parts ), including the unit price, the max unit price, the min unit price and the average unit price. My primary focus is with the PART table. The result will list the same model and manuf along with all the parts and prices. Maybe an example would help:
//
$ritelist2101 = "SELECT tblMain.tblMain_Main00, tblMain.tblMain_Main05, tblMain.tblMain_Main06, tblPart.tblPart_Part00, tblPart.tblPart_Part01, tblPart.tblPart_Part02 FROM tblMain, tblPart WHERE (tblMain.tblMain_Main00 = tblPart.tblPart_Part00) ORDER BY tblMain_Main05, tblMain_Main00";
$ritelist2102 = mysql_query($ritelist2101);
//
Sample Data:
Main00=125
Main05=Sony
Main06=Desktop
//
Main00=136
Main05=Sony
Main06=Desktop
//
Main00=142
Main05=Dell
Main06=Laptop
//
//
Part00=125
Part01=Drive
Part02=500.00
//
Part00=125
Part01=Video
Part02=100.00
//
Part00=125
Part01=Keyboard
Part02=63.00
//
//
Part00=136
Part01=Drive
Part02=450.00
//
Part00=136
Part01=Video
Part02=300.00
//
RESULT: (Manu-Model-Part-Price-Max-Min-Avg)
Sony-125-DRIVE-500-500-63-221
Sony-125-VIDEO-100-500-63-221
Sony-125-KEYBOARD-63-500-63-221
Sony-136-DRIVE-450-450-300-375
Sony-136-VIDEO-300-450-300-375
etc...
//
I fully understand that a lot of information is duplicated, but it is not my place to ask, but to do. Supposedly more calculations will be made, but first things first. Also, I need to read the data with:

$Main00 = $row['tblMain_Main00']; // Main Model
$Main05 = $row['tblMain_Main05']; // Manufacturer
$Main06 = $row['tblMain_Main06']; // Type
$Part00 = $row['tblPart_Part00']; // Part Model

Can anyone please help me on this.

Re: Duplicating mysql information on SUM ...

Posted: Sun May 22, 2011 9:44 pm
by califdon
You need to understand the difference between a results set from a query and the display of that data in a web page or wherever. Yes, there will be duplication of the data from MAIN in each row of the results set that contains details from the PART table for the same computer. That's the way queries work. But that doesn't mean that you have to display it that way. That's normally handled by the PHP code that displays your data. It is common to compare the key field of the "parent" table in each row with the key field value saved in a variable from the previous row. When those 2 values are equal, it means that you skip displaying the "parent" table data for the current row.