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?

 

usl_id | usl_ups_id | usl_name | usl_attention_name | usl_address | usl_address_2 | usl_address_3 | usl_city | usl_state | usl_postal | usl_country | usl_residence_indicator

--------+------------+----------+--------------------+------ -------+---------------+---------------+----------+--------- --+------------+-------------+-------------------------

277 | 7X7364 | | | | | | DERMOTT | AR | | |

278 | 7X7364 | | | | | | DERMOTT | AR | | |

(2 rows)

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);
      
    }

[MmmVomit]

Is there a specific field in the table that is supposed to be unique?

[smilesmita]

may the address and usl_ups_id

[Barand]

define unique key on usl_ups_id