EVO 1.0.14 : How to INSERT record IF NOT EXISTS

76 Posts

shlomot Reply #1, 11 years, 10 months ago

Hello,

I'm using the script below to insert "trigger" records into a table.
Existence of such record prevents the server from running redundant activity somewhere else.

$sqlCalcStatus2 = $modx->db->query("SELECT 1 FROM `cg_celeb_calculation_status` WHERE (CelebID = '$CelebID')");
$resultCalcStatus2 = $modx->db->getRow($sqlCalcStatus2);

  if (!$resultCalcStatus2)
  {
    $fields = array('CelebID'	=> $CelebID);
    $modx->db->insert( $fields, 'cg_celeb_calculation_status' );
  }

However, I'm getting occasional and inconsistent error messages from MODx about an attempt to insert duplicate CelebID value:
« MODX Parse Error »
MODX encountered the following error while attempting to parse the requested resource:
« Execution of a query to the database failed - Duplicate entry '328887' for key 'PRIMARY' »
SQL > INSERT INTO cg_celeb_calculation_status (`CelebID`) VALUES('328887')

Can someone propose a better mean to avoid insert if record already exists?

Thanks a lot,
Shlomo [ed. note: shlomot last edited this post 11 years, 10 months ago.]

Cheers,
Shlomo Tommer

☆ A M B ☆
1,031 Posts

Jako Reply #2, 11 years, 10 months ago

I would replace 1 with *

$sqlCalcStatus2 = $modx->db->query("SELECT * FROM `cg_celeb_calculation_status` WHERE (CelebID = '$CelebID')");

Is the CelebID field auto incremented? Then don't add it to $fields.

76 Posts

shlomot Reply #3, 11 years, 10 months ago

Thanks for your feedback, Jako.

SELECT 1 is a very effective way to query the table by indices only, not needing access to the data table itself in order to retrieve values for all columns.

CelebID is a referenced value in this table and not an incremented column.

My question if actually: how would you write in MODx the following SQL Statement:

INSERT cg_celeb_calculation_status cg1 (CelebID)
WHERE NOT EXISTS (
SELECT * FROM cg_celeb_calculation_status cg2
WHERE cg1.CelebID = cg2.CelebID )

Cheers,
Shlomo Tommer

☆ A M B ☆
1,031 Posts

Jako Reply #4, 11 years, 10 months ago

$modx->db->query exists.