Hi there,
Ive just written something quick to enter End of Day stock data from text file into a MYSQL database.
This is what the stock data looks like, Ive cropped it, but it runs to 3800 lines.
AAC,20101101,1.6,1.6,1.55,1.555,558449
AAD,20101101,1.08,1.08,1.04,1.055,1120721
AAE,20101101,0.011,0.011,0.011,0.011,1504065
AAG,20101101,0.295,0.295,0.255,0.275,778858
AAI,20101101,13.1,13.1,13.1,13.1,0
This is the PHP code:
//Stock Format: Code, Date, Open, High, Low, Close, Volume
//File where Stock End of Day data is stored
$lines = file('stockdata.txt');
foreach ($lines as $line_num => $line) {
$pieces = explode(",", $line);
//Where $pieces[0] = Stock Code, $pieces[1] = Date etc
$result = @mysql_query("SELECT * FROM ". $pieces[0] ."");
if (!$result) {
//No table exists - create table and update table
//Creating table in MYSQL database
@$create = mysql_query("CREATE TABLE ". $pieces[0] ." (date int(8), open float(7,3), high float(7,3), low float(7,3), close float(7,3), volume int(8) )");
//Inserting data into the new table
@$new = mysql_query("INSERT into `". $pieces[0] ."` (date, open, high, low, close, volume) values ('". $pieces[1] ."', '". $pieces[2] ."', '". $pieces[3] ."', '". $pieces[4] ."', '". $pieces[5] ."', '". $pieces[6] ."')");
} else {
//Table found - Insert data into table
@$new = mysql_query("INSERT into `". $pieces[0] ."` (date, open, high, low, close, volume) values ('". $pieces[1] ."', '". $pieces[2] ."', '". $pieces[3] ."', '". $pieces[4] ."', '". $pieces[5] ."', '". $pieces[6] ."')");
}
}
?>
This script will be run every day to update my data base for daily prices. There are 3800 lines in the text file.
How does the code look? Are there areas to optimize so it runs quicker or less resource intensive? Granted it takes only 1 second my WAMP server, could it be a problem with 250 entries a year? And how are my data type selections?