Skip to content

Instantly share code, notes, and snippets.

@thewinterwind

thewinterwind/insert_many_rows.php

Last active December 12, 2016 13:59
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save thewinterwind/e9ae660189259a4f2334 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save thewinterwind/e9ae660189259a4f2334 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. thewinterwind revised this gist Jun 21, 2014. 1 changed file with 61 additions and 56 deletions.
    117 changes: 61 additions & 56 deletions insert_many_rows.php
    View file
    Original file line number Diff line number Diff line change
    @@ -1,63 +1,68 @@
    public function store_stock_history()
    {
    $date = date('Y-m-d');
    <?php

    $files = File::files(app_path() . '/resources/historical_lists/' . $date);
    class Stock {

    $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)
    public function store_stock_history()
    {
    $symbol = basename($file, '.csv');

    if (in_array($symbol, $completed_symbols)) continue;

    $handle = fopen($file, "r");

    while( ! feof($handle))
    $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)
    {
    // 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;
    $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;
    }

    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;
    }
    }
  2. thewinterwind revised this gist Jun 21, 2014. 1 changed file with 56 additions and 56 deletions.
    112 changes: 56 additions & 56 deletions insert_many_rows.php
    View file
    Original file line number Diff line number Diff line change
    @@ -1,63 +1,63 @@
    public function store_stock_history()
    {
    $date = date('Y-m-d');
    {
    $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');

    $files = File::files(app_path() . '/resources/historical_lists/' . $date);
    foreach ($files as $file)
    {
    $symbol = basename($file, '.csv');

    $completed_stocks = DB::table('stocks')
    ->select('symbol')
    ->where('history_updated', $date)
    ->groupBy('symbol')
    ->orderBy('symbol', 'asc')
    ->get();
    if (in_array($symbol, $completed_symbols)) continue;

    $completed_symbols = array_pluck($completed_stocks, 'symbol');
    $handle = fopen($file, "r");

    foreach ($files as $file)
    while( ! feof($handle))
    {
    $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;
    // 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;
    }
    }
  3. thewinterwind created this gist Jun 21, 2014.
    63 changes: 63 additions & 0 deletions insert_many_rows.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,63 @@
    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;
    }
    }