At the moment i'm trying to save stock data from loading csv files of yahoo. I created a database called stocks with that constitutes the ticker names of NASDAQ/NYSE/AMEX as tables ( about 6,5 K tables). the problem ist the saving of the data i get is taking way too long. I want to load an save the datas in 1 minute ticks. the loading takes me about 30 seconds and the saving about 3 mins. i have to reduce the saving time. here is the script i wrote for geting and saving the data:
include('mysql_connect.php');
$ticker=array();
$db="stocks";
$res=mysql_query("SHOW TABLES FROM $db");
while($row=mysql_fetch_array($res,MYSQL_NUM)){
$ticker[]=$row[0];
}
$ticker_length=count($ticker);
$steps=floor($ticker_length/200);
for($j=0;$j<=$steps;$j++){
$ticker_url="";
if($j<$steps){
for($i=$j*200;$i<($j+1)*200;$i++){
if($i==(($j+1)*200)-1){
$ticker_url=$ticker_url.$ticker[$i];
}else{
$ticker_url=$ticker_url.$ticker[$i]."+";
}
}
$url="http://finance.yahoo.com/d/quotes.csv?s='$ticker_url'&f=snxab2l1va2p2opm3m4ghd1t1=.csv";
$filehandle=fopen("$url","r");
while(!feof($filehandle)){
$line=fgetcsv($filehandle,1024);
if( $line[0]=="" || $line[0]==null || !isset($line[0]) ){
//echo"Leer<br/>";
}else{
$Symbol=strtolower($line[0]);
$Name=$line[1];
$LastTradePriceOnly=$line[5];
$Volume=$line[6];
$query=mysql_query("INSERT INTO $Symbol(symbol,Name,LastTradePriceOnly,Volume)
VALUES('$Symbol','$Name','$LastTradePriceOnly','$Volume')");
}
}
fclose($filehandle);
}else{
for($i=$j*200;$i<$ticker_length;$i++){
if($i==$ticker_length-1){
$ticker_url=$ticker_url.$ticker[$i];
}else{
$ticker_url=$ticker_url.$ticker[$i]."+";
}
}
$url="http://finance.yahoo.com/d/quotes.csv?s='$ticker_url'&f=snxab2l1va2p2opm3m4ghd1t1=.csv";
$filehandle=fopen("$url","r");
while(!feof($filehandle)){
$line=fgetcsv($filehandle,1024);
if( $line[0]=="" || $line[0]==null || !isset($line[0]) ){
//echo"empty<br/>";
}else{
$Symbol=strtolower($line[0]);
$Name=$line[1];
$LastTradePriceOnly=$line[5];
$Volume=$line[6];
$query=mysql_query("INSERT INTO $Symbol(symbol,Name,LastTradePriceOnly,Volume)
VALUES('$Symbol','$Name','$LastTradePriceOnly','$Volume')");
}
}
fclose($filehandle);
}
}
so now i want to know:
would be pleased by getting some help.
thanks.
i have found the solution. Mysql allows you to save your lines and cols from csv without reading the lines in complex loops:
$url = "http://finance.yahoo.com/d/quotes.csv?s=AAPL+MSFT+GOOG&f=...the datas you need=.csv";
$sql = "LOAD DATA LOCAL INFILE '" . $url . "'
INTO TABLE `" . $data_tablename . "`
FIELDS
TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES
TERMINATED BY '\n'
(`your_cols`,``,.....)";
mysql_query($sql)or die(mysql_error());
my script took me 250 sec on the server but now just 45 sec's. Amazing profit of time....