This website uses cookies to ensure you get the best possible experience. See our Cookies Policy.

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

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.

Insights meet inbox

Sign up for weekly articles & resources.

<?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;


Posted by Christopher Davis

Related Content

thumbnail image

Get Informed

PMG Innovation Challenge Inspires New Alli Technology Solutions

4 MINUTES READ | November 2, 2021

Get Informed

Applying Function Options to Domain Entities in Go

11 MINUTES READ | October 21, 2019

thumbnail image

Get Informed

My Experience Teaching Through Jupyter Notebooks

4 MINUTES READ | September 21, 2019

Get Informed

Trading Symfony’s Form Component for Data Transfer Objects

8 MINUTES READ | September 3, 2019

Get Inspired

Working with an Automation Mindset

5 MINUTES READ | August 22, 2019

Get Informed

Parsing Redshift Logs to Understand Data Usage

7 MINUTES READ | May 6, 2019

Get Inspired

3 Tips for Showing Value in the Tech You Build

5 MINUTES READ | April 24, 2019

thumbnail image

Get Informed

Testing React

13 MINUTES READ | March 12, 2019

Get Inspired

Tips for Designing & Testing Software Without a UX Specialist

4 MINUTES READ | March 6, 2019

Get Informed

A Beginner’s Experience with Terraform

4 MINUTES READ | December 20, 2018

All POST