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
carsky
Forum Commoner
Posts: 30 Joined: Sun Jul 08, 2007 3:26 am
Post
by carsky » Sun Aug 26, 2007 2:44 pm
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; ?>
<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">
</td>
</tr>
<tr>
<td colspan="7">
<?php echo "Total Sales"." ".displayAmount($totalsale);?>
</td>
</tr>
</table>
webgroundz
Forum Commoner
Posts: 58 Joined: Thu Jun 21, 2007 1:20 am
Location: Philippines
Post
by webgroundz » Sun Aug 26, 2007 5:48 pm
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..
carsky
Forum Commoner
Posts: 30 Joined: Sun Jul 08, 2007 3:26 am
Post
by carsky » Sun Aug 26, 2007 11:14 pm
thanks for your help i'll try it out here..thanks a lot.
carsky
Forum Commoner
Posts: 30 Joined: Sun Jul 08, 2007 3:26 am
Post
by carsky » Mon Aug 27, 2007 12:30 am
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; ?>
<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">
</td>
</tr>
<tr>
<td colspan="7">
<?php echo "Total Sales"." ".displayAmount($totalsale);?>
</td>
</tr>
</table>
guys can you help me out. thanks
webgroundz
Forum Commoner
Posts: 58 Joined: Thu Jun 21, 2007 1:20 am
Location: Philippines
Post
by webgroundz » Mon Aug 27, 2007 2:21 am
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..
carsky
Forum Commoner
Posts: 30 Joined: Sun Jul 08, 2007 3:26 am
Post
by carsky » Mon Aug 27, 2007 2:24 am
ok ill try it again thank a lot for the help..you're also a filipino?wer r u located?