select a month or a year not both

theBrent · September 12, 2007

helo guys! this code works if i choose both year and month.. but if i choose only a year or a month it displays no results.. pls help!

$totalsale = 0;
$totalqty = 0;
$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'";

if(isset($_POST['month']) && !empty($_POST['month']))
  $sql.=" AND MONTH(od_date) = '".intval($_POST['month'])."'";
if(isset($_POST['year']) && !empty($_POST['year']))
  $sql.=" AND YEAR(od_date) = '".intval($_POST['year'])."'";

$result = dbQuery($sql);

xyn · September 12, 2007

<?
$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' $extra";

if(isset($_POST['month']) && !isset($_POST['year'])) #	Month set; year not set
{
$extra = "AND MONTH(od_date) = '".intval($_POST['month'])."'";
}
elseif(!isset($_POST['month']) && isset($_POST['year']))	#	Year Set; No Month
{
$extra = "AND YEAR(od_date) = '".intval($_POST['year'])."'";
}

$result = mysql_query($sql);
echo(mysql_num_rows($sql));

?>{

theBrent · September 12, 2007

sir xyn what's $extra for? and sir i'm using DBquery, i tried ur code and i encountered error.. but i have learned from your code so i tried it this way...

$totalsale = 0;
$totalqty = 0;
$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'";

if(isset($_POST['month']) && !empty($_POST['month']))
  $sql.=" AND MONTH(od_date) = '".intval($_POST['month'])."'";
  
else if(!isset($_POST['month']) && isset($_POST['year']))
  $sql.=" AND YEAR(od_date) = '".intval($_POST['year'])."'";

$result = dbQuery($sql);

the month is working fine the problme now is the year.. anymore ideas sir?

xyn · September 12, 2007

what is...

dbQuery() ?

In php.net i found nothing; so i guess its your own function?

theBrent · September 12, 2007

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in salesreport.php on line 18

i have this error sir..

and sir what is $extra? it's undefined...

theBrent · September 12, 2007

here is my form guys to fully understand..

<?php
$totalsale = 0;
$totalqty = 0;

$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'";

if(isset($_POST['month']) && !empty($_POST['month']))
  $sql.=" AND MONTH(od_date) = '".intval($_POST['month'])."'"; 
else if (isset($_POST['year']) && !empty($_POST['year']))
  $sql.=" AND YEAR(od_date) = '".intval($_POST['year'])."'";

$result = dbQuery($sql)
?>

<form action="index.php?view=sales" name="sales" method="post" onsubmit='return formValidator()'>
<table width="100%" 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" 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>    
		<select name="year" id="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 * $od_tax;
$total = $od_qty * $pd_price + $od_shipping_cost + $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>
<form action="salesprint.php" method="POST" name="report11">
<input type="hidden"  name="oid2" value="<?php echo $month;?>">
<input type="hidden"  name="oid3" value="<?php echo $year;?>">
<input type="submit"  name="enter" value="Printable">
</form>

xyn · September 12, 2007

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in salesreport.php on line 18

i have this error sir..

and sir what is $extra? it's undefined...

what is outputted when you try this:

<?
$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' $extra";

if(isset($_POST['month']) && !isset($_POST['year'])) #	Month set; year not set
{
$extra = "AND MONTH(od_date) = '".intval($_POST['month'])."'";
}
elseif(!isset($_POST['month']) && isset($_POST['year']))	#	Year Set; No Month
{
$extra = "AND YEAR(od_date) = '".intval($_POST['year'])."'";
}
else
{
  die('we have problems with _POST');
}

$result = mysql_query($sql);
echo(mysql_num_rows($sql));

?>

theBrent · September 12, 2007

Notice: Undefined variable: extra in salesreport.php on line 5

we have problems with _POST

xyn · September 12, 2007

ok. at the top of the page put...

error_report(E_ALL & ~E_NOTICE);

as for the second error. theres problems with your form.

theBrent · September 12, 2007

should i redo the form?

undefined function with: error_report(E_ALL & ~E_NOTICE);

xyn · September 12, 2007

error_reporting(E_ALL & ~E_NOTICE);

Sign In

select a month or a year not both

Recommended Posts

theBrent

Link to comment

Share on other sites

xyn

Link to comment

Share on other sites

theBrent

Link to comment

Share on other sites

xyn

Link to comment

Share on other sites

theBrent

Link to comment

Share on other sites

theBrent

Link to comment

Share on other sites

xyn

Link to comment

Share on other sites

theBrent

Link to comment

Share on other sites

xyn

Link to comment

Share on other sites

theBrent

Link to comment

Share on other sites

xyn

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information