Implementation, steps 14 and 15 - PHP

Step 14: Combine all queries above and computations. Then print to an HTML table using a WHILE loop:

if (mysql_num_rows($result2) >0)

{

//print analysis

//echo '<table width=100% cellpadding=10 cellspacing=10 border=1>';

echo '<table width=100% border=1>';

echo '<tr><td><b>Entry</b></td><td><b>Date</b></td><td><b>Open</b>
</td><td><b>High</b></td><td><b>Low</b></td><td><b>Close</b></td>
<td><b>MA200</b></td><td><b>MA50</b></td><td><b>Delta</b></td>
<td><b>%Strength</b></td><td><b>Recommendation</b></td></tr>';

while ($row2= mysql_fetch_row($result2))

{

$result3 = mysql_query("SELECT avg(close) from `sp500` WHERE `entry`>='$lowerlimit' AND `entry`<='$upperlimit'")

or die(mysql_error());

// store the record of the "example" table into $row

$row3 = mysql_fetch_array($result3)

or die("Invalid query: " . mysql_error());

// Print out the contents of the entry

$ma200 = $row3['avg(close)'];

$result4 = mysql_query("SELECT avg(close) from `sp500` WHERE `entry`>='$lowerlimit50' AND `entry`<='$upperlimit'")

or die(mysql_error());

// store the record of the "example" table into $row

$row4 = mysql_fetch_array($result4)

or die("Invalid query: " . mysql_error());

// Print out the contents of the entry

$ma50 = $row4['avg(close)'];

$w=round($ma200,2);

$x=round($ma50,2);

$y=round(($ma50-$ma200),2);

$z=round(((($ma50-$ma200)/$ma200)*100),3);

if ($z >= 8.51) {

$recommendation = 'above SATURATED BULL TREND( VERY HIGH RISK BUYING)';

}

elseif ( $z >=5.1 && $z <=8.5) {

$recommendation = 'CONFIRMED BULL TREND (HIGH RISK BUYING)';

}

elseif ( $z >=2 && $z <=5) {

$recommendation = 'CONFIRMED BULL TREND (LOW RISK BUYING)';

}

elseif ( $z >=0 && $z <=1.99) {

$recommendation = 'UNCONFIRMED BULL TREND';

}

elseif ( $z <=0 && $z >=-4.99) {

$recommendation = 'UNCONFIRMED BEAR TREND';

}

elseif ( $z <=-5 && $z >=-8) {

$recommendation = 'CONFIRMED BEAR TREND (LOW RISK SHORT)';

}

elseif ( $z <=-8.1 ) {

$recommendation = 'SATURATED BEAR TREND( VERY HIGH RISK SHORT)';

}

$upperlimit--;

$lowerlimit50--;

$lowerlimit--;

echo '<tr>';

echo '<td>'.$row2[0].'</td>';

echo '<td>'.$row2[1].'</td>';

echo '<td>'.$row2[2].'</td>';

echo '<td>'.$row2[3].'</td>';

echo '<td>'.$row2[4].'</td>';

echo '<td>'.$row2[5].'</td>';

echo '<td>'.$w.'</td>';

echo '<td>'.$x.'</td>';

echo '<td>'.$y.'</td>';

echo '<td>'.$z.'</td>';

echo '<td>'.$recommendation.'</td>';

echo '</tr>';

}

echo '</table>';

}

else

{

echo '<br />No rows found.<br />';

}

Step 15: Free the MySQL results and close the database connection.

mysql_free_result($result1);

mysql_free_result($result2);

mysql_free_result($result3);

mysql_free_result($result4);

mysql_close($dbhandle);

This is a fairly complicated application. You can see it in action here: http://www.php-developer.org/SP500historicaldataanalysis.php  

Or download the complete PHP script.