if (isset($_GET["year"])) { $year = $_GET["year"]; } else { $year = ""; } } // counting the offset $offset = ($pageNum - 1) * $rowsPerPage; if (array_key_exists("stock1", $_POST) && array_key_exists("stock2", $_POST) || array_key_exists("stock1", $_GET) && array_key_exists("stock2", $_GET)) { include 'view/compare_header.html'; if (isset($_POST["stock1"]) && isset($_POST["stock2"])) { $recommendations = getPerformance($_POST["stock1"], $_POST["year"], $_POST["website"], $offset, $rowsPerPage); $recommendations2 = getPerformance($_POST["stock2"], $_POST["year"], $_POST["website"], $offset, $rowsPerPage); } else { $recommendations = getPerformance($_GET["stock1"], $_GET["year"], $_GET["website"], $offset, $rowsPerPage); $recommendations2 = getPerformance($_GET["stock2"], $_GET["year"], $_GET["website"], $offset, $rowsPerPage); } if (isset($recommendations) && isset($recommendations2)) { include "view/Comparison.html"; } else { echo "<h3> No Recommendations found in the database for this stock.</h3>"; } // ------------------------------------------------------------- // the following code for pagination is based on an example by http://www.phpasks.com/articles/phpmysqlpaging-splittingyourqueryresultinmultiplep.html // count the rows in the database $queryString = "SELECT COUNT(*) AS count1 FROM recommendations WHERE symbol='{$symbol1}' AND url LIKE " . "'%" . "{$website}" . "%'" . " AND date LIKE " . "'" . "{$year}" . "%'"; $queryString = "SELECT COUNT(*) AS count2 FROM recommendations WHERE symbol='{$symbol2}' AND url LIKE " . "'%" . "{$website}" . "%'" . " AND date LIKE " . "'" . "{$year}" . "%'"; $result = mysql_query($queryString, $localDB); $row = mysql_fetch_array($result); if ($row['count1'] > $row['count2']) { $num_rows = $row['count2'];
function getAllPerformances($symbol = "", $year = "", $website = "", $tableName, $maxPage) { $offset = 0; $rowsPerPage = 10; global $localDB; global $sql_database; //echo "table name: ".$tableName."<br />"; //check if the tableName already exists in the db $existRes = mysql_query("\r\n\t\t\t\tSELECT COUNT(*) AS count\r\n\t\t\t\tFROM information_schema.tables\r\n\t\t\t\tWHERE table_name = '{$tableName}'\r\n\t\t\t\t"); //echo "exist:".mysql_result($existRes, 0); //if(!$existRes) die(mysql_error()."\n"); //if the table exists if ($existRes = mysql_result($existRes, 0) == 1) { //echo 'exist1'; $querySmallTable = mysql_query("SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_NAME = \"{$tableName}\"", $localDB); $queryAll = mysql_query("SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_NAME = \"recommendations\"", $localDB); if (!$querySmallTable) { die(mysql_error() . "\n"); } if (!$queryAll) { die(mysql_error() . "\n"); } //echo 'exist2'; $result = mysql_fetch_array($querySmallTable); $dateSmallTable = strtotime($result['UPDATE_TIME']); $result = mysql_fetch_array($queryAll); $dateAll = strtotime($result['UPDATE_TIME']); //echo "dates: recs".$dateAll. " newtable: ".$dateSmallTable; //if the table needs to be updated if ($dateAll > $dateSmallTable) { //$res = mysql_select_db($sql_database); $res = mysql_query("DROP TABLE {$tableName}"); // echo "dropping"; if (!$res) { die(mysql_error() . "\n drop table error? \n"); } } else { // echo "not dropping"; return; //exists and updated } } else { //echo ' not exist '; } //echo "table updating"; $res = mysql_query("CREATE TABLE " . $tableName . " (\r\n\t\t\t\tsymbol TEXT,\r\n\t\t\t\turl TEXT,\r\n\t\t\t\tdate DATE,\r\n\t\t\t\tdirection VARCHAR(40),\r\n\t\t\t\t1d FLOAT,\r\n\t\t\t\t5d\tFLOAT,\r\n\t\t\t\t30d FLOAT,\r\n\t\t\t\t365d FLOAT,\n\t\t\t\tdisplayURL TEXT)\r\n\t\t\t\t"); if (!$res) { die(mysql_error() . "\n create table error? \n"); } for ($i = 0; $i < $maxPage; $i++) { $offset = $i * $rowsPerPage; //get the performances of a certain number of stocks $performances = getPerformance($symbol, $year, $website, $offset, $rowsPerPage); //var_dump($performances); //echo "BEFORE <br />"; foreach ($performances as $per) { //echo "<br> <br <br> per:"; //var_dump($per); if (is_nan($per["1d"])) { $per["1d"] = "NULL"; } if (is_nan($per["5d"])) { $per["5d"] = "NULL"; } if (is_nan($per["30d"])) { $per["30d"] = "NULL"; } if (is_nan($per["365d"])) { $per["365d"] = "NULL"; } $queryString = "REPLACE INTO " . $tableName . " VALUES (" . '"' . $per["symbol"] . '"' . " , " . '"' . $per["url"] . '"' . " , " . '"' . $per["date"] . '"' . " , " . '"' . $per["direction"] . '"' . " , " . '"' . $per["1d"] . '"' . " , " . '"' . $per["5d"] . '"' . " , " . '"' . $per["30d"] . '"' . " , " . '"' . $per["365d"] . '"' . " , " . '"' . $per["displayURL"] . '"' . ")"; //echo $queryString."<br>"; $res = mysql_query($queryString); // echo "Symbol: "; //echo $per["symbol"] . " " . $per["url"] . " " . $per["date"] . " " . $per["direction"] . " " . $per["1d"] . " " . $per["5d"] . " " . $per["30d"] . " " . $per["365d"] . "<br / >"; if (!$res) { die(mysql_error() . "\n"); } } // echo "AFTER <br />"; } }