CODEWITHSUNDEEP
phpsqlwordpress
Rob
Rob

Reputation: 6380

Avoid adding results to a table if it already exists

I'm currently getting the results of a query and adding them to a new table. At the moment it's just adding the results on every load.

How can I avoid it adding the results if the user_id already exists in the payment_issue table?

$results = $wpdb->get_results( "SELECT s.id, s.status FROM subscriptions as s LEFT JOIN subscriptionlog as l ON s.id=l.subscription_id WHERE l.event LIKE 'payment_error_mode_triggered %' AND l.date >= 1514764800 AND l.date <= 1569888000 and s.status = 'payment-issue'" );

foreach($results as $result){
  $customer_id = $result->id;
  $wpdb->insert("payment_issue", array(
   "user_id" => $customer_id,
  ));
}

Upvotes: 0

Views: 78

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

In MySQL, you can use on duplicate key update to avoid inserting new users.

First, you need a unique index or constraint on payment_issue:

create unique index unq_payment_issue_id on payment_issue(id);

Note: Your question doesn't specify what the name of the "user id" column is in payment_issue, so I am just using id.

Then, you should phrase your insert as insert . . . select.

Third, you should phrase this as:

INSERT INTO payment_issue (id, status)
    SELECT s.id, s.status
    FROM subscriptions s LEFT JOIN
         subscriptionlog l
         ON s.id = l.subscription_id
    WHERE l.event LIKE 'payment_error_mode_triggered %' AND
          l.date >= 1514764800 AND 
          l.date <= 1569888000 and
          s.status = 'payment-issue'
    ON DUPLICATE KEY UPDATE id = VALUES(id);

Upvotes: 1

Related Questions