Page 1 of 1

getting duplicate reports from 'INNER JOIN' query

Posted: Fri Aug 26, 2005 6:48 pm
by michlcamp
I had some great help putting this code together last night and it works great. What I discovered after testing it a few times, though, was that if I have more than one order from a customer, the report it prints out shows duplicate entries for each order.

When a customer places an order, the shipping information is posted into 'customers', the actual order quantities are posted into 'orders2', along with the customer name ('realname').

I'm assuming it's because when an order is placed, the customer information is posted again as a new data row (in 'customers'). Anyway, here's the code and any help or suggestions appreciated.
Thanks in advance.

Code: Select all

@mysql_select_db($dbname) or die( "Unable to select database");
$sql = "SELECT * FROM `customers` INNER JOIN `orders2` ON (`customers`.`realname` = `orders2`.`realname`) WHERE `customers`.`realname` = '$realname2' ORDER BY orders2.date desc";
$query=$sql;
$result = mysql_query($query) or die(mysql_error());
if (is_resource($result)){
    if (mysql_num_rows($result)){
        echo "<div style=\"width:550\">";
        
        while($row = mysql_fetch_array($result, MYSQL_ASSOC)){

Posted: Fri Aug 26, 2005 7:03 pm
by feyd
if you used a order ID instead of customer name as the link between the two, it'd work (after adjusting the query to use it)

Posted: Fri Aug 26, 2005 8:54 pm
by michlcamp
I figured it would be something like that, and I'm not quite sure how to set that up...

Each of my orders has an id number, assigned when posted in an 'id' field (auto_increment). Can I link back to that somehow? How would the query look?

thanks.
mc

Posted: Fri Aug 26, 2005 9:09 pm
by feyd
because each "customer" is technically an order, use the ID from that table. Change the "realname" field in the order2 table to the same type settings as the "customer" ID. This will destroy most, if not all the field data you currently have, so beware you'll have to fix that "manually." Each order item should now have the customer ID stored in this field. The query would usually be something like:

Code: Select all

SELECT * FROM `customer` INNER JOIN `order2` ON( `customer`.`id` = `order2`.`customer` )
that's if you set the new ID link field in order2 to 'customer'

feyd

Posted: Sat Aug 27, 2005 1:58 am
by michlcamp
Thanks, Feyd. I'll do as you suggested.

the biggest consideration in the mix is that many of the customers are ordering for a medical or educational facility, sometimes ordering the same product for different departments, so their name will be posted several times with different shipping destinations or for different organizations. Then they come back to order other products as well.

Let's say you order a product today and are assigned 12 as your auto_incrementing 'id'. Is there a way to assign you the same number the next time you order something. As I'm learning php I'm amazed at how it can pretty much do whatever you want it to...for now, this is the fastest way to learn...so your help is greatly appreciated.

I'll post the code that generates the report...I think you were the one that suggested it...as you can see, the script is activated by a preceding form that posts "realname" from a "view by customer" script. Pick a customer name from an auto-generated list of names in 'customers', then the various orders placed by that customer show up, only in duplicate if the customer has more than one order in the table.
phew! thanks.

Code: Select all

$realname2 = $_POST["realname"];
include("host.php");
$dbh=mysql_connect ($host, $user, $password);
$link = $dbh;
@mysql_select_db($dbname) or die( "Unable to select database");
$sql = "SELECT * FROM `customers` INNER JOIN `orders2` ON (`customers`.`realname` = `orders2`.`realname`) WHERE `customers`.`realname` = '$realname2' ORDER BY orders2.date desc";
$query=$sql;
$result = mysql_query($query) or die(mysql_error());
if (is_resource($result)){
    if (mysql_num_rows($result)){
        echo "<div style=\"width:550\">";
        
        while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
            echo "<table><tr><td bgcolor=\"#FFFFC0\" width=400>Order Placed on <b>{$row["date"]}</b></td><td bgcolor=\"#ffffff\" width=150><b>Charge Total: {$row["total"]}</b></tr></table>";
            echo "<div style=\"width:50%; float:left\"><b>";
            if ($row["completeset"] > 0){
            echo "<span>Complete Set: {$row["completeset"]}</span>";}
            if ($row["briefguide"] > 0){
            echo "<br /><span>Brief Guide: {$row["briefguide"]}</span>";}
            if ($row["briefguide_sp"] > 0){
            echo "<br /><span>Brief Guide_SP: {$row["briefguide_sp"]}</span>";}
            if ($row["response"] > 0){
            echo "<br /><span>Response Forms: {$row["response"]}</span>";}
            if ($row["score_interp"] > 0){
            echo "<br /><span>Score_Interp Forms: {$row["score_interp"]}</span>";}
            if ($row["response_sp"] > 0){
            echo "<br /><span>Response_SP: {$row["response_sp"]}</span>";}
            if ($row["response_viet"] > 0){
            echo "<br /><span>Response_Viet: {$row["response_viet"]}</span>";}
            if ($row["bulkorder"] > 0){
            echo "<br /><span>Bulk Order: {$row["bulkorder"]}</span>";}
            if ($row["manual"] > 0){
            echo "<br /><span>PEDS Manual: {$row["manual"]}</span>";}
            echo "</b></div>";


            echo "<div style=\"width:50%; \"><span>{$row["org"]}</span>";
            echo "<br /><span>{$row["realname"]}</span>";
            echo "<br /><span>{$row["sender"]}</span>";
            echo "<br /><span>{$row["shstreet"]}</span>";
            echo "<br /><span>{$row["shstreet2"]}</span>";
            echo "<br /><span>{$row["shcity"]}</span>";
            echo "<br /><span>{$row["shstate"]}, {$row["shzip"]}</span>";
            echo "<br /><span>{$row["shcountry"]}</span></div><br /><br />";

            
            
        }
        echo "</div>";
    }
}
?>

Posted: Sat Aug 27, 2005 2:06 am
by feyd
you shouldn't try to set the same order id to each order. You'll get merged junk again. If you want to attach the order to a customer, create a customer table, link the ID from that table in with the order table. Then, all their various orders are seperatable, and each order can be itemized.