SQL HELP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL HELP

Author

Topic

argus
Starting Member

2 Posts

Posted - 2008-05-03 : 18:53:26

I was using this php code for getting the stock quotes from yahoo finance and saving them into a database.I created the database as given below. When i run the php code It gets the data and stores it correctly into SQL database for the first time however when i run the php code again it deletes the earlier values of the stocks.Please could you help me out such that it appends to the SQL table. I am kind of new to this and i am doing it for the first time.Your help
would be highly appreciated.
Thank you.

<?PHP

/**

* Class: yahoo.stocks.class.php

*

* -----------------------------------------------------------------------------

* -----------------------------------------------------------------------------

* mySQL table structure (only needed if cache functionality is used and no active mySQL connection to $db is present)

*

* CREATE TABLE `stocks` (

* `ID` int(11) NOT NULL auto_increment,

* `stock` varchar(64) NOT NULL default '',

* `value` varchar(16) NOT NULL default '0',

* `changepoints` varchar(16) NOT NULL default '0',

* `open` varchar(16) NOT NULL default '',

* `intra_top` varchar(16) NOT NULL default '',

* `intra_down` varchar(16) NOT NULL default '',

* `date` varchar(10) NOT NULL default '',

* `time` varchar(6) NOT NULL default '',

* PRIMARY KEY (`ID`)

* ) TYPE=MyISAM AUTO_INCREMENT=1 ;

*

* -----------------------------------------------------------------------------

* -----------------------------------------------------------------------------

* Some tock keywords:

* ^DJI - Dow Jones

* ^IXIC - Nasdaq

* ^GSPC - S&P 500

* ^STOXX50E - Euro STOXX50

* For more stock keywords visit http://finance.yahoo.com

* -----------------------------------------------------------------------------

*/

class yahoo_stocks {

/**

* Time difference Between Yahoo and your local time;

*/

var $time = "+3"; //ATTENTION: daylight saving isn't (yet) respected

var $refreshtime = "5"; // 0,25 because yahoo updates their values approx every 15sec!

var $dbhost = "localhost";

var $user = "username";

var $passwd = "password";

var $db = "database_name";

var $stocks_table = "stocks";

/**

* how long should quote values be stored in mysql (0 = unlimited / otherwise time in days)

*/

var $keepfilesfor = 0;

/**

* yahoo_stocks :: get_stocks()

* :: ATTENTION - IF THE CACHE IS USED - calling this

* :: function will need an open mySQL connection to be present!

* :: use my_connect() first

*

* @param $stock Stock shortcut

* @param $cache (y or n)

* @return array ()

*/

function get_stocks($stock, $cache)

{

if ($cache == "y") {

if ($this->keepfilesfor != "0") {

//changed$deletefrom = time() - $this->keepfilesfor * 24 * 3600;

//$mystock = mysql_escape_string($stock);

//changed $q = "DELETE FROM `$this->stocks_table` WHERE stock='$mystock' ";

print("\n ********1st time \n");

print($q);

// mysql_query($q);

}

$timeout = time()-60 * $this->refreshtime;

$mystock = mysql_escape_string($stock);

$q = "SELECT * FROM `$this->stocks_table` WHERE stock='$mystock' ";

//print($mystock);

$q .= "ORDER BY `ID` DESC LIMIT 1";

//print("\n ******** 2nd time \n");

// print($q);

$d = mysql_query($q);

if (!mysql_num_rows($d)) {

$reallocaltime = time();

$return = $this->generate_stock_array($stock);

/**

* Check the mysql database to prevent double entries using the md5 hash

*/

$qquery = "SELECT * FROM `$this->stocks_table` WHERE stock='$mystock'";

$qcheck = mysql_query($qquery);

// print("\n ******** $qcheck \n");

// print($qcheck);

//print("\n ******** $qcheck \n");

//changedif (mysql_num_rows($qcheck)) {

//changed $x = mysql_fetch_array($qcheck);

//print("\n ******** $qcheck \n");

// print($qcheck);

//print("\n ******** $qcheck \n");

//changed return $x;

//changed }
//changed else
{

$sql = "INSERT INTO `$this->stocks_table` (`stock` , `value` , `changepoints` , `open` , `intra_top` , `intra_down` , `date` , `time` )";

$sql .= "VALUES('${return['stock']}','${return['value']}','${return['changepoints']}','${return['open']}','${return['intra_top']}','${return['intra_down']}',";

$sql .= "'${return['date']}','${return['time']}')";

mysql_query($sql);

return $return;

}

}
else
{

$x = mysql_fetch_array($d);

return $x;

}

} else {

return $this->generate_stock_array($stock);

}

}

/**

* yahoo_stocks::generate_stock_array()

*

* :: please don't use this function - Use get_stocks() instead

* :: this function is for internal use in get_stocks()

*

* @param $stock

* @return

*/

function generate_stock_array($stock)

{

$open = fopen("http://finance.yahoo.com/d/quotes.csv?s=$stock&f=sl1d1t1c1ohgv&e=.csv", "r");

$read = str_replace('"', '', trim(fread($open, 2000)));

$data = explode(",", $read);

fclose($open);

/**

* Converting yahoo's date to local date

*/

$strtotime_date = strtotime(str_replace('"', '', "$data[2] $data[3]"));

if (eregi("^-.*", $this->time)) {

$this->time = str_replace("-", "", $this->time);

$return['unixtime'] = $strtotime_date - $this->time * 3600;

} else {

$this->time = str_replace("+", "", $this->time);

$return['unixtime'] = $strtotime_date + $this->time * 3600;

}

$return['stock'] = $data[0];

$return['value'] = $data[1];

$return['date'] = date("j.n.Y", $return['unixtime']);

$return['time'] = date("G:i", $return['unixtime']);

$return['changepoints'] = $data[4];

$return['open'] = $data[5];

$return['intra_top'] = $data[6];

$return['intra_down'] = $data[7];

$return['md5'] = md5($read);

if ($return['value'] == "0.00" && $return['date'] == "1.1.1970") {

die("ERROR: yahoo.stocks.class.php - <b>$stock</b> is no valid stock");

}

return $return;

}

/**

* yahoo_stocks :: get_stock_bundle() :: uses an array ($symbols) to gather several stock quotes at once

*

* @param $symbols ;

* @return ;)

*/

function get_stock_bundle($syms)

{

foreach ($syms as $s) {

$bundle[$s] = $this->generate_stock_array($s);

}

return $bundle;

}

/**

* yahoo_stocks :: my_connect() :: Connect to the mySQL server

*

* @return mysql _connection

*/

function my_connect()

{

$this->conn = @mysql_connect($this->dbhost, $this->user, $this->passwd) or die("Can't connect to mySQLn");

return mysql_select_db($this->db, $this->conn);

}

/**

* yahoo_stocks :: my_close() :: Disconnect from the mySQL server

*

* @return TRUE ;)

*/

function my_close()

{

return @mysql_close();

}

}

