1

I wish not to insert a record if the record already is in my mysql database

I have a MySQL query that inserts sales data into my database

I run a script that gathers all my sales for the past day/or week

but sometimes I will have a query that will have the same/ duplicate sales information in it and I do not wish to have that query entered into my database, I just want to skip past it

the field 'SalesRecord' is unquie for each sales record, ie 7343, what would be the simplest way to write a sql query to skip past a sales record if the sales record already exists in my database

heres my sql

// NOW ADD THE SALES DATA TO EBAYSALESLISTINGS DATABASE
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
$sql = "INSERT INTO `ebaysaleslistings`(`SalesRecord`, `OrderID`, `Order`, `AmountPaid`, `SalesTaxAmount`, `ExternalTransactionID`, ".
   "`ExternalTransactionTime`, `ExternalFeeOrCreditAmount`, `ExternalTransactionPaymentOrRefundAmount`, `ShippingServiceSelected`, `ShippingServiceCost`, `ShippedTime`, ".
   "`Name`, `Street1`, `Street2`, `CityName`, `PostalCode`, `StateOrProvince`, `CountryName`, `Phone`, `OrderLineItemID`, `EbayItemID`, ".
   "`EbayFees`, `Title`, `QuantityPurchased`, `BuyerEmail`, `TransactionID`, `TransactionPrice`, `Platform`) ".

   "VALUES ('$elm_SaleRecordID','$elm_OrderID','$elm_OrderStatus','$AmountPaid','$SalesTaxAmount','$externalTransaction->ExternalTransactionID','$externalTransaction->ExternalTransactionTime','$externalTransaction->FeeOrCreditAmount','$externalTransaction->FeeOrCreditAmount', ".
   "'$ShippingServiceSelected->ShippingService','$ShippingServiceSelected->ShippingServiceCost','$order->ShippedTime','$shippingAddress->Name','$shippingAddress->Street1','$shippingAddress->Street2','$shippingAddress->CityName','$shippingAddress->PostalCode', ".
   "'$shippingAddress->StateOrProvince','$shippingAddress->CountryName','$shippingAddress->Phone','$transaction->OrderLineItemID','$elm_EbayItemID', ".
   "'$elm_EbayFees','$elm_title','$transaction->QuantityPurchased','$elm_email','$transaction->TransactionID','$transaction->TransactionPrice','$transaction->Platform')";

echo "<br><br>" . $sql . "<br><br>";

mysql_select_db('ebaylistingmanager');
$retval = mysql_query( $sql, $conn );
xop32
  • 93
  • 1
  • 3
  • 12
  • you should be getting an error if you're trying to insert with a duplicate unique key. to avoid that use `IGNORE` http://dev.mysql.com/doc/refman/5.1/en/insert.html. If your table isn't set up to have the SalesRecord as a unique key, then you need to do that. – ElefantPhace Dec 28 '14 at 21:58
  • 3
    possible duplicate of [How to 'insert if not exists' in MySQL?](http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) – slavoo Dec 28 '14 at 21:59
  • You should also highly consider switched over to either mysqli_* or using PDO. mysql_* are deprecated and offer serious security risks – ElefantPhace Dec 28 '14 at 22:00

2 Answers2

1

If your column SalesRecord is set to be unique, then you will get an insert error. if not, alter the table and make the column unique. Then simply use this syntax:

$sql = "INSERT IGNORE INTO `ebaysaleslistings` ......";

Note the IGNORE after the INSERT. This will make MySQL ignore any errors.

And please switch to PDO or mysqli_* methods. mysql_* methods have been deprecated.

kRiZ
  • 2,137
  • 4
  • 25
  • 38
1

I would not use the Insert ignore option as this will ignore other errors,

The most efficient way to do this, assuming when you say unique for that field you mean the database actually has a unique key setup properly is to use PDO, and just ignore the exception that is thrown for the duplicate key error its like code 23000 or such I'd have to look it up.

$PDO = new \PDO(
    "mysql:host=$dbHost;dbname=$dbName",
    $dbUser,
    $dbPass
);
//set pdo error mode to throw exceptions.  So we can in turn catch them :)
$this->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

try{
   //obviously use your query here.
   $stmt = $PDO->prepare( '
      INSERT INTO
           table
      ( field1, field2 ) VALUES ( :value1, :value2 )
  ');
    $stmt->execute( array( ':value1'=>1, ':value2' => 2) );

}catch( PDOException $e ){
    if( $e->getCode() != 23000 ){
       //if not code 23000 die with error message output - 23000 is duplicate key
       die( "<pre>($e->getMessage()}\n\n{$e->getTraceAsString()}");
    }
}

This is kind of off the top of my head but it should point you in the right direction. Of course and even better solution is to avoid the issue all together by not inserting data that is duplicate. But I would need more specifics on how you get the duplicate data to really take a crack at that.

Further reading

http://php.net/manual/en/book.pdo.php

http://php.net/manual/en/class.pdoexception.php

An added benefit to using PDO, besides SQL injection prevention is the use of prepare and execute in a loop. This shouldn't be understated because you run it multiple times more efficiently and cleaner then using mysql alone.

      $stmt = $PDO->prepare( '
         INSERT INTO
           table
         ( field1, field2 ) VALUES ( :value1, :value2 )
     ');

      foreach( $inputs as $values ){
         try{
              $stmt->execute( $values );
         }catch( PDOException $e ){
              if( $e->getCode() != 23000 ){
              //if not code 23000 die with error message output - 23000 is duplicate key
                  die( "<pre>($e->getMessage()}\n\n{$e->getTraceAsString()}");
              }
          }
      }

This way you are not regenerating the query on each and every loop of the input. However, you want the try catch block just around the actual execution of the query or it will "kick" you out of the loop.

ArtisticPhoenix
  • 20,683
  • 2
  • 18
  • 34