looking for efficient way to group data on output in PHP

Question

I have a table that holds products, another that holds variations and a third that hold product variations (many products can have many variations..both ways). I want to display (group by) each product and show the variations.

what i've read is just looping thru and comparing id's to see which record you are on, if a new one then adjust output...doesn't seem clean.

Answer 1

If you are writing the SQL your choices are either one query where you do the comparisons or many queries which are dependent on the first. On the other hand, ADOdb for PHP has ActiveRecords which can handle the one-to-many relationship for you. http://phplens.com/lens/adodb/docs-active-record.htm#onetomany

Answer 2

I would suggest modifying your SQL to show the data you want, that way there should be no need manually modify the data. The group by clause, or order by clause may be your friend here.

Answer 3

I would prefer to use a caching Sytem.

Implementing The Cache in PHP

<?php
// start the output buffer
ob_start(); ?>

//Your usual PHP script and HTML here ...

<?php
$cachefile = "cache/home.html";
// open the cache file "cache/home.html" for writing
$fp = fopen($cachefile, 'w');
// save the contents of output buffer to the file
fwrite($fp, ob_get_contents());
// close the file
fclose($fp);
// Send the output to the browser
ob_end_flush();
?>

Using the cached files

<?php

$cachefile = "cache/home.html";

if (file_exists($cachefile)) {


    // the page has been cached from an earlier request

    // output the contents of the cache file

    include($cachefile); 


    // exit the script, so that the rest isnt executed
    exit;

}

?>

Answer 4

I think you're looking for a way to make a list of a single product and all it's variations. Or make a list of all products, and then for every product to show every variation of that product.

I'm assuming your tables look somewhat like this:

product
    productId
    description

variation
    variationId
    description

productVariation
    productVariationId
    productId
    variationId

For a single product and all it's variations you could use the following query, that does two inner joins.

SELECT
    P.description as product,
    V.description as variation
FROM
    product as P,
INNER JOIN
    productVariation AS PV
    ON PV.productId = P.productId
INNER JOIN
    variation as V
    ON V.variationId = PV.variationId
WHERE
    P.productId = 1

For the whole list of products, just omit the WHERE clause. If a product has no variations, it won't be included in the list. If you want that, use LEFT JOINs instead.

The query will return the following

product variation
shoe    blue
shoe    green
shoe    red
hat     green
hat     purple
hat     yellow
sock    white

Update:

I'm guessing you want the data displayed as follows:

shoe
blue
green
red

hat
green
purple
yellow

sock
white

That can be done by the following PHP code.

$sql = "
    SELECT
        P.productId,
        P.description as product,
        V.description as variation
    FROM
        product as P,
    INNER JOIN
        productVariation AS PV
        ON PV.productId = P.productId
    INNER JOIN
        variation as V
        ON V.variationId = PV.variationId    
";

$result = mysql_query($sql);

//first put all the results into an array so we can look backward and 
//see previous items
$resultSet = array();
while($record = mysql_fetch_array($result)) {
    $resultSet[] = $record;
}

for ( $i = 0 ; $i < count($resultSet) ; $i++ ) {
    if ( $i == 0 ) {
        //for the first item, show the product name
        echo $resultSet[$i]['product'].'<br/>';
    } else if ($resultSet[$i]['productId'] != $resultSet[$i-1]['productId']) {
        //every time we encounter a new product
        //display a new line and show the product name
        echo '<br/>'.$resultSet[$i]['product'].'<br/>';
    }
    echo $resultSet[$i]['variation'].'<br/>';
}