PHP MySQL split output into sections

Question

I'm trying to do something with PHP & MySQL that I just cannot get my head around.

I have a table that takes the following structure (they are two tables but will act as one when they are joined in the SQL query):

id | brand  | model  
1  | brand1 | model1  
2  | brand1 | model2  
3  | brand1 | model3  
4  | brand2 | modelA

I've been able to get PHP to output the following fine:

brand1 model1  
brand1 model2  
brand1 model3  
brand2 modelA

What I'd really like to be able to do is output it as:

Brand1  
Model1  
Model2  
Model3  

Brand2  
ModelA

Anyone got any ideas?

This is my current code:

<?php 
$result = mysql_query("SELECT cameras.cameraid, cameras.model, brands.brand FROM cameras JOIN brands ON cameras.brandid=brands.brandid WHERE cameras.categoryid='$cat' ORDER BY brands.brand ASC, cameras.level ASC")or die(mysql_error());

while($row = mysql_fetch_array($result))
{
     echo "<li><a href='camera.php?id=". $row['cameraid'] . "'>" . $row['brand'] . " " . $row['model'] . "</a></li>";
}

?>

Create an array. Loop over the data. In that array, create arrays for each brand (if it hasn't been created already). Push the models into the correct brand. Basically, make a 2D array. — gen_Eric, Sep 16 '15 at 21:11
You can use nested queries. Outer query: get unique brand, foreach brand (inner query) get all the models. — Maximus2012, Sep 16 '15 at 21:12
Could either of you write a small piece of example code to show me what you mean please? I've tried ways of both and can't get it to work. — bigal, Sep 16 '15 at 21:41
I'll never stop repeating that MYSQL_* functionalities are deprecated and will be removed in the next version of PHP. Update to mysqli_ or use PDO (best option) — Lelio Faieta, Sep 17 '15 at 08:07
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Mar 17 '16 at 21:21

score 4 · Accepted Answer · answered Sep 16 '15 at 23:18

What you're looking for is a two-dimensional array with a nested foreach-loop. This will allow you to have an array that looks like this

Array ( 
    [brand1] => Array ( 
        [1] => model1 
        [2] => model2 
        [3] => model3 
    ) 
    [brand2] => Array ( 
        [4] => modelA 
        [5] => modelB 
        [6] => modelC 
    ) 
)

We use this to our advantage when we are later echoing out the models as a list of each brand. The first array will contain all the brands, while each brand is a array of its own - creating the two dimensional array, which contains all the models (and the product IDs as the key for the elements in that array).

Put each of your elements into an array in your while($row = mysql_fetch_array($result))-loop, and use the nested foreach-loop to echo them out.

while ($row = mysql_fetch_array($result)) {
    // Here we fetch all the results, and put it into a two-dimentional array 
    // with keys as brandname and the ID of the product
    $brands[$row['brand']][$row['cameraid']] = $row['model'];
}

// Then we echo them all out in a list!
foreach ($brands as $key=>$brand) {
    // The $key is the name of the brand
    echo $key."<ul>";
    foreach ($brand as $id=>$model) {
        // The $id is the ID of the camera in the table
        // The $model is the model-name itself
        echo "<li><a href='camera.php?id=".$id."'>".$model."</a></li>";
    }
    echo "</ul>";
}

Furthermore, it's deprecated and not recommended, to use mysql_* functions, you should consider moving onto mysqli_* or PDO for better functionality, continuous support and better security.

Thanks, this worked well. Very good detailed answer with explanations too! — bigal, Sep 17 '15 at 14:47

score 1 · Answer 2 · answered Sep 17 '15 at 06:37

You should try something like this. It is using multidimensional arrays to group items.

<?php 
$result = mysql_query("SELECT cameras.cameraid, cameras.model, brands.brand FROM cameras JOIN brands ON cameras.brandid=brands.brandid WHERE cameras.categoryid='$cat' ORDER BY brands.brand ASC, cameras.level ASC")or die(mysql_error());

$brands = array();
while($row = mysql_fetch_array($result))
{
    $brands[$row['brand']][] = array($row['model'], $row['cameraid']);
    echo "<li><a href='camera.php?id=". $row['cameraid'] . "'>" . $row['brand'] . " " . $row['model'] . "</a></li>";
}

foreach($brands as $brand => $data){
    echo $brand . "<br>";
    foreach($data as $item){
        echo "<li><a href='camera.php?id=" . $item[1] . "'>" . $item[0] . "</a></li>";
    }
}

?>

Saeed Ansari · Answer 3 · 2015-09-17T08:03:32.337

-1

below is the code for grid using table It will give you and idea how to achieve your task

$sql = mysql_query("SELECT id, member_name FROM member_table ORDER BY id DESC LIMIT 15"); 
$i = 0;
// Establish the output variable
$dyn_table = '<table border="1" cellpadding="10">';
while($row = mysql_fetch_array($sql)){ 

    $id = $row["id"];
    $member_name = $row["member_name"];

    if ($i % 3 == 0) { // if $i is divisible by our target number (in this case "3")
        $dyn_table .= '<tr><td>' . $member_name . '</td>';
    } else {
        $dyn_table .= '<td>' . $member_name . '</td>';
    }
    $i++;
}
$dyn_table .= '</tr></table>';

line by line explanation of this code is in the link below

Array gird output tutorial will give you some Idea try to use this for your won scnario Array Grid output tutorial well explained video

edited Sep 17 '15 at 08:03

answered Sep 16 '15 at 22:01

Saeed Ansari

455
8
16

1

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – Jazi Sep 17 '15 at 06:49
OK but I wanted to comment this but my score didn't allow me to comment I will paste code here – Saeed Ansari Sep 17 '15 at 07:59

PHP MySQL split output into sections

3 Answers3