Page 1 of 1

PHP mySQL Help

Posted: Fri Apr 30, 2010 8:41 am
by clicktrack
I want to make a call to grab all unique dates from a field in a table. I know how to do that.

Code: Select all

$sql    = "SELECT distinct postdate FROM `trades` order by postdate desc";
I will also have a date variable submitted from a form. What I want to do is scroll through the results and set 2 variables. One is the day before the date submitted and the other is the day after the one submitted. It is not as simple as taking the date selected and adding 1 day and sbtracting one day from it as those dates might not be in the database.

Example:

SQL Query returns
4/30/2010
4/29/2010
4/27/2010
4/26/2010
4/25/2010

Ex 1. Date submitted from form is 4/29/2010 then I would want the two variables to be 4/30/2010 and 4/27/2010.
Ex 2. Date submitted from form is 4/25/2010 then I would want one variable set to 4/26/2010 and other would not get set or set to something where I know I was at earliest date.

Any help would be appreciated.

Re: PHP mySQL Help

Posted: Fri Apr 30, 2010 9:08 am
by hypedupdawg
I take it that these dates are stored in an array inside the $sql variable? In that case, you should be able to make something like this:

Code: Select all

<?php
$sql    = "SELECT distinct postdate FROM `trades` order by postdate desc";
//I am presuming that 4/30/2010 = $sql[0], 4/29/2010 = $sql[1] etc.

$i = 0;
$var1 = array();
$var2 = array();
while ($sql[i] != "")
	{
	print "<br/><br/>Actual Date: " . $sql[i];
	if ($sql[i - 1] != "") //Checking to see if there is a more recent date.
		{
		$var1[i] = $sql[i - 1]; //Setting it to the record before	
		}
	else
		{
		$var1[i] = "Most Recent Date"; //Setting it to a predefined message
		}
	print "<br/>Date Recieved: " . $var1[i];

	if ($sql[i + 1] != "") //Checking to see if there is a less recent date.
		{
		$var2[i] = $sql[i + 1]; //Setting it to the record after	
		}
	else
		{
		$var2[i] = "Least Recent Date"; //Setting it to a predefined message	
		}
	print "<br/>Date Before: " . $var2[i];

	$i++
	}
?>
I havn't been able to check that this works, as I am away from home at the moment - but you should get the idea. To retrieve a complete set of data, all you would need to do is call the $sql, $var1 and $var2 arrays with the same key value (which I have called $i).

Give me a shout whether it works/fails/is not what you asked for!

Re: PHP mySQL Help

Posted: Fri Apr 30, 2010 9:55 am
by clicktrack
Thanks for the reply. The data is stored in my sql table and I can calling it with the sql statement into a variable. I can then store that into an array. I will take what you have an play around with it and see if I can get it to work.

Thanks for your help and I will respond once I have done so.

Re: PHP mySQL Help

Posted: Fri Apr 30, 2010 11:19 am
by clicktrack
I got it to work with some slight modifications. Thanks. the var1 and var2 just needed to be regular variables rather then arrays. Other then that you were pretty spot on. Below is the working code that did the trick for me.

Thanks

Code: Select all

<?php
 
if (!$link = mysql_connect('localhost', 'blah', 'blah')) {
    echo 'Could not connect to mysql';
    exit;
}
if (!mysql_select_db('coveredc_content', $link)) {
    echo 'Could not select database';
    exit;
}
$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;
}
		
$tmparray = array();
while ($row = mysql_fetch_array($result))
	{
		$tmparray[] = $row[0];
	}


$i = 0;
$var1 = "";
$var2 = "";


if (isset($_REQUEST['date']))
{
$date = $_REQUEST['date'];
} else {
$date = $tmparray[0];
}
//print $date;
while ($i <= count($tmparray))
        {
			if ($date == $tmparray[$i]) {
				if ($tmparray[$i - 1] != "") //Checking to see if there is a more recent date.
						{
						$var1 = $tmparray[$i - 1]; //Setting it to the record before      
						}


				if ($tmparray[$i + 1] != "") //Checking to see if there is a less recent date.
						{
						$var2 = $tmparray[$i + 1]; //Setting it to the record after       
						}
			} 
				$i++;
        }
		
		
	if ($var1 !="") {
		print "<a href=\"daily-trades?date=$var1\">$var1</a>";
		}
	print "&nbsp;";
	if ($var2 !="") {
		print "<a href=\"daily-trades?date=$var2\">$var2</a>";
		}