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 :

xx 2
xy 4
zz 1

ze 10

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

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);

$num = mysql_num_rows($result);
$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:

    $query = "SELECT * from transfer_check ORDER BY store_t";
    $result = mysql_query($query);
    while ( $row = mysql_fetch_assoc($query) ) {
       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>";
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download