Page 1 of 1

need help on query sort

Posted: Sun Sep 02, 2007 5:32 am
by noob#10
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">
&nbsp;
</td>
</tr>
<tr>
<td colspan="7">
<?php echo "Total Items Sold:"."&nbsp;".$totalqty;?>
</td>
</tr>
<tr>
<td colspan="7">
<?php echo "Total Sales:"."&nbsp;".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!

Re: need help please!

Posted: Sun Sep 02, 2007 6:02 am
by volka
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
Before Post Read: General Posting Guidelines wrote:7. Write a clear subject line, it'll help people deciding whether to look at your post or not - 'help, PHP noob' is probably going to be ignored by some.

Posted: Sun Sep 02, 2007 6:25 am
by noob#10
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.. :oops:
if you want me to display just tell me.. :oops:

Posted: Sun Sep 02, 2007 6:30 am
by volka
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)){

Posted: Sun Sep 02, 2007 6:46 am
by noob#10
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..

Posted: Sun Sep 02, 2007 8:25 am
by volka
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)."

Posted: Sun Sep 02, 2007 9:13 am
by noob#10
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> &nbsp; &nbsp;
			<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">
&nbsp;
</td>
</tr>
<tr>
<td colspan="7">
<?php echo "Total Items Sold:"."&nbsp;".$totalqty;?>
</td>
</tr>
<tr>
<td colspan="7">
<?php echo "Total Sales:"."&nbsp;".displayAmount($totalsale);?>
</td>
</tr>
</table>
</form>

Posted: Sun Sep 02, 2007 9:27 am
by volka
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> &nbsp; &nbsp;
<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.

Posted: Sun Sep 02, 2007 9:52 am
by noob#10
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. :D

Posted: Sun Sep 02, 2007 10:06 am
by volka
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?