Duplicating information with mysql on group ...

Discussion in 'Databases' started by pepe_lepew1962, Sep 13, 2011.

  1. #1
    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
     
    pepe_lepew1962, Sep 13, 2011 IP
  2. mastermunj

    mastermunj Well-Known Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    110
    #2
    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):
     
    mastermunj, Sep 14, 2011 IP
  3. pepe_lepew1962

    pepe_lepew1962 Member

    Messages:
    36
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #3
    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 />";
    //
    }
     
    pepe_lepew1962, Sep 25, 2011 IP
  4. mastermunj

    mastermunj Well-Known Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    110
    #4
    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!
     
    mastermunj, Sep 25, 2011 IP
  5. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #5
    $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];
     
    Rukbat, Sep 26, 2011 IP
  6. pepe_lepew1962

    pepe_lepew1962 Member

    Messages:
    36
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #6
    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
    }
     
    pepe_lepew1962, Sep 26, 2011 IP