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