duplicate entry

smilesmita

this functions adds new locations in am_ups_shipment_location table after a tag in XML is detected it is pointed towards this function and this function then goes and adds the new location.

for the first time when i run the php prgoram the new location gets added.but when i refresh ,again the same location gets added but with different id[id is on autoincrement].

here is how my table looks after refresh.[you can see duplicate entry].This is what i dont wnat...i dont want a duplicate entry.
Can you please help me get rid of this duplicate entry by writing some update functions and checking against already present data in the table?

Function Parse_XML_UPS_Location($location)
{
global $db;

    $usl_fields = array (
        "usl_attention_name" => "text",
        "usl_name"           => "text",
        "usl_address"        => "text",
        "usl_address_2"      => "text",
        "usl_address_3"      => "text",
        "usl_city"           => "text",
        "usl_state"          => "text",
        "usl_postal"         => "text",
        "usl_ups_id"          => "text",
        "usl_country"        => "text" );
    $usl = array();

    if ( Is_Array($location) ) {
        foreach ( $location AS $k => $v ) {
            if ( Is_Array($v) && isset($v["TAG"]) ) {
                switch ( $v["TAG"] ) {
                case 'NAME':
                    $usl["usl_name"] = $v["VALUE"];
                    break;
                case 'ATTENTIONNAME':
                    $usl["usl_attention_name"] = $v["VALUE"];
                    break;
                case 'SHIPPERNUMBER':
                    $usl["usl_ups_id"] = $v["VALUE"];
                    break;
                case 'ADDRESS':
                     $usl["usl_address_details"]=$this->Parse_XML_UPS_Location_Address($v);

                case 'ADDRESSARTIFACTFORMAT':
                    $usl = array_merge($usl, $this->Parse_XML_UPS_Location_Address($v));
                    break;
            }
           } 
        }

    }

$r = $db->Query("SELECT usl_ups_id,usl_city,usl_state,usl_address from am_ups_shipment_location");

          for ( $i = 0; $r && $i < $r->Row_Count(); $i++ ) {
         $tuple = $r->Fetch_Row($i);
        }

echo "<pre>";

print_r($usl);
print_r($tuple);
echo "</pre>";
if(($usl[usl_city]==$tuple[usl_city])&&($usl[usl_state]==$tuple[usl_state])){
echo "city and state matched";
}

    if ( !IsBlank($usl["usl_ups_id"]) ) {
        $id = intval($db->Query11("
            SELECT MIN(usl_id)
                FROM am_ups_shipment_location
                WHERE usl_ups_id = '" . addslashes($usl["usl_ups_id"]) .
                    "';"));

    if ( $id )
            return ($id);

    }

    $wheres = array();
    $searchok = false;
    foreach ( $usl_fields AS $f => $t ) {
        $wheres[] = "COALESCE($f,'') = '" . addslashes($usl[$f]) . "'";
        if ( !IsBlank($usl[$f]) )
            $searchok = true;
    }
    if ( !$searchok ) {
        return 0;
    }
    $id = intval($db->Query11("
        SELECT MIN(usl_id)
            FROM am_ups_shipment_location
            WHERE " . join(" AND ", $wheres) . " ;"));

    if ( $id )
        return ($id);

    $id = intval($db->Query11("
        SELECT NEXTVAL('usl_id');"));

    if ( !$id )
        return 0;

    $usl["usl_id"] = $id;
    $usl_fields["usl_id"] = "id";
    echo "<pre>";
    print_r($usl);
    echo "</pre>"; 

    $this->Add_Rec("insert","am_ups_shipment_location",$usl_fields,$usl,array("usl_id" => $id));


    return ($id);

}

ahundiak

CREATE UNIQUE INDEX usl_ups_id ON am_ups_shipment_location (usl_ups_id);

Creating an unique index will ensure no dups regardless of what your code might do. Just be prepared to catch the DUPLICATE RECORD error code on inserts and updates.