Page 1 of 1

sort by month and year using dropdown

Posted: Sun Aug 26, 2007 2:44 pm
by carsky
hello guys, i need your help..im doing a module on salesreport..the data can now be view..and the total can all be viewed..what i want to happen is to sort the report by month and year..maybe you could help me out on my code...i really need your help..thanks.

here's the link to the test site.. http://jehlion.org/im_thesis/iecctestpr ... view=sales .. just use admin for user and pass.

Code: Select all

<table width="80%" cellpadding="5" cellspacing="0" border="1" sytle="font-family:arial;color:purple;font-size:12px" align="center">
<tr>
	<td style="font-size:20px;color:purple;font-family:arial" colspan="7" align="center">SALES REPORT</td>
</tr>
<tr>
	<td colspan="7">
		<?php
		$years = "<select name=birthyear size=1>\n<option value=year>Select...\n";

		for ($i = 2020; $i >= 2007; $i = $i - 1) 
		{
		$years = $years . "<option>$i\n";
		}

		$years = $years . "</select>\n";
	
		$months = "<select name=birthmonth size=1>\n<option value=month>Select...\n";

		for ($i = 1; $i <= 12; $i++) {
		$monthname = date("F", mktime(12, 0, 0, $i, 1, 2000));
		$months = $months . "<option value=$i>$monthname\n";
		}

		$months = $months . "</select>\n";
		?>
		<b>Sort by Month</b>
		<?php echo $months; ?>&nbsp;&nbsp;
		<b>Sort by Year</b>
		<?php echo $years; ?>
		
	</td>
</tr>
<tr>
<td>Order Date</td>
<td>Order ID</td>
<td>First Name</td>
<td>Last Name</td>
<td>Order Quantity</td>
<td>Price</td>
<td>Total Amount</td>
</tr>

<?php
//this is the query that gets all the data and orders the data retrieved by month or by year...
	$sql = "SELECT * FROM tbl_order od, tbl_order_item o, tbl_product pd WHERE od.od_id = o.od_id AND o.pd_id = pd.pd_id AND od_status = 'Paid' ORDER BY $month  ASC";
	$result = dbQuery($sql);

$totalsale = 0;
while($row = dbFetchAssoc($result)){
	extract($row);
	
	
	$total = $od_qty * $pd_price;
	$totalsale += $total;
?>
<tr>
<td><?php echo $od_date;?></td>
<td align="center"><?php echo $od_id;?></td>
<td><?php echo $od_payment_first_name;?></td>
<td><?php echo $od_payment_last_name;?></td>
<td align="center"><?php echo $od_qty;?></td>
<td><?php echo displayAmount($pd_price);?></td>
<td><?php echo displayAmount($total);?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="7">
&nbsp;
</td>
</tr>
<tr>
<td colspan="7">
<?php echo "Total Sales"."&nbsp;".displayAmount($totalsale);?>
</td>
</tr>
</table>

Posted: Sun Aug 26, 2007 5:48 pm
by webgroundz
i think your query must look like this :

Code: Select all

//sort by month
if($_POST['cbo_month'])
{
$month = $_POST['cbo_month'];// name of the combo box for month

$result = $db->query("SELECT field_name FROM table_name WHERE MONTH(date_processed) = '$month' ");

//loop
}

//sort by year
if($_POST['cbo_year'])
{
$year = $_POST['cbo_year'];// name of the combo box for year

$result = $db->query("SELECT field_name FROM table_name WHERE YEAR(date_processed) = '$year' ");
//loop
}
i name the field name date_processed for your date but i don't know what is your name for that field., hope this would help, thanks.. :) :) :)

Posted: Sun Aug 26, 2007 11:14 pm
by carsky
thanks for your help i'll try it out here..thanks a lot.

Posted: Mon Aug 27, 2007 12:30 am
by carsky
i have one problem right now..i think $_POST on the same page requires a different method or maybe this is not possible.

Code: Select all

<table width="80%" cellpadding="5" cellspacing="0" border="1" sytle="font-family:arial;color:purple;font-size:12px" align="center">
<tr>
	<td style="font-size:20px;color:purple;font-family:arial" colspan="7" align="center">SALES REPORT</td>
</tr>
<tr>
	<td colspan="7">
		<?php
		$years = "<select name=year size=1>\n<option value=year>Select...\n";

		for ($i = 2020; $i >= 2007; $i = $i - 1) 
		{
		$years = $years . "<option>$i\n";
		}

		$years = $years . "</select>\n";
	
		$months = "<select name=month size=1>\n<option value=month>Select...\n";

		for ($i = 1; $i <= 12; $i++) {
		$monthname = date("F", mktime(12, 0, 0, $i, 1, 2000));
		$months = $months . "<option value=$i>$monthname\n";
		}

		$months = $months . "</select>\n";
		?>
		<b>Sort by Month</b>
		<?php echo $months; ?>&nbsp;&nbsp;
		<b>Sort by Year</b>
		<?php echo $years; ?>
		
	</td>
</tr>
<tr>
<td>Order Date</td>
<td>Order ID</td>
<td>First Name</td>
<td>Last Name</td>
<td>Order Quantity</td>
<td>Price</td>
<td>Total Amount</td>
</tr>

<?php

//sort by month
if($_POST['month'])
{
$month = $_POST['month'];// name of the combo box for month

$sql = "SELECT * FROM tbl_order od, tbl_order_item o, tbl_product pd WHERE od.od_id = o.od_id and o.pd_id = pd.pd_id and MONTH(od_date) = '$month' ";
$result = dbQuery($sql);

//loop
}

if($_POST['year'])
{
$month = $_POST['year'];// name of the combo box for month

$sql = "SELECT * FROM tbl_order od, tbl_order_item o, tbl_product pd WHERE od.od_id = o.od_id and o.pd_id = pd.pd_id and YEAR(od_date) = '$year' ";
$result = dbQuery($sql);
//loop
}

$totalsale = 0;
while($row = dbFetchAssoc($result)){
	extract($row);
	
	
	$total = $od_qty * $pd_price;
	$totalsale += $total;
?>
<tr>
<td><?php echo $od_date;?></td>
<td align="center"><?php echo $od_id;?></td>
<td><?php echo $od_payment_first_name;?></td>
<td><?php echo $od_payment_last_name;?></td>
<td align="center"><?php echo $od_qty;?></td>
<td><?php echo displayAmount($pd_price);?></td>
<td><?php echo displayAmount($total);?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="7">
&nbsp;
</td>
</tr>
<tr>
<td colspan="7">
<?php echo "Total Sales"."&nbsp;".displayAmount($totalsale);?>
</td>
</tr>
</table>
guys can you help me out. thanks

Posted: Mon Aug 27, 2007 2:21 am
by webgroundz
wait, i know the problem..

i would recommend that you add a button for you to trigger your search..
like this:

Code: Select all

if($_POST['Submit'])
{
$month = $_POST['cbo_month'];
$year = $_POST['cbo_year'];

$sql = "SELECT * FROM tbl_order od, tbl_order_item o, tbl_product pd WHERE od.od_id = o.od_id and o.pd_id = pd.pd_id AND MONTH(od_date) = '$month' AND YEAR(od_date) = '$year' ";
$result = dbQuery($sql);  
//loop
}
i think this snippet will work..sorry for the first snippet..thanks..

Posted: Mon Aug 27, 2007 2:24 am
by carsky
ok ill try it again thank a lot for the help..you're also a filipino?wer r u located?