[Resolved] retrieve data based on two date drop down menus

[fakename]

So The query works when I simply hard code two dates into it.
My problem is trying to get it to do the same with drop down date menus.
The drop down boxes show up quite nicely, but
retrieves nada. I'm not quite sure what I'm missing here.
Any advice appreciated.

<?php
/ Connecting, selecting database /
$link = mysql_connect("localhost", "password", "")
or die("Could not connect : " . mysql_error());
mysql_select_db("dbname, $link) or die("Could not select database");

/*
	** Function: DateSelector
	** Input: STRING $inName, [INTEGER $useDate]
	** Output:
	** Description: Creates three form fields for get month/day/year.
	**   $useDate is seconds since the epoch. It's default is time().
	**   $inName is the "prefix" to be used when naming the html elements.
	*/
	function DateSelector($inName, $useDate="")
	{
		$monthName = array(1=>"January", "February", "March",
		"April", "May", "June", "July", "August",
		"September", "October", "November", "December");

		if($useDate == "")
		{
			$useDate = Time();
		}

		print("<SELECT NAME=\"" . $inName . "Month\">\n");
		for($currentMonth = 1; $currentMonth <= 12; $currentMonth++)
		{
			echo "<OPTION VALUE=\"";
			echo intval($currentMonth);
			echo "\"";
			if(intval(date("m", $useDate))==$currentMonth)
			{
				echo " SELECTED";
			}
			echo ">".$monthName[$currentMonth]."\n";
		}
		echo "</SELECT>\n";

		echo "<SELECT NAME=\"" . $inName . "Day\">\n";
		for($currentDay=1; $currentDay <= 31; $currentDay++)
		{
			echo "<OPTION VALUE=\"$currentDay\"";
			if(intval(date("d", $useDate))==$currentDay)
			{
				echo " SELECTED";
			}
			echo ">$currentDay\n";
		}
		echo "</SELECT>\n";

		echo "<SELECT NAME=\"" . $inName . "Year\">\n";
		$startYear = date("Y", $useDate);
		if($startYear < 1997)
		{
			$startYear = date("Y");
		}

		for($currentYear = 1997; $currentYear <= $startYear+1;$currentYear++)
		{
			echo "<OPTION VALUE=\"$currentYear\"";
			if(date("Y", $useDate)==$currentYear)
			{
				echo " SELECTED";
			}
			echo ">$currentYear\n";
		}
		echo "</SELECT>\n";
	}

	function getDateSelector($inName, $useDate="")
	{
		$monthName = array(1=>"January", "February", "March",
		"April", "May", "June", "July", "August",
		"September", "October", "November", "December");

		if($useDate == "")
		{
			$useDate = Time();
		}

		$data = "<SELECT NAME=\"" . $inName . "Month\">\n";
		for($currentMonth = 1; $currentMonth <= 12; $currentMonth++)
		{
			$data .= "<OPTION VALUE=\"" . intval($currentMonth) . "\"";
			if(intval(date("m", $useDate))==$currentMonth)
			{
				$data .= " SELECTED";
			}
			$data .= ">" . $monthName[$currentMonth] . "\n";
		}
		$data .= "</SELECT>\n";

		$data .= "<SELECT NAME=\"" . $inName . "Day\">\n";
		for($currentDay=1; $currentDay <= 31; $currentDay++)
		{
			$data .= "<OPTION VALUE=\"$currentDay\"";
			if(intval(date("d", $useDate))==$currentDay)
			{
				$data .= " SELECTED";
			}
			$data .= ">$currentDay\n";
		}
		$data .= "</SELECT>\n";

		$data .= "<SELECT NAME=\"" . $inName . "Year\">\n";
		$startYear = date("Y", $useDate);
		if($startYear < 1997)
		{
			$startYear = date("Y");
		}

		for($currentYear = 1997; $currentYear <= $startYear+1;$currentYear++)
		{
			$data .= "<OPTION VALUE=\"$currentYear\"";
			if(date("Y", $useDate)==$currentYear)
			{
				$data .= " SELECTED";
			}
			$data .= ">$currentYear\n";
		}
		$data .= "</SELECT>\n";

		return($data);
	}

/ The query /

$query = "select invoice_sku.ExternalSKU as Sku,invoice_sku.Name as Name, SUM(Quantity) as Total "
. " FROM invoice_sku, invoice "
. " where invoice_sku.Invoice = invoice.ID "
. " and invoice.Created >= '".$starTime ."' and '".$endTime ."' "
. " group by invoice_sku.ExternalSKU ";

      $result = mysql_query($query,$link) or die("Query failed : " . mysql_error());

/ Printing drop down andresults in HTML /

	print "<table width=750 valign=top>\n";
	print "\t<tr valign=top>\n";
	print "\t<td valign=top>\n";
	print "<form action=\"http://www.agferrari.com/get_sales.php\" method=\"post\">\n";
	print getDateSelector("start", $startTime);
	print getDateSelector("end", $endTime);
	print "<input type=\"submit\" value=\"Find Product totals\" name=\"submit_button\" />\n";

while ($line = mysql_fetch_object($result))
{
print "\t\t$line->Sku\n";
print "\t\t$line->Name\n";
print "\t\t$line->Total <br>\n";
print "\t</td>\n";
print "\t</tr>\n";
print "</table>\n";
}

/* Free resultset */
mysql_free_result($result);

/* Closing connection */
mysql_close($link);

?>

[tomhath]

Where did $starTime and $endTime come from?

$query = "select invoice_sku.ExternalSKU as Sku,invoice_sku.Name as Name, SUM(Quantity) as Total "
. " FROM invoice_sku, invoice "
. " where invoice_sku.Invoice = invoice.ID "
. " and invoice.Created >= '".$starTime ."' and '".$endTime ."' "
. " group by invoice_sku.ExternalSKU ";

[fakename]

The $starTime should be $startTime. I got them from some code I came across:

print getDateSelector("start", $startTime);
print getDateSelector("end", $endTime);

I asumed that they were the variables I needed to use, so I added them to the query.

[acrabadabra]

First of all please use the PHP vB Code next time, it will give a readable thread and maybe a faster feedback.

Second:
The form you have combined with the function creates a pulldown with the name= as given in your function statement (respectively in your code, 'start' and 'end'). So your SQL statement should become:

$query = "select invoice_sku.ExternalSKU as Sku,invoice_sku.Name as Name, SUM(Quantity) as Total " 
. " FROM invoice_sku, invoice " 
. " where invoice_sku.Invoice = invoice.ID " 
. " and invoice.Created >= '".$_POST['start']."' and '".$_POST['end']."' " 
. " group by invoice_sku.ExternalSKU ";

PS. Dont forget to close the thread if your answer has been answered.

[fakename]

With regards to using the PHP vB Code, I honestly don't know what that means.

I tried the code you modified, but returned the same results I had earlier. Your reply and the previous one made me realize that I must be doing something wrong in terms of using that drop down function.

Hopefully I'll figure that out.

thanks.

[acrabadabra]

Sorry, my fault. Did not read the code good enough 🙂
The functions concats the 'start' with respectively Month, Day and Year. (Sometimes it helps if you look at the source output of a generated HTML code, so you can actually see what happens)

So, it should not be: $_POST['start'] but

$query = "select invoice_sku.ExternalSKU as Sku,invoice_sku.Name as Name, SUM(Quantity) as Total "  

. " FROM invoice_sku, invoice "  

. " WHERE invoice_sku.Invoice = invoice.ID "  

. " AND invoice.Created >= '".$_POST['startYear'].'-'.$_POST['startMonth'].'-'.$_POST['startDay']."'"
. " AND '".$_POST['endYear'].'-'.$_POST['endMonth'].'-'.$_POST['endDay']."' "  

. " group by invoice_sku.ExternalSKU ";

And vB Code is something like HTML, but used in forums as these. Uou can read about it on the left when you type a new message. Good luck!

[fakename]

That worked like a charm. Don't know how I missed posting all the variables. Thank you