PMG Digital Made for Humans

How to Insert Only if a Condition is Met in SQL and PHP

3 MINUTE READ | October 23, 2013

How to Insert Only if a Condition is Met in SQL and PHP

Author's headshot

Christopher Davis

Christopher Davis has written this article. More details coming soon.

Let’s say you’re building a system that stores bids (for an ad, for an auction, doesn’t matter) from an external source. Each bid is a value, item/product ID, and timestamp.

The external source of the bid doesn’t push updates. Your program will have to go fetch them. However, the bid source only gives you the latest bids. No linear history, which is what your program will do.

The process is pretty simple: every X minutes your program will go fetch all the bids and insert them into your local bids table if they have a later timestamp than the current stored bid.

for bid in fetch_bids()    if (bid.timestamp > fetch_item_bid(bid.item_id).timestamp)        insert_bid(bid)    endifendfor

How would you do this with just a single SQL query? You’ll have to use an INSERT [...] SELECT query with a bit of extra magic.

CREATE TABLE bids (

  ID INT UNSIGNED NOT NULL AUTO_INCREMENT,    item_id INT UNSIGNED NOT NULL,    bid_value DECIMAL(10, 2) NOT NULL,    bid_date DATETIME NOT NULL,    PRIMARY KEY (ID)) Engine=InnoDB;

Inserting a bid is very simple:

INSERT INTO bids (item_id, bid_value, bid_date) VALUES (1, 20.00, '2013-10-18 10:30:00');

We’ll keep the INSERT part of the above query intact as we continue on. To insert only if the timestamp (bid_date in the table above) is later than the stored timestamp, we’ll need to use a SELECT statement. That said, we don’t really want to select anything: we already have our values to insert from the external source. Instead of using a field list, we’ll use real values in the SELECT part of our query:

INSERT INTO bids (item_id, bid_value, bid_date) SELECT 1, 20.00, '2013-10-18 10:30:00';

With the SELECT in place, we can add FROM, WHERE, and HAVING clauses:

INSERT INTO bids (item_id, bid_value, bid_date) SELECT 1, 20.00, '2013-10-18 10:30:00' FROM bids WHERE item_id = 1 HAVING MAX(bid_date) < '2013-10-18 10:30:00';

The above is a bit strange but makes more sense if you look only at the SELECT part. If the WHERE and HAVING conditions are not met the result set will be empty. If that happens, nothing gets selected — not even the static values — and nothing is inserted. Since we want to check the bids table, that’s where we’ll select from.

There are limitations to this method, of course. The biggest one being that if item_id wasn’t already in the table the WHERE clause will fail and nothing will be inserted. To get around that your program might need to select all item ID’s present and insert if a bid is encountered without an existing item ID:

existing_ids = get_existing_item_ids()

for bid in fetch_bids()    if bid.item_id not in existing_ids        insert_bid(bid)        continue    endif

endfor

The above will even work with bound parameters. Here’s quick example in PHP using Doctrine DBAL.

Stay in touch

Bringing news to you

Subscribe to our newsletter

By clicking and subscribing, you agree to our Terms of Service and Privacy Policy

<?php
// require whatever files you need...
require __DIR__ . '/vendor/autoload.php';

$config = new DoctrineDBALConfiguration();
$params = array(
    'dbname'    => 'is_tut',
    'user'      => 'root',
    'password'  => null,
    'host'      => 'localhost',
    'driver'    => 'pdo_mysql',
);

$conn = DoctrineDBALDriverManager::getConnection($params, $config);

$res = $conn->executeUpdate(
    'INSERT INTO bids (item_id, bid_value, bid_date)'
    . ' SELECT :item_id, :bid_value, :bid_date'
    . ' FROM bids'
    . ' WHERE item_id = :item_id'
    . ' HAVING MAX(bid_date) < :bid_date',
    array(
        'item_id'   => 1,
        'bid_value' => 20.05,
        'bid_date'  => '2013-10-18 10:45:00'
    )
);

echo 'Inserted ', $res, ' rows!', PHP_EOL;


Related Content

thumbnail image

AlliPMG CultureCampaigns & Client WorkCompany NewsDigital MarketingData & Technology

PMG Innovation Challenge Inspires New Alli Technology Solutions

4 MINUTES READ | November 2, 2021

thumbnail image

Applying Function Options to Domain Entities in Go

11 MINUTES READ | October 21, 2019

thumbnail image

My Experience Teaching Through Jupyter Notebooks

4 MINUTES READ | September 21, 2019

thumbnail image

Working with an Automation Mindset

5 MINUTES READ | August 22, 2019

thumbnail image

3 Tips for Showing Value in the Tech You Build

5 MINUTES READ | April 24, 2019

thumbnail image

Testing React

13 MINUTES READ | March 12, 2019

thumbnail image

A Beginner’s Experience with Terraform

4 MINUTES READ | December 20, 2018

ALL POSTS