Skip to content

Instantly share code, notes, and snippets.

@soardex
Created May 5, 2014 16:48
Show Gist options
  • Save soardex/22331ffd44cf7108dbb8 to your computer and use it in GitHub Desktop.
Save soardex/22331ffd44cf7108dbb8 to your computer and use it in GitHub Desktop.
A PHP script to get CVS from remote host then save to a MySQL database.
<?php
$mysqli = new mysqli("localhost", "root", "toor", "khfinance");
if ($mysqli->connect_errno) {
print("Failed to connect to MySQL: " . $mysqli->connect_error);
}
$query = "SELECT * FROM khf_symbols";
if ($result = $mysqli->query($query)) {
while ($row = $result->fetch_assoc()) {
$filename = "http://download.finance.yahoo.com/d/quotes.csv?s=" . $row["c_symbol"] . "&f=sl1d1t1c1ohgv&e=.csv";
$delimeter = ",";
if (($handle = fopen($filename, "r")) !== FALSE) {
print($filename . "\n");
//while (!feof($handle)) {
// $readline = fgets($handle, 1000);
// print "Data: " . $readline;
//}
while (($content = fgetcsv($handle, 1000, $delimeter)) !== FALSE) {
$td = explode("/", $content[2]);
$tde = $td[2] . "-" . $td[0] . "-" . $td[1];
$set = function($c) {
return ($c === "N/A" || $c === "n/a") ? 0, $c;
}
if (($stmt = $mysqli->prepare("INSERT INTO khf_updated_quotes (s_opening_price, s_closing_price, s_highest_price, s_lowest_price, s_volume_share, s_transact_date) VALUES (?, ?, ?, ?, ?, ?)"))) {
if (!$stmt->bind_param(
"ddddis",
$content[5],
$content[1],
$content[6],
$content[7],
$content[8],
$tde
)) {
print("Binding failed: " . $stmt->error);
}
if (!$stmt->execute()) {
print("Execute failed: " . $stmt->error);
}
}
print_r($content);
}
fclose($handle);
}
}
$result->free();
}
$mysqli->close();
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment