Group by common id

Question

I am trying to fetch data from my database and would like to group common values in the column called order_ids by that id.

This is the state I currently get my data in

Order_Id      |    Product Name

-------------------------------
  10001       |    iPhone 5
  10001       |    Blackberry 9900
  10002       |    Galaxy S
  10003       |    Rhyme
  10004       |    Google Nexus
  10005       |    Razr
  10006       |    iPad Air

And this is the state I want to get it in

Order_Id      |    Product Name

-------------------------------
  10001       |    iPhone 5
                   Blackberry 9900
  10002       |    Galaxy S
  10003       |    Rhyme
  10004       |    Google Nexus
  10005       |    Razr
  10006       |    iPad Air

Here is how I get the result in my controller file

    foreach($results_query as $results_custom) {
        $this->data['result_custom'][] = array(
            'model' => $results_custom['product_name'],
            'order_number' => $results_custom['order_id']
        );
    }

Here is how I display it in my view file

 <?php foreach ($results_custom as $result) { ?>
 <li><?php echo $result['model']; ?></li> <br />
 <li><?php echo $result['order_number']; ?></li><br />
 <?php } ?>

Is it possible to get my data to display like that or in that state by using SQL or PHP? Please let me know if you want to see my query as well.

handle that on the PHP during loop. – John Woo Nov 01 '13 at 17:38 — John Woo, Nov 01 '13 at 17:38

iam-decoder · Answer 1 · 2013-11-01T19:04:04.883

0

you might be able to accomplish this in just MySQL, but it might be easier if you just create a php loop. most people prefer a foreach loop, but I like while loops:

$orderid = "number";
$order_query = mysql_query("SELECT * FROM ordertable WHERE Order_Id = '$orderid'");
while($order_data = mysql_fetch_array($order_query)){
    $ordername = stripslashes(mysql_real_escape_string($order_data['Product Name']));
    echo $ordername.'<br />';
}

if you need this for ALL orders you could remove searching for a specific order:

$order_query = mysql_query("SELECT * FROM ordertable ORDER BY Order_Id ASC");
while($order_data = mysql_fetch_array($order_query)){
    $orderid = mysql_real_escape_string($order_data['Order_Id']);
    $ordername = stripslashes(mysql_real_escape_string($order_data['Product Name']));
    echo 'ID#: '.$orderid.' - '.$ordername.'<br />';
}

edited Nov 01 '13 at 19:04

answered Nov 01 '13 at 17:43

iam-decoder

2,494
1
11
28

just a hint, you can do it just in mysql – Jorge Campos Nov 01 '13 at 18:00
Can you have a look at my updated code if you get the chance to? That's what I currently use. Thanks! – James Nov 01 '13 at 18:42
I am using foreach so I went with @JorgeCampos' code but thanks a lot for your answer as well, I'll use it in the future when I need it :) – James Nov 01 '13 at 19:08
@iamde_coder better to take the second while, but where do you take out the repeated `order_id` as OPs question? – Jorge Campos Nov 01 '13 at 19:12
this is where I would have 2 while loops, the first would output the order id, the second would ouput the product names for the order id. – iam-decoder Nov 01 '13 at 19:18

Jorge Campos · Accepted Answer · 2013-11-01T18:39:42.600

0

In php would be easier to do it. As I don't have PHP enviroment to test it I will show you some logic to do it. Not necessarily working code. Thats because you didn't provide what you did

<?
  $sql = "select order_id, product name from ..... order by order_id"// rest of sql code ....
  //here you iterate your results
  $previousId = ""; //var to store previous id
  while( $fetch... ){
      if ( $fetchedID != $previousId ){
        echo $fetchedId . "-" . $fetchedProductName;
      }else{
        echo $fetchedProductName;
      }
      $previousId = $fetchedID;
  }
?>

This should do. As you updated your code this is a solution for you:

<?php 
   $lines = ""; //to make code cleaner;
   $previousModel = "";
   foreach ($results_custom as $result) {
     if ( $previousModel != $result['model'] ){ 
         $line .= "<li>" .  $result['model'] . "</li>";
     }else{
         $line .= "<li></li>";
     }   
     $line .= "<li>" . $result['model'] . "</li><br />";
     $previousModel = $result['model'];
   }
   echo $line;
 <?php } ?>

edited Nov 01 '13 at 18:39

answered Nov 01 '13 at 17:44

Jorge Campos

20,662
7
51
77

`` <= assuming short tags are "on" ;-) – Funk Forty Niner Nov 01 '13 at 17:54
@Fred-ii- yeas, that is how i'm used to :) – Jorge Campos Nov 01 '13 at 17:59
I have updated my question, please have a look if you can. Thanks! – James Nov 01 '13 at 18:20
@James take a look now, please. – Jorge Campos Nov 01 '13 at 18:40

score 0 · Answer 3 · edited May 23 '17 at 12:03

0

I suggest you to use GROUP_CONCAT for getting the result

You try as follows

SELECT order_id,GROUP_CONCAT(product_name) as product_name FROM your_table GROUP BY order_id

Just look at this http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Note : GROUP_CONCAT has a size limit. Check this link for more MySQL and GROUP_CONCAT() maximum length

edited May 23 '17 at 12:03

Community

1
1

answered Nov 01 '13 at 18:49

Shafeeque

1,990
2
11
27

Thanks for your answer, I'll take a look at it :) – James Nov 01 '13 at 19:09

Group by common id

3 Answers3