php mysql group by

Question

this is my table

 row  | car_id | car_model | car_features |
  1      1          CAR 1      Features 1
  2      2          CAR 2      Features 2
  3      2          CAR 2      Features 3

and i want to make it like

 row  | car_id | car_model | car_features |
  1      1          CAR 1      Features 1
  2      2          CAR 2      Features 2, Features 3

and this is my php mysql script:

<?php
    $con = mysql_connect("localhost", "root", "root");
    mysql_select_db("car", $con);
    $format = mysql_query("SELECT c.* , p.*, d.*,f.* ,e.* FROM bsi_car_master c,bsi_car_type p, bsi_car_vendor d, bsi_selected_features f, bsi_car_features e WHERE c.car_type_id=p.id AND c.car_vendor_id=d.id AND c.car_id = f.car_id AND f.features_id = e.id");
    $row = 1;

    while($srow = mysql_fetch_array($format))
    {
        blah blah blah....
    }
?>

can show me some example ... :)

user3081581
– user3081581

2015-10-19 07:02:00 +00:00
Commented Oct 19, 2015 at 7:02 — user3081581
– user3081581, Commented Oct 19, 2015 at 7:02
Aw, don't use group concat. Just modify your loop.

Strawberry
– Strawberry

2015-10-19 07:38:50 +00:00
Commented Oct 19, 2015 at 7:38 — Strawberry
– Strawberry, Commented Oct 19, 2015 at 7:38
@Strawberry can give me some example?

user3081581
– user3081581

2015-10-19 07:52:49 +00:00
Commented Oct 19, 2015 at 7:52 — user3081581
– user3081581, Commented Oct 19, 2015 at 7:52

Sougata Bose · Accepted Answer · 2015-10-19 07:03:36Z

3

Use GROUP_CONCAT with GROUP BY. Try this -

SELECT `row`, `car_id`, `car_model`, GROUP_CONCAT(`car_features`, ',') 
FROM your_table GROUP BY `car_id`

answered Oct 19, 2015 at 7:03

Sougata Bose

31.8k8 gold badges52 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sateesh Pagolu · Accepted Answer · 2015-10-19 07:04:12Z

2

SELECT car_id,car_model,GROUP_CONCAT(car_features,',')  
FROM yourtable
GROUP BY car_id,car_model;

answered Oct 19, 2015 at 7:04

Sateesh Pagolu

9,6563 gold badges32 silver badges49 bronze badges

Comments

Ninju · Accepted Answer · 2015-10-19 07:09:06Z

0

$format = mysql_query("SELECT c.* , p.*, d.*,f.* ,e.*,group_concat(`c.car_features`,',') as `carfeatures` FROM bsi_car_master c,bsi_car_type p, bsi_car_vendor d, bsi_selected_features f, bsi_car_features e WHERE c.car_type_id=p.id AND c.car_vendor_id=d.id AND c.car_id = f.car_id AND f.features_id = e.id group by c.car_id");

answered Oct 19, 2015 at 7:09

Ninju

2,5322 gold badges16 silver badges21 bronze badges

Comments

Strawberry · Accepted Answer · 2015-10-19 12:51:02Z

Hopefully, it's fairly obvious that I'm no PHP coder, but here's another way to do it, without GROUP_CONCAT()...

<?php
/*
DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,car_id INT NOT NULL
,car_model VARCHAR(12) NOT NULL
,car_features VARCHAR(20) NOT NULL
);

INSERT INTO my_table VALUES
(1      ,1          ,'CAR 1','Features 1'),
(2      ,2          ,'CAR 2','Features 2'),
(3      ,2          ,'CAR 2','Features 3');
*/

require('path/to/mysqli/connection/stateme.nts');

$query = "
    SELECT id
         , car_id
         , car_model
         , car_features
      FROM my_table
     ORDER
        BY car_model;
    ";

$result = mysqli_query($db,$query);
 $car_id = 0;

 while($row = mysqli_fetch_assoc($result)){
 if ($car_id== $row['car_id']){
 echo  "  >".$row['car_features']."<br>\n";
 } else {
 echo $row['car_model']."<br>\n  >".$row['car_features']."<br>\n";
$car_id = $row['car_id'];
 }
 } // end of while loop

/* 
Outputs...

CAR 1
>Features 1
CAR 2
>Features 2
>Features 3
*/

?>

Collectives™ on Stack Overflow

php mysql group by

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related