Alihamra Alihamra - 3 years ago 54
MySQL Question

PHP selecting duplicate records

I have the following mysql table :

id store_t item qty
1 M5 xx 2
2 M5 xy 4
3 M8 ze 10
4 M5 zz 1


I want the table output to be like this :

M5
xx 2
xy 4
zz 1

M8
ze 10


What i am looking for is to find a way to group and select the duplicate
store_t
and output its
item
and
qty
without repeating
store_t
.

I've tried using
GROUP BY store_t
inside mysql statement it works to group the Duplicates of store_t but only showing one record of item and qty.

Here is my code :

$query = "SELECT * from transfer_check GROUP BY store_t";
$result = mysql_query($query);
if(mysql_num_rows($result)>0){

$num = mysql_num_rows($result);
for($i=0;$i<$num;$i++){
$row = mysql_fetch_assoc($result);

//Table Created for each Store

echo "<table width=80% align=center border=2 cellpadding=3 cellspacing=0>";
echo "<tr><td align=center colspan=2>".$row['store_t']."</td></tr>";
echo "<tr><td align=center bgcolor=white><b>Item No.</b></td><td align=center
bgcolor=lightgreen><b>QTY Transferred</b></td></tr>";

echo "<tr><td align=center><b>".strtoupper($row['item_no'])."</b></td><td align=center> <b>".$row['qty']."</b></td></tr>";
echo "</table>";


//End of i loop
}

Muc Muc
Answer Source

Have you tried ordering the results by store_t and then looping through them?

Something like:

    <?php
    $query = "SELECT * from transfer_check ORDER BY store_t";
    $result = mysql_query($query);
    ?><table><?php
    while ( $row = mysql_fetch_assoc($query) ) {
    ?><tr><?php
       if ( !isset($curStore) || $curStore != $row['store_t'] ) {              
           $curStore = $row['store_t'];
           echo "<td colspan='2'>$curStore</td></tr><tr>";
       }
       echo "<td>{$row['item']}</td><td>{$row['qty']}</td>";
       ?></tr>
   }?>
</table>
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download