//instantiate the class

$stocks = new yahoo_stocks();

$stocks->my_connect();//if you have no open mySQL connection (yet)

//$eurostoxx = $stocks->get_stocks("^STOXX50E", "y"); //Get data for Euro StoXX 50 and use cache (returns an array)

$apple = $stocks->get_stocks("AAPL", "y");

$cisco = $stocks->get_stocks("CSCO", "y");

$msft = $stocks->get_stocks("MSFT", "y");

$intel = $stocks->get_stocks("INTC", "y");

$nokia = $stocks->get_stocks("NOK", "y");

$oracle = $stocks->get_stocks("ORCL", "y");

$dell = $stocks->get_stocks("DELL", "y");

$canon = $stocks->get_stocks("CAJ", "y");

$texasinst = $stocks->get_stocks("TXN", "y");

$EMC = $stocks->get_stocks("EMC", "y");

$motorola = $stocks->get_stocks("MOT", "y");

$AMD = $stocks->get_stocks("AMD", "y");

$johnjohn = $stocks->get_stocks("JNJ", "y");

$pfizer = $stocks->get_stocks("PFE", "y");

$glaxo = $stocks->get_stocks("GSK", "y");

$novartis = $stocks->get_stocks("NVS", "y");

$merck = $stocks->get_stocks("MRK", "y");

$astra = $stocks->get_stocks("AZN", "y");

$northrop = $stocks->get_stocks("NOC", "y");

$amgen = $stocks->get_stocks("AMGN", "y");

$sybase = $stocks->get_stocks("SY", "y");

$abbott = $stocks->get_stocks("ABT", "y");

$wyeth = $stocks->get_stocks("WYE", "y");

$genentech = $stocks->get_stocks("DNA", "y");

$alcon = $stocks->get_stocks("ACL", "y");

$citigroup = $stocks->get_stocks("C", "y");

$bofamerica = $stocks->get_stocks("BAC", "y");

$hsbc = $stocks->get_stocks("HBC", "y");

$berkshire = $stocks->get_stocks("BRK-A", "y");

//$aig = $stocks->get_stocks("AIG", "y");

$jpmorgan = $stocks->get_stocks("JPM", "y");

$allianz = $stocks->get_stocks("AZ", "y");

$wachovia = $stocks->get_stocks("WB", "y");

$clic = $stocks->get_stocks("LFC", "y");

$goldman = $stocks->get_stocks("GS", "y");

$barclays = $stocks->get_stocks("BCS", "y");

$axa = $stocks->get_stocks("AXA", "y");

$exxon = $stocks->get_stocks("XOM", "y");

$gamestop = $stocks->get_stocks("GME", "y");

$chevron = $stocks->get_stocks("CVX", "y");

//$bp = $stocks->get_stocks("BP", "y");

$opc = $stocks->get_stocks("OXY", "y");

$impoil = $stocks->get_stocks("IMO", "y");

$chinamobile = $stocks->get_stocks("CHL", "y");

$atnt = $stocks->get_stocks("T", "y");

$vodafone = $stocks->get_stocks("VOD", "y");

$google = $stocks->get_stocks("GOOG", "y");

$walmart = $stocks->get_stocks("WMT", "y");

$verizon = $stocks->get_stocks("VZ", "y");

//$dowjones = $stocks->get_stocks("^DJI", "n");//Dow Jones and no cacheing

//echo var_dump($stocks->get_stocks("^DJI", "n"));//displays the kind of array this class returns

$stocks->my_close();

?>

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-03 : 20:36:28

Wrong place, this is ms sql server forum.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources