Issue in duplicate display data

PHP
1

Hi…

I encountered problem in my query and while loop my data was duplicates when I join 2 tables, because the one field that I need to display is from another table.

here is my code:


<div>
<table>
<thead>
<th>Items</th>
<th>Sub Items</th>
<th>Item Code</th>
<th>Demanded Qty</th>
<th>UoM</th>
<th>Class</th>
<th>Description</th>
<th>BIN Location</th>
</thead>
<?php
$sql = "SELECT Items FROM bom_items ORDER BY Items";
$res_bom = mysql_query($sql, $con);

while($row = mysql_fetch_assoc($res_bom)){
echo "<tr>
        <td style='border: none;font-weight: bold;'>&nbsp;$row[Items]</td>
        </tr>";
//$sql = "SELECT SubItems, ItemCode, UoM, Class, Description FROM bom_subitems WHERE Items = '$row[Items]' ORDER BY Items"or die(mysql_error());
//$sql = "SELECT DISTINCT bs.SubItems, bs.ItemCode, bs.UoM, bs.Class, bs.Description,w.BINLocation FROM bom_subitems bs LEFT JOIN wms w ON bs.Items = w.Items WHERE bs.Items = '$row[Items]' AND w.Items = '$row[Items]' ORDER BY bs.Items, w.Items"or die(mysql_error());
$sql = "SELECT DISTINCT bs.SubItems, bs.ItemCode, bs.UoM, bs.Class, bs.Description,w.BINLocation FROM bom_subitems bs, wms w WHERE bs.Items = '$row[Items]' AND w.Items = '$row[Items]' ORDER BY bs.Items, w.Items"or die(mysql_error());
$res_sub = mysql_query($sql, $con);

while($row_sub = mysql_fetch_assoc($res_sub)){
    echo "<tr>
        <td style='border: none;'>&nbsp;</td>
        <td style='border: none;'>&nbsp;$row_sub[SubItems]</td>
        <td style='border: none;'>&nbsp;$row_sub[ItemCode]</td>
        <td>&nbsp;</td>
        <td style='border: none;' size='3'>&nbsp;$row_sub[UoM]</td>
        <td style='border: none;'>&nbsp;$row_sub[Class]</td>
        <td style='border: none;'>&nbsp;$row_sub[Description]</td>
        <td style='border: none;'>&nbsp;$row_sub[BINLocation]</td>
        </tr>";

}
}
?>

I will attach my sample page.

Thank you

2

I think you are missing mapping between two tables. Below code might avoid duplicate records

$sql = "SELECT DISTINCT bs.SubItems, bs.ItemCode, bs.UoM, bs.Class, bs.Description,w.BINLocation FROM bom_subitems bs, wms w WHERE bs.Items = w.Items AND w.Items = ‘$row[Items]’ ORDER BY bs.Items, w.Items"or die(mysql_error());

3

Still it duplicates data.

Thank you

4

Here is the tables:

bom_subitems
Items
SubItems
ItemCode
UoM
Class
Description

wms
Items
BINLocation

I think I have problem in my query

I attach the data from the database.

Thank you

5

i’ve looked at your sample output, and i’ve looked at your query

there is no duplication

:slight_smile:

6

yah in database no duplicate.

but when I used the query and while loop it duplicated.

7

here my code and the data output:


<?php
   error_reporting(0);
   date_default_timezone_set("Asia/Singapore"); //set the time zone
$con = mysql_connect('localhost', 'root','');

if (!$con) {
    echo 'failed';
    die();
}

mysql_select_db("mes", $con);
$sr_date =date('Y-m-d H:i:s');

$sql = "SELECT sr_number FROM stock_requisition ORDER BY sr_date DESC LIMIT 1";
        $result = mysql_query($sql, $con);
        if (!$result) {
            echo 'failed';
            die();
        }
        $total = mysql_num_rows($result);
        if ($total <= 0) {
            $currentSRNum = 1;
        }
        else {
//------------------------------------------------------------------------------------------------------------------
            // Stock Number iteration....
            $row = mysql_fetch_assoc($result);

            $currentSRNum = (int)(substr($row['sr_num'],0,3));

            $currentSRYear  = (int)(substr($row['sr_num'],2,2));
            $currentSRMonth = (int)(substr($row['sr_num'],0,2));

            $currentYear  = (int)(date('y'));
            $currentMonth = (int)(date('m'));
            $currentDay = (int)(date('d'));


            if ($currentYear == $currentSRYear) {
                if ($currentMonth == $currentSRMonth) {
                    $currentSRNum = $currentSRNum + 1;
                }
                if ($currentMonth > $currentSRMonth) {
                    $currentSRNum = 1;
                }
                if ($currentDay > $currentSRDay) {
                    $currentSRNum = 1;
                }
            }
            if ($currentYear > $currentRefYear) {
                $currentSRNum = 1;
            }
        }
//------------------------------------------------------------------------------------------------------------------
        $yearMonth = date('ymd');
        $currentSR = $yearMonth . sprintf("%03d", $currentSRNum);
?>
<html>
<title>Stock Requisition</title>
<head>
</head>
<body>
<div id="ddcolortabs">
<ul>
<li> <a href="ParameterSettings.php" title="Parameter Settings"><span>Parameter Settings</span></a></li>
<li id="current"><a href="StockRequisition.php" title="Stock Requisition"><span>Stock Requisition</span></a></li>
<li style="margin-left: 1px"><a href="kanban_report.php" title="WIP Report"><span>Wip Report</span></a></li>
</ul>
</div>
<div id="SR_date">
<label>Date :</label>
<input type="text" name="sr_date" value="<?php echo $sr_date; ?>" size="16" readonly="readonly">
</div>
<div id="SR_number">
<label>SR# :</label>
<input type="text" name="sr_number" value="<?php echo $currentSR; ?>" size="8" readonly="readonly" style="font-weight: bold;">
<br/>
</div>
<div>
<table>
<thead>
<th>Items</th>
<th>Sub Items</th>
<th>Item Code</th>
<th>Demanded Qty</th>
<th>UoM</th>
<th>Class</th>
<th>Description</th>
<th>BIN Location</th>
</thead>
<?php
//$sql = "SELECT Items FROM bom_items ORDER BY Items";
$sql = "SELECT DISTINCT Items FROM bom_subitems ORDER BY Items";
$res_bom = mysql_query($sql, $con);

while($row = mysql_fetch_assoc($res_bom)){

    $Items = $row['Items'];
echo "<tr>
        <td style='border: none;font-weight: bold;'>&nbsp;$row[Items]</td>
        </tr>";

//$sql = "SELECT SubItems, ItemCode, UoM, Class, Description, BINLocation FROM bom_subitems WHERE Items = '$row[Items]' ORDER BY Items"or die(mysql_error());

//$sql = "SELECT DISTINCT bs.SubItems, bs.ItemCode, bs.UoM, bs.Class, bs.Description,w.BINLocation FROM bom_subitems bs LEFT JOIN wms w ON bs.Items = w.Items WHERE bs.Items = '$row[Items]' AND w.Items = '$row[Items]' ORDER BY bs.Items, w.Items"or die(mysql_error());
$sql = "SELECT  bs.SubItems, bs.ItemCode, bs.UoM, bs.Class, bs.Description,w.BINLocation FROM bom_subitems bs, wms w WHERE bs.Items = '$row[Items]' AND w.Items = '$row[Items]' ORDER BY bs.Items, w.Items"or die(mysql_error());
//$sql = "SELECT COUNT (DISTINCT , bs.SubItems, bs.ItemCode, bs.UoM, bs.Class, bs.Description,w.BINLocation) FROM bom_subitems bs, wms w WHERE bs.Items = '$row[Items]' AND w.Items = '$row[Items]' ORDER BY bs.Items, w.Items";
//$sql = "SELECT bs.SubItems, bs.ItemCode, bs.UoM, bs.Class, bs.Description,w.BINLocation FROM bom_subitems bs, wms w WHERE bs.Items = w.Items AND w.Items = '$row[Items]' AND bs.Items = '$row[Items]' ORDER BY bs.Items, w.Items"or die(mysql_error());
$res_sub = mysql_query($sql, $con);
 while($row_sub = mysql_fetch_assoc($res_sub)){

    echo "<tr>
        <td style='border: none;'>&nbsp;</td>
        <td style='border: none;'>$row_sub[SubItems]</td>
        <td style='border: none;'>&nbsp;$row_sub[ItemCode]</td>
        <td style='border: none;'><center><input type='text' name='DemandedQty' id='DemandedQty' value='' size='7'></center></td>
        <td style='border: none;' size='3'>&nbsp;$row_sub[UoM]</td>
        <td style='border: none;'>&nbsp;$row_sub[Class]</td>
        <td style='border: none;'>&nbsp;$row_sub[Description]</td>
        <td style='border: none;'>&nbsp;$row_sub[BINLocation]</td>
        </tr>";

}
}

?>
</table>
</div>
</body>
</html>

Thank you

8

no, no duplication, sorry

9

Yes, each complete row looks unique to me as well, though some portions of a row appear in other rows. Which are you saying are the replicates?