CODEWITHSUNDEEP
phphtmlmysql
user90210
user90210

Reputation: 115

GROUP_CONCAT is not returning output as expected

I have a page where i can view all 'proposals' with things called 'tags' similar concept to how stack overflow has tags. Before using Group_Concat a new row in the table was being added for every new tag entered for that proposal which results in a lot of duplicate data as shown below:

table showing duplicate rows

even after using distinct it shows the same row because the tag is different. I am trying to get these tags to exist in the same column so it would be like E-Commerce, 'Personalisation' etc and show it in one row. I was informed GROUP_CONCAT would be the solution however after using that, it seems to return one row with all the tags like below:

All tags in one row

I really am not sure how to overcome this issue. any help would be much appreciated.

This is the code for the sql query:

$stmt = $db_conx->prepare('SELECT p.proposal_id, p.proposal_title, p.description, c.course_title, GROUP_CONCAT(DISTINCT t.tag_title)  FROM proposal p LEFT JOIN proposal_tags pt on pt.proposal_id = p.proposal_id LEFT JOIN tag_details t on t.tag_code = pt.tag_code LEFT JOIN course_details c on c.course_code = p.course_code');
    $stmt->execute();

    $proposals = $stmt->fetchAll(PDO::FETCH_ASSOC);

and this is the table:

  <h4><center>View All Proposals</center></h4>
  <div class = "container container-fluid"id = "table_container" style="width:auto; margin-top:50px;" class="mainbox col-md-6">
    <div class="row clearfix">
      <div class="col-md-12">
        <table class="table table-bordered table-hover" id="tab_logic">
          <thead>
            <tr >
              <th class="text-center">
                Proposal ID
              </th>
              <th class="text-center">
                Proposal Title
              </th>
              <th class="text-center">
                Proposal Description
              </th>
              <th class="text-center">
                Course
              </th>
              <th class="text-center">
                Tags
              </th>
            </tr>
          </thead>
          <tbody>
            <!-- populating the table with information from mysql database -->
            <?php foreach ($proposals as $proposal) {
              echo "<tr><td>";
              echo $proposal['proposal_id'];
              echo "</td><td>";
              echo substr($proposal['proposal_title'],0,30) ."...";
              echo "</td><td>";
              echo substr($proposal['description'],0,50) ."...";
              echo "</td><td>";
              echo $proposal['course_title'];
              echo "</td><td>";
              echo $proposal['GROUP_CONCAT(DISTINCT t.tag_title)'];
              echo "<tr><td>";
</tbody>
              </table>
            </div>
          </div>
        </div>

second query attempted:

('SELECT p.proposal_id, p.proposal_title, p.description, c.course_title, pt.*, GROUP_CONCAT(DISTINCT t.tag_title) AS tags  FROM proposal p 
      LEFT JOIN course_details c on c.course_code = p.course_code
      LEFT JOIN proposal_tags pt on pt.proposal_id = p.proposal_id
      LEFT JOIN tag_details t on t.tag_code = pt.tag_code
      WHERE pt.tag_code IN ('.implode(',', $tag).')
      GROUP BY p.proposal_id ');

Upvotes: 1

Views: 119

Answers (1)

gen_Eric
gen_Eric

Reputation: 227310

You need to add GROUP BY p.proposal_id to the end of your query.

Otherwise, MySQL just groups all results into one row.

SELECT p.proposal_id, p.proposal_title, p.description, c.course_title,
    GROUP_CONCAT(DISTINCT t.tag_title) AS tags
FROM proposal p
LEFT JOIN proposal_tags pt on pt.proposal_id = p.proposal_id
LEFT JOIN tag_details t on t.tag_code = pt.tag_code
LEFT JOIN course_details c on c.course_code = p.course_code
GROUP BY p.proposal_id

P.S. You can do GROUP_CONCAT(DISTINCT t.tag_title) AS tags, then you can do $proposal['tags'].

Upvotes: 2

Related Questions