Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006
    Posts
    10

    Question Unanswered: result duplications?

    Alright gang, This one has me baffled and I am hoping someone will take the time to help me figure this out. This script is selecting order numbers from a db based off of the order type and the time period that the order was placed. The table that I am searching archives every line item for each order, there is a part number field and a order number field for every record. So if there are 10 items on the order, the order number will appear 10 times in the table. At this point, I am only concerned with the order number itself and I do not want the order number to duplicate in my results from the query. Please see the code below, pay attention to the $check and the $ordernum variables. I am using the $check variable to eliminate the duplications in the while loop but for whatever reason, it does not always work. 95% of the time it works but I need it to work every time. Does anyone have any better ideas for eliminating duplications?

    <?
    $check = "";
    $countrec = 1;
    $warque = "SELECT sddoco, sddcto, sdshan, sdivd, sdrcd FROM query1 WHERE sdivd >= $from AND sdivd <= $to AND sdrcd LIKE '$warcode'";
    $result4 = odbc_exec($warcon, $warque);
    while (odbc_fetch_row($result4)){
    $ordernum = odbc_result($result4, 1);
    $ordertype = odbc_result($result4, 2);
    $shipto = odbc_result($result4, 3);
    $ivdcode = odbc_result($result4, 4);
    $reason = odbc_result($result4, 5);
    if (($ordernum != $check) AND ($ordertype != "CR") AND ($ordertype != "CV") AND ($ordertype != "CW")){
    ?>
    <tr>
    <td align="center"><font color="#666666" face="Tahoma"><?echo $countrec++?></td></font>
    <td align="center"><font face="Tahoma">
    <a href='<?print ("warorder.php?ordernum=$ordernum&ordertype=$order type&date=$date&shipto=$shipto&reason=$reason&cust name=$custname");?>'title="<?echo "$ordernum $ordertype"?>" target='_blank'><?echo "$ordernum $ordertype"?>[/url]
    </td></font>
    <td align="center"><font color="#666666" face="Tahoma"><?echo $reason?></td></font>
    <td align="center"><font color="#666666" face="Tahoma"><?echo $custname?></td></font>
    <td align="center"><font color="#666666" face="Tahoma"><?echo $date?></td></font>
    <td width="20" align="center"><font size= face="Tahoma">
    <a><button name="open" value="none" type="submit">create</button></a></font>
    <input type="hidden" name="ordernum" value='<?print ("$ordernum");?>'>
    <input type="hidden" name="ordertype" value='<?print ("$ordertype");?>'>
    <input type="hidden" name="date" value='<?print ("$date");?>'>
    <input type="hidden" name="reason" value='<?print ("$reason");?>'>
    <input type="hidden" name="month" value='<?print ("$month");?>'>
    <input type="hidden" name="year" value='<?print ("$year");?>'>
    <input type="hidden" name="shipto" value='<?print ("$shipto");?>'>
    <input type="hidden" name="custname" value='<?print ("$custname");?>'>
    </td>
    </tr>
    <?
    }
    $check = $ordernum;
    }
    odbc_close($connect);
    odbc_close($warcon);
    ?>

  2. #2
    Join Date
    Jul 2006
    Posts
    56
    Check this out:

    At this point, I am only concerned with the order number itself and I do not want the order number to duplicate in my results from the query.
    You have your answer right there. If the only thing you need is the order number, then build your query to meet your needs.

    In my experience, in many cases it's best to have more than one query to the db than to fetch data you don't need. Maybe if you query once for the order number you may be able to remove that particular field out of the next query, throu which you'll fetch whatever fields/data you may still need.

    There's another reason I do that. The DataBase software is actually hosted in the same Data Center, but in a different server, and it's much much better than the shared server where my PHP application is. So it's much faster to just let the DB handle complex queries than to sort it out with PHP/ASP/JAVA or whatever.

    So, either use "group by" or "select distinct" to narrow your results.

    Sorry for my bad English. Hope you understand what I mean.

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    168
    You may use group by or distinct keyword. This will give you the result that you need. But when you prefer to extract multiple data from a table and need to avoid duplicate you could use group by clause.
    Freelance and Technology Consultant
    -------------------
    Dreams are for ever
    -------------------

Posting Permissions

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