CODEWITHSUNDEEP
phploopsmysqliwhile-loop
user2564276
user2564276

Reputation: 23

MySQLi One Query Instead of Two (single while loop instead of while loop in while loop)

I got three tables : http://sqlfiddle.com/#!2/c7317

I want to get this output per row:

 ProductId = 1
 ProductCode = 1
 ProductRetailPrice = 1
 SubProductThumb = 1.jpg (the first subproducts thumb, where ProductId = 1)

Sub Products of ProductId 1
---------------------------
SubProductId = 1
SubProductPieces = 10

SubProductId = 4
SubProductPieces = 40

SubProductId = 7
SubProductPieces = 70

I got this code working with MySQL :

$show_product_content = mysql_query("
        SELECT DISTINCT a.`ProductId`, a.`ProductCode`, a.`ProductRetailPrice`, a.`ProductPrice`, a.`ProductOffer`, a.`ProductTopSeler`, a.`ProductStrass`, a.`ProductStatus`, b.`SubProductThumb`
        FROM `Products` as a, `SubProducts` as b
        WHERE b.ProductId=a.ProductId
        GROUP BY a.`ProductId`
    ");

    while($row = mysql_fetch_array($show_product_content))
    {
        echo '<br />ProductId = '.$row['ProductId'].'<br />';
        echo 'ProductCode = '.$row['ProductCode'].'<br />';
        echo 'ProductRetailPrice = '.$row['ProductRetailPrice'].'<br />';
        echo 'SubProductThumb = '. $row['SubProductThumb'].'<br /><br />';

        $product_id = $row['ProductId'];
        $show_sub_product_content = mysql_query("
            SELECT SubProductId, SubProductPieces, SubProductStatus, SubProductRingSize6, SubProductRingSize7, SubProductRingSize8, SubProductRingSize9, c1.ColorHex as Color1, c1.ColorName as ColorName1, c2.ColorHex as Color2, c2.ColorName as ColorName2
            FROM SubProducts
            INNER JOIN Colors c1 ON c1.ColorId=SubProducts.SubProductColor1
            INNER JOIN Colors c2 ON c2.ColorId=SubProducts.SubProductColor2
            WHERE ProductId='$product_id'
        ");

        echo 'Sub Products of ProductId '.$product_id.'<br />------------------------------------<br />';

        while($row = mysql_fetch_array($show_sub_product_content))
        {
            echo 'SubProductId = '.$row['SubProductId'].'<br />';
            echo 'SubProductPieces = '.$row['SubProductPieces'].'<br /><br />';
        }
    }

but i want to do this with prepare statement and single query, is this possible?

Upvotes: 0

Views: 88

Answers (2)

DevZer0
DevZer0

Reputation: 13535

Your single query is below, i combined your two queries. 

 SELECT a.`ProductId`, a.`ProductCode`, a.`ProductRetailPrice`, a.`ProductPrice`, a.`ProductOffer`, a.`ProductTopSeler`, a.`ProductStrass`, a.`ProductStatus`, b.`SubProductThumb`, b.SubProductId, b.SubProductPieces, b.SubProductStatus, b.SubProductRingSize6, b.SubProductRingSize7, b.SubProductRingSize8, b.SubProductRingSize9, c1.ColorHex as Color1, c1.ColorName as ColorName1, c2.ColorHex as Color2, c2.ColorName as ColorName2
    FROM `Products` as a, `SubProducts` as b
    LEFT JOIN Colors c1 ON c1.ColorId=b.SubProductColor1
    LEFT JOIN Colors c2 ON c2.ColorId=b.SubProductColor2
    WHERE b.ProductId=a.ProductId
    GROUP BY a.`ProductId`

Upvotes: 1

lour
lour

Reputation: 25

I think that a LEFT JOIN should be useful in that specific case. I am doing some tests, I will let you know.

Upvotes: 0

Related Questions