PHP mySQL 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 Help

Post 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.
User avatar
hypedupdawg
Forum Commoner
Posts: 74
Joined: Sat Apr 10, 2010 5:21 am

Re: PHP mySQL Help

Post 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!
clicktrack
Forum Newbie
Posts: 5
Joined: Thu Apr 29, 2010 9:20 am

Re: PHP mySQL Help

Post 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.
clicktrack
Forum Newbie
Posts: 5
Joined: Thu Apr 29, 2010 9:20 am

Re: PHP mySQL Help

Post 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>";
		}	

Post Reply