Ryahn Ryahn - 3 months ago 7
MySQL Question

Displaying multiple records in one row with duplicate user id

I have a table setup to where awards are tracked by a user id. There will be multiple records with the same user id but different awards.

Current database

+---------+----------+---------------------+
| user_id | award_id | award_date |
+---------+----------+---------------------+
| 1 | 26 | 2016-08-20 00:00:00 |
| 1 | 27 | NULL |
| 1 | 28 | NULL |
| 1 | 29 | NULL |
| 1 | 30 | NULL |
| 1 | 31 | NULL |
| 2 | 26 | 2016-08-19 00:00:00 |
| 2 | 2 | NULL |
| 3 | 36 | NULL |
| 3 | 2 | NULL |
| 4 | 1 | NULL |
| 4 | 2 | NULL |
| 5 | 1 | NULL |
| 5 | 2 | NULL |
| 6 | 6 | 2016-08-23 23:06:48 |
| 6 | 1 | NULL |
| 2 | 20 | NULL |
| 3 | 20 | 2016-08-18 00:00:00 |
| 4 | 20 | NULL |
| 5 | 20 | NULL |
+---------+----------+---------------------+


The current code I am using is not able to display the records to the user. It will only grab a single result. I have tried removing the GROUP BY but it needs to keep all the records to one row with the multiple results.

$sql = "SELECT * FROM awards ORDER BY id ASC";
$results = mysqli_query($con, $sql);
$awards = array();
while ( $row = mysqli_fetch_assoc($results) )
{
$awards[] = $row;
}

$asql = "SELECT * FROM ( awards, rosters )
inner join ranks on ranks.id=rosters.rankid
inner join user_awards on user_awards.award_id=awards.id
where rosters.ruser_id = user_awards.user_id AND rosters.rplatoon='viking'
GROUP BY rosters.rname
ORDER BY rosters.rname";
$aresults = mysqli_query($con, $asql);
if(!$aresults and $mysqliDebug) {
echo "<p>There was an error in query:". $aresults."</p>";
echo $con->error;
}

$uawards = array();
while ( $arow = mysqli_fetch_assoc($aresults) )
{
$uawards[] = $arow;
}
?>

<table >
<thead>
<tr>
<th>Rank/Name</th>
<?php foreach ($awards as $award)
{
if ($award['category'] == 'medal')
{
echo '<th style="text-align:center;">
<div class="tooltip4">
<div id="award-'. $award['image_name'] . '3">&nbsp;</div>
<span class="tooltiptext4">'. $award['award_name'] .'</span>
</div>
</th>';
}
}
?>


</tr>
</thead>
<tbody>
<?php foreach ($uawards as $users )
{
echo '<tr><td>' . $users['name'] . ' '. $users['rname'] . '</td>';
$medal = 0;
foreach ($awards as $award)
{
if ( $award['category'] == 'medal')
{
if ( $award['id'] == $users['award_id'])
{
$awardid = $users['award_id'];
if ( $awardid == 26 )
{
echo '<td>True</td>';
}
elseif ( $awardid = 27 )
{
echo '<td>True</td>';
}
elseif ( $awardid = 28 )
{
echo '<td>True</td>';
}
elseif ( $awardid = 29 )
{
echo '<td>True</td>';
}
elseif ( $awardid = 30 )
{
echo '<td>True</td>';
}
elseif ( $awardid = 31 )
{
echo '<td>True</td>';
}
elseif ( $awardid = 32 )
{
echo '<td>True</td>';
}
elseif ( $awardid = 33 )
{
echo '<td>True</td>';
}
elseif ( $awardid = 34 )
{
echo '<td>True</td>';
}
elseif ( $awardid = 35 )
{
echo '<td>True</td>';
}
elseif ( $awardid = 36 )
{
echo '<td>True</td>';
}
elseif ( $awardid = 37 )
{
echo '<td>True</td>';
}
elseif ( $awardid = 38 )
{
echo '<td>True</td>';
}
elseif ( $awardid = 39 )
{
echo '<td>True</td>';
}
elseif ( $awardid = 40 )
{
echo '<td>True</td>';
}
elseif ( !$awardid )
{
echo '<td>True</td>';
}
}
else
{
echo '<td>False</td>';
}
}
}
echo '</tr>';
}
?>
</tbody>
</table>


Here is the result

enter image description here

I hope I worded this correctly.
Thanks in advanced :)

Answer
 $asql =   SELECT user_id,GROUP_CONCAT(award_id SEPARATOR ',') as awards FROM user_awards
    GROUP BY user_id
$aresults = mysqli_query($con, $asql);

Give 1 row in a comma separated(In awards 26,27,28,29,30,31 against user_Id 1) use explode function and Array in

   <table >
            <thead>
                <tr>
                    <th>Rank/Name</th>
                     <?php foreach ($awards as $award)
             {
                 if ($award['category'] == 'medal')
                 {
                    echo '<th style="text-align:center;">
                              <div class="tooltip4">
                                  <div id="award-'. $award['image_name'] . '3">&nbsp;</div>
                                  <span class="tooltiptext4">'. $award['award_name'] .'</span>
                              </div>
                          </th>';
                  }   
              }
             ?>


                  </tr>
              </thead>


    <tbody>
    while($arow = mysqli_fetch_assoc($aresults)
    {
<tr>
       $award=explode(',',$arow['awards'])
?><td><?php
       if (in_array("26", $award)) echo "true"; 
       else  echo "false"; 
?></td>
<td><?php
       if (in_array("27", $award)) echo "true"; 
       else  echo "false"; 
?></td>
   </tr> 
    }?>

this an example of two columns more add in while loop <td><?php if (in_array("award_id", $award))replace award_ids to your table Ids echo "true"; else echo "false"; ?></td>

Comments