• About Us
    • New York
  • Work
  • Capabilities
  • Careers
  • Technology
  • Blog
  • Contact Us
  • About Us
    • New York
  • Work
  • Capabilities
  • Careers
  • Technology
  • Blog
  • Contact Us
October 23, 2013

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

Posted by Christopher Davis

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)
    endif
endfor

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.

Set Up Your Schema

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;

Evolving The Insert

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

    maybe_insert_bid(bid) // the SQL from above
endfor

Working With Bound Parameters

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

<?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;
bidsDevelopmentEngineeringPHPSchemaSQL
Previous
Next

Latest White Papers

  • Shifting Plans for 2020 & Beyond
  • Game On: How Brands Can Log Into A Diverse Multi-Billion Dollar Industry
  • What CCPA Means For Brands
  • How Google is Improving Consumer Data Privacy
  • Ways to Prepare for the Cookieless Future
  • See all White Papers

Featured Posts

  • Ad Age Names PMG #1 Best Place to Work in 2021
  • MediaPost Names PMG Independent Agency of the Year
  • PMG Client Portfolio Trends During Amazon Prime Day 2020
  • A Closer Look at the Congressional Big Tech Market Power Report
  • What to Know About Reddit

Categories

  • Consumer Insights
  • Content
  • Creative Design
  • Data Analytics
  • Development
  • Digital TV & Video
  • Ecommerce
  • Industry News
  • Local
  • Mobile
  • Paid Search
  • PMG Culture
  • Programmatic & Display
  • SEO
  • Social Media
  • Structured Data
Fort Worth

2845 West 7th Street
Fort Worth, TX 76107

Dallas

3102 Oak Lawn Avenue
Suite 650
Dallas, TX 75219

Austin

823 Congress Avenue
Suite 800
Austin, TX 78701

London

33 Broadwick Street
London
W1F 0DQ

New York

120 East 23rd Street
New York, NY 10010

Get in touch

(817) 420 9970
info@pmg.com

Subscribe to the PMG Newsletter
© 2021 PMG Worldwide, LLC, All Rights Reserved
  • Contact
  • Privacy Policy
 Tweet
 Share
 Tweet
 Share
 Tweet
 Share
 LinkedIn
We and our partners use cookies to personalize content, analyze traffic, and deliver ads. By using our website, you agree to the use of cookies as described in our Cookie Policy.