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
noob#10
Forum Newbie
Posts: 19 Joined: Wed Aug 15, 2007 9:55 am
Post
by noob#10 » Sun Sep 02, 2007 5:32 am
i have a module that sorts all reports by date but it doesn't work.. i'm guessing its my query but i'm not that good in guessing
basically all i want to do is display data depending on what the client is asking.. for example if a client would choose form the dropdown list to display reports in march all reports in march should appear but it doesnt..
here is my code for the form:
Code: Select all
<?php
if (!defined('WEB_ROOT')) {
exit;
}
$sql = "SELECT *
FROM tbl_order od, tbl_order_item o, tbl_product pd
WHERE o.pd_id = pd.pd_id and o.od_id = od.od_id AND od_status = 'Paid'";
$result = dbQuery($sql);
$totalsale = 0;
$totalqty = 0;
?>
<form action="getreports.php" method="post" name="getReports" id="getReports">
<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">
Month required<br><br>
<select name="month" id="month">
<option>--selected--</option>
<option value="01">January</option>
<option value="02">Febuary</option>
<option value="03">March</option>
<option value="04">April</option>
<option value="05">May</option>
<option value="06">June</option>
<option value="07">July</option>
<option value="08">August</option>
<option value="09">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select> <input type="button" value="sort" onclick="salesreport();">
</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(Tax included)</td>
</tr>
<?php
while ($row = dbFetchAssoc($result)){
extract($row);
$tax = $pd_price * 0.12;
$total = $od_qty * $pd_price + $tax;
$totalsale += $total;
$totalqty += $od_qty;
?>
<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 align="center"><?php echo displayAmount($total);?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="7">
</td>
</tr>
<tr>
<td colspan="7">
<?php echo "Total Items Sold:"." ".$totalqty;?>
</td>
</tr>
<tr>
<td colspan="7">
<?php echo "Total Sales:"." ".displayAmount($totalsale);?>
</td>
</tr>
</table>
</form>
and in this form the is the query:
Code: Select all
<?php
require_once '../../library/config.php';
require_once '../library/functions.php';
checkUser();
$action = isset($_GET['action']) ? $_GET['action'] : '';
switch ($action) {
case 'sales' :
salesReport();
break;
default :
// if action is not defined or unknown
// move to main user page
header('Location: index.php');
}
function salesReport()
{
$month = $_POST['month'];
$sql = "SELECT *
FROM tbl_oder 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.od_date) = '$month'";
$result = dbQuery($sql);
header("Location: index.php");
}
?>
could it be my query is incomplete? i'am really not that good in php so please help! thank you!
Last edited by
noob#10 on Sun Sep 02, 2007 6:20 am, edited 2 times in total.
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Sun Sep 02, 2007 6:02 am
noob#10 wrote: i have a module that sorts all reports by date but it doesn't work..
please elaborate on "doesn't work". E.g., do you get an error message?
and please please
noob#10
Forum Newbie
Posts: 19 Joined: Wed Aug 15, 2007 9:55 am
Post
by noob#10 » Sun Sep 02, 2007 6:25 am
i have edited the subject line.. thank you for the reminder... :
about the problem:
if i want to display reports on march this form does not give me any results.. it still gives me the reports of all the months.. hope that helps.. i practically explained everything on the top..
i have a new form if this form is hard to read..
if you want me to display just tell me..
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Sun Sep 02, 2007 6:30 am
Your function salesReport() does nothing with the result of the database query.
Take a closer look at what you do with the return value of dbQuery() in your main script.
Esp. the part starting with
while ($row = dbFetchAssoc($result)){
noob#10
Forum Newbie
Posts: 19 Joined: Wed Aug 15, 2007 9:55 am
Post
by noob#10 » Sun Sep 02, 2007 6:46 am
sir what do you mean that it doesn't do anything? could be the query is wrong?
so it should be like:
$result = dbQuery($sql); to
$result = dbQuery($result);
??
and sir that query displays ALL of the reports which i think is correct..
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Sun Sep 02, 2007 8:25 am
Please take another close look on your main script
Code: Select all
<?php
$result = dbQuery($sql);
// <-- other code, unrelated to $result -->
while ($row = dbFetchAssoc($result)) {
extract($row);
$tax = $pd_price * 0.12;
$total = $od_qty * $pd_price + $tax;
$totalsale += $total;
$totalqty += $od_qty;
?>
<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 align="center"><?php echo displayAmount($total);?></td>
</tr>
<?php
}
?>First it sends the sql statment to the sql server and gets a result resource (
$result ).
Then it uses this resource to fetch one row/records after another until there no more records left (
while ($row = dbFetchAssoc($result)) { ... } )
Within the while loop it uses the current row and prints the data.
You function salesReport() stops after "First it sends the sql statment to the sql server and gets a result resource (
$result )."
noob#10
Forum Newbie
Posts: 19 Joined: Wed Aug 15, 2007 9:55 am
Post
by noob#10 » Sun Sep 02, 2007 9:13 am
how about this code sir is the problem thesame?
Code: Select all
<?php
$month = (isset($_POST['month']));
$year = (isset($_POST['year']));
$totalsale = 0;
$totalqty = 0;
if(isset($month) && $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 od.od_status = 'Paid' AND MONTH(od_date) = '$month' AND YEAR(od_date) = '$year'";
}else{
$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.od_status = 'Paid'";
}
$result = dbQuery($sql);
?>
<form action="index.php?view=sales" method="post">
<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">
Month and Year required<br><br>
<select name="month">
<option>--selected--</option>
<option value="01">January</option>
<option value="02">Febuary</option>
<option value="03">March</option>
<option value="04">April</option>
<option value="05">May</option>
<option value="06">June</option>
<option value="07">July</option>
<option value="08">August</option>
<option value="09">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select>
<select name="year">
<option>--selected--</option>
<option value="2007">2007</option>
<option value="2008">2008</option>
<option value="2009">2009</option>
<option value="2010">2010</option>
<option value="2011">2011</option>
<option value="2012">2012</option>
<option value="2013">2013</option>
<option value="2014">2014</option>
<option value="2015">2015</option>
<option value="2016">2016</option>
<option value="2017">2017</option>
<option value="2018">2018</option>
</select>
<input type="submit" value="sort">
</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(Tax included)</td>
</tr>
<?php
while ($row = dbFetchAssoc($result)){
extract($row);
$tax = $pd_price * 0.12;
$total = $od_qty * $pd_price + $tax;
$totalsale += $total;
$totalqty += $od_qty;
?>
<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 align="center"><?php echo displayAmount($total);?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="7">
</td>
</tr>
<tr>
<td colspan="7">
<?php echo "Total Items Sold:"." ".$totalqty;?>
</td>
</tr>
<tr>
<td colspan="7">
<?php echo "Total Sales:"." ".displayAmount($totalsale);?>
</td>
</tr>
</table>
</form>
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Sun Sep 02, 2007 9:27 am
uh
Isn't that the code I was talking about?
I only replaced
?>
<form action="index.php?view=sales" method="post">
<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">
Month and Year required<br><br>
<select name="month">
<option>--selected--</option>
<option value="01">January</option>
<option value="02">Febuary</option>
<option value="03">March</option>
<option value="04">April</option>
<option value="05">May</option>
<option value="06">June</option>
<option value="07">July</option>
<option value="08">August</option>
<option value="09">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select>
<select name="year">
<option>--selected--</option>
<option value="2007">2007</option>
<option value="2008">2008</option>
<option value="2009">2009</option>
<option value="2010">2010</option>
<option value="2011">2011</option>
<option value="2012">2012</option>
<option value="2013">2013</option>
<option value="2014">2014</option>
<option value="2015">2015</option>
<option value="2016">2016</option>
<option value="2017">2017</option>
<option value="2018">2018</option>
</select>
<input type="submit" value="sort">
</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(Tax included)</td>
</tr>
<?php
by
// <-- other code, unrelated to $result -->
to help you focus on the essential part.
noob#10
Forum Newbie
Posts: 19 Joined: Wed Aug 15, 2007 9:55 am
Post
by noob#10 » Sun Sep 02, 2007 9:52 am
yes i got your message sir
but sir that code you posted the one with "while" is actually working:? .. it displays all the data on the form.. all i want to do is to get all data that the client is asking like when he asks for a march report all he has to do is pick march from the dropdown and all the march report should appear... sorry sir if i'm giving you a hard time if you have your own idea on how to do it i would greatly appreciate it.
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Sun Sep 02, 2007 10:06 am
I want you to take a close look at your working code.
Again, your working code does: send the sql statement, fetch record after record in a while loop, use the current record within the while loop to print the data.
Your not-so-much-working code (in function salesReport()) does: send the sql statement.
What does salesReport not do?