SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Issue in duplicate display data

    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:

    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
    Attached Images Attached Images

  2. #2
    SitePoint Enthusiast
    Join Date
    Aug 2010
    Location
    Perundurai, Tamilnadu, India
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still it duplicates data.

    Thank you

  4. #4
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Attached Images Attached Images

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i've looked at your sample output, and i've looked at your query

    there is no duplication

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yah in database no duplicate.

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

  7. #7
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here my code and the data output:

    Code:
    <?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
    Attached Images Attached Images

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    no, no duplication, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,173
    Mentioned
    190 Post(s)
    Tagged
    2 Thread(s)
    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?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •