Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
How to insert millions of rows to the database with PHP (YT Tutorial)
<?php
class Stock {
public function store_stock_history()
{
$date = date('Y-m-d');
$files = File::files(app_path() . '/resources/historical_lists/' . $date);
$completed_stocks = DB::table('stocks')
->select('symbol')
->where('history_updated', $date)
->groupBy('symbol')
->orderBy('symbol', 'asc')
->get();
$completed_symbols = array_pluck($completed_stocks, 'symbol');
foreach ($files as $file)
{
$symbol = basename($file, '.csv');
if (in_array($symbol, $completed_symbols)) continue;
$handle = fopen($file, "r");
while( ! feof($handle))
{
// the stock's daily summary (closing price, volume, etc.)
$summary = fgetcsv($handle);
// 1. continue to next iteration if it's the header
// 2. continue to next iteration if there isn't seven elements (invalid record)
if ($summary[0] == 'Date' || count($summary) !== 7) continue;
// if the date is less than today, we've already stored it, break out of the loop
if (remove_whitespace($summary[0]) < $date) break;
DB::table('summaries')->insert([
'date' => remove_whitespace($summary[0]),
'symbol' => remove_whitespace($symbol),
'open' => remove_whitespace($summary[1]),
'high' => remove_whitespace($summary[2]),
'low' => remove_whitespace($summary[3]),
'close' => remove_whitespace($summary[4]),
'adjusted_close' => remove_whitespace($summary[6]),
'volume' => remove_whitespace($summary[5]),
'updated_at' => new Datetime,
]);
print "Inserted: " . $symbol . ". Date: " . $summary[0] . PHP_EOL;
}
fclose($handle);
DB::table('stocks')
->where('symbol', $symbol)
->update([
'history_updated' => $date,
]);
print "--------------------------------". PHP_EOL;
print "Finished inserting for: " . $symbol . PHP_EOL;
print "--------------------------------". PHP_EOL;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment