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;