Duplicating mysql information on SUM ...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pepe_lepew1962
Forum Commoner
Posts: 44
Joined: Thu Nov 20, 2008 10:29 am

Duplicating mysql information on SUM ...

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Duplicating mysql information on SUM ...

Post 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.
Post Reply