php mySQL group concat and group by

Question

I have two table

table 1: 
 row  | car_id | car_model | 
  1      1          CAR 1      
  2      2          CAR 2      
  3      3          CAR 3  
  4      4          CAR 4

table 2:

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

What I want to display :

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

if I using group concat with group by

there will display row 1,2 and I want to display all the rows...

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

Please send me the sql statement..THANK!!

this is my query

$format = mysql_query("SELECT c.* , p.*, d.*,f.* ,GROUP_CONCAT(DISTINCT e.features_title SEPARATOR '<br>') AS 'car_features' 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");

I feel like there is a KEY element missing here.. Where are your efforts? We are not a free coding service... — Naruto
– Naruto, Commented Oct 19, 2015 at 8:47
Why didn't you ask this with the previous question? stackoverflow.com/questions/33208411/php-mysql-group-by — Sougata Bose
– Sougata Bose, Commented Oct 19, 2015 at 8:48

Fjarlaegur · Accepted Answer · 2015-10-19 08:51:33Z

1

You should use an left join as suggested in the comments:

SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.car_id=table2.car_id;

Take a look at some MySQL tutorials. w3schools.org helped me a lot.

I guess that should do the trick for you.

But as said, please put a little more effort in your questions. I am glad to help, StackOverflow is not a free code service!

answered Oct 19, 2015 at 8:51

Fjarlaegur

1,4431 gold badge16 silver badges34 bronze badges

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

2 Comments

Fjarlaegur Over a year ago

If this helped you solve your problem, please consider marking my answer as the right answer for your question. This would be helpfull for all of us. No problem for helping! Hope it solves your problem.

Sophie Over a year ago

Hello, Fjarlaegur could you take a look on my question? stackoverflow.com/questions/75136136/…

Thorsten Kettner · Accepted Answer · 2015-10-19 09:09:52Z

0

Use an outer join, so as to get rows even when no matching record in table2 exists.

UPDATE: Now that you have showed your query:

It is very strange to see your query, because on one hand you seem to be a beginner, on the other you are using a join syntax that was taught last century and is long since out-dated.

So your query should better look like this:

SELECT 
  c.*, 
  p.*, 
  d.*,
  f.*,
  GROUP_CONCAT(DISTINCT e.features_title SEPARATOR '<br>') AS car_features 
FROM bsi_car_master c
INNER JOIN bsi_car_type p ON p.id = c.car_type_id
INNER JOIN bsi_car_vendor d ON d.id = c.car_vendor_id
LEFT JOIN bsi_selected_features f ON f.car_id = c.car_id
LEFT JOIN bsi_car_features e ON e.id = f.features_id
GROUP BY c.car_id

p and d are inner-joined, i.e. there must be a match in p and d otherwise the c record is not selected. But f is outer-joined, so you get c (and p and d) even when no record in f exists for that c (then all f columns are null in that record). As to selecting e, we must also outer-join this, because again, if we got no match, we still want the record c with p and d.

I changed 'car_features' to car_features by the way. Single quotes are for string literals. Double quotes are for names, but they are not needed here

edited Oct 19, 2015 at 9:09

answered Oct 19, 2015 at 8:46

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

6 Comments

Thorsten Kettner Over a year ago

A downvote? Does anybody think that this answer is incorrect? The OP has a query obviously, so they will only have to change the join type. I cannot say whether left or right join, because the OP forgot to post their query.

Thorsten Kettner Over a year ago

I think brevity doesn't render an answer a mere comment. The question was, how to see records in spite of not having a match. The answer is to outer-join the table. However, now that we see the actual query, we see that the join syntax is out-dated and there are two tables that must be outer-joined. I've updated my answer accordingly.

underscore_d Over a year ago

upvoted for bothering to write the whole query and pointing out the incongruity around the manual joins. while we're providing free tutoring that can easily be obtained elsewhere, I'd point out that "names" are formally called identifiers. and identifiers can be delimited by the ANSI standard "double quotes" or the MySQL (and some other dialects) -specific ` backticks `.

user3081581 Over a year ago

@ThorstenKettner Thank you very much... but the car_id with the empty car_features is blank...

Thorsten Kettner Over a year ago

c.car_id should have a value, f.car.id should be null. I don't know how MySQL handles c.* and f.*. Does it show both car_id columns or just one? If it shows just one then it seems to decide for the wrong one. Simply name the columns you want to see explicitly, rather than using *; that should solve the issue.

|

Collectives™ on Stack Overflow

php mySQL group concat and group by

2 Answers 2

2 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related