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
– gen_Eric, Commented Sep 16, 2015 at 21:11
You can use nested queries. Outer query: get unique brand, foreach brand (inner query) get all the models. — Maximus2012
– Maximus2012, Commented Sep 16, 2015 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
– bigal, Commented Sep 16, 2015 at 21:41

Qirel · Accepted Answer · 2015-09-16 23:18:15Z

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!

Ahmad · Accepted Answer · 2015-09-17 06:37:52Z

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 · Accepted Answer · 2015-09-17 08:03:32Z

-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, 2015 at 8:03

answered Sep 16, 2015 at 22:01

Saeed Ansari

4659 silver badges18 bronze badges

2 Comments

Jazi Over a year ago

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.

Saeed Ansari Over a year ago

OK but I wanted to comment this but my score didn't allow me to comment I will paste code here

Collectives™ on Stack Overflow

PHP MySQL split output into sections

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related