PHp mySQL query - Need Help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
clicktrack
Forum Newbie
Posts: 5
Joined: Thu Apr 29, 2010 9:20 am

PHp mySQL query - Need Help

Post by clicktrack »

I have a mySQL table that most Weekdays I enter 10-30 records in. The records have to deal with stock options so there is a field for expiration date. There is also a field for Postdate which is the date I entered it into the DB. I am trying to create a user friendly page to display the information but can't figure out an efficient method to pull the information I need. I am wondering if anyone can help.

When the user initially goes to the page I want it to display the most recent days worth of data. Generally for any given days worth of data there would be 2-3 different expiration dates and I would want each set of expiration dates in there own table. Example would be 10 stocks with May 21 expiration and 10 stocks with June 18 expiration. My hiccup is if they come in on a day where there is no data I would need to grab the most recent. So if they cam in on Saturday I would need to grab Fridays if it was in there or the most recent if that was not in there for any reason.

My second part is I want to give the user the option to go back/forward days for historical purposes. So when they initially enter there are no days forward so I would only need to grab the postdate of the entry prior to that days entry. Once they clicked on that to go back a day now I need to grab the prior days postdate and next days postdate so they could go back one day or forward one day.

Right now I got it to the point where I grab all of the postdates in the DB and I can click on any given day to see the data. Then I grab that days data and display it in one table which really I want broken into multiple tables depending on the expiration date.

Code: Select all

$sql    = "SELECT distinct postdate FROM `trades` order by postdate desc";

$result = mysql_query($sql, $link);
 
if (!$result) {
    echo "DB Error, could not query the database\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

		print "<table><tr><td>What day would you like to view?</td></tr>";

while ($row = mysql_fetch_row($result))
	{

		 foreach ($row as $field)
		 {
			   print "<tr><td nowrap=\"nowrap\" align=\"center\" bgcolor=\"$row_color\"><a href=\"$_SERVER[PHP_SELF]?date=$field\">$field</a></font></td></tr>";
		 }

		 
			  $row_count++;
	}
	print "</table>";

if (isset($_REQUEST['date']))
{
$date = $_REQUEST['date'];
} else {
$date = date("Y-m-d");
}

$sql	= "SELECT premium, downside, symbol, expire, close, strike, ivalue, evalue, bid, ask, postdate FROM `trades` WHERE `postdate` = '". $date."'";

if (isset($_REQUEST['Orderby']))
{
	$sql    = $sql . " Order By ". $_REQUEST['Orderby'];
} else {
	$sql    = $sql . " Order By expire, premium desc";
}
//print $sql;
$result = mysql_query($sql, $link);
 
if (!$result) {
    echo "DB Error, could not query the database\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}


// Define colors for alternating rows
$color1 = "#fffaf0";
$color2 = "#ffffff";
$row_count = 0;

$row = mysql_fetch_assoc($result);

if (!empty($row)) { 

	print "<table align=\"center\" border=\"0\" cellpadding=\"2\" bgcolor=\"#4F5158\">";
	print "<tr style=\"color: white;\">
			<th><a href=\"/listtrades.php?date=2010-04-27&Orderby=premium\">Return</a></th>
			<th><a href=\"/listtrades.php?date=2010-04-27&Orderby=downside\">Downside</a></th>
			<th><a href=\"/listtrades.php?date=2010-04-27&Orderby=symbol\">Symbol</a></th>
			<th><a href=\"/listtrades.php?date=2010-04-27&Orderby=expire\">Expire</a></th>			
			<th><a href=\"/listtrades.php?date=2010-04-27&Orderby=close\">Close</a></th>
			<th><a href=\"/listtrades.php?date=2010-04-27&Orderby=strike\">Strike</a></th>
			<th><a href=\"/listtrades.php?date=2010-04-27&Orderby=ivalue\">I Value</a></th>
			<th><a href=\"/listtrades.php?date=2010-04-27&Orderby=evalue\">E Value</a></th>
			<th><a href=\"/listtrades.php?date=2010-04-27&Orderby=bid\">Bid</a></th>
			<th><a href=\"/listtrades.php?date=2010-04-27&Orderby=ask\">Ask</a></th>
			<th><a href=\"/listtrades.php?date=2010-04-27&Orderby=postdate\">Posted</a></th></tr>";
	
     
 
	while ($row = mysql_fetch_row($result))
	{
	$row_color = ($row_count % 2) ? $color1 : $color2;
	 
		 print "<tr>";
		 foreach ($row as $field)
		 {
			   print "<td nowrap=\"nowrap\" align=\"center\" bgcolor=\"$row_color\"><font size=\"2\">$field</font></td>";
		 }
		 print "</tr>";
		 
			  $row_count++;
	}
	print "</table>";
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: PHp mySQL query - Need Help

Post by Christopher »

So which if the things that you want to do are not working?
(#10850)
clicktrack
Forum Newbie
Posts: 5
Joined: Thu Apr 29, 2010 9:20 am

Re: PHp mySQL query - Need Help

Post by clicktrack »

The code that I have listed is working but not doing what I want.

The code I have grabs all of the days that are in the database and displays them so i can look at any days worth of data. it also then displays that days data in one table when i want it broken up into multiple tables based on expiration date field.
Post Reply