[SOLVED]how do i sort by month from database?

theBrent · September 2, 2007, 3:00pm

hi guys i’m new here… and i need help

i have a module that sorts all reports by date but it doesn’t work i’m not getting any errors… 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:

<?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>

could it be my query is incomplete? i’am really not that good in php so please help! thank you!

Q1712 · September 2, 2007, 3:07pm

isset() returns a bool (true or false) just to check whether a value is set (in ur case is submitted with the form).
that means $month and $year wount be set to the date but to true or false

[php]
if(isset($_POST[‘month’]) and !empty($_POST[‘month’])) $month = $_POST[‘month’];
else $month = intval(date(‘m’));
if(isset($_POST[‘year’]) !empty($_POST[‘year’])) $year = $_POST[‘year’];
else $year = intval(date(‘Y’));
[/php]

havn’t checked the rest of the code yet.

theBrent · September 2, 2007, 3:26pm

thanks for the reply sir!

should i add this to my code sir? i was really thinking that the error would be in the query… thanks for lending a hand sir!

Q1712 · September 2, 2007, 3:43pm

the code was a replacement of:
[php]$month = (isset($_POST[‘month’]));
$year = (isset($_POST[‘year’]));[/php]

but i had a little securety issue in there. this one is better:
[php]
if(isset($_POST[‘month’]) and !empty($_POST[‘month’])) $month = intval($_POST[‘month’]);
else $month = intval(date(‘m’));
if(isset($_POST[‘year’]) !empty($_POST[‘year’])) $year = intval($_POST[‘year’]);
else $year = intval(date(‘Y’));
[/php]

P.S: some links to understand that code:
http://php.net/isset
http://php.net/empty
http://php.net/date

theBrent · September 2, 2007, 4:01pm

wwoooooooooooooottttt!!! thank you sir!!! so much!!!

can i have a request??

i want to display ALL the records so that it would be easy too look…

could that be another query??

Q1712 · September 2, 2007, 4:25pm

sry as i said i havn’t had a look at the rest of the code.

to get that done u need to extend the if.

[php]$totalsale = 0;
$totalqty = 0;

if(isset($_POST[‘month’]) && !empty($_POST[‘month’]) && isset($_POST[‘year’]) && !empty($_POST[‘year’)) {

$month = intval($_POST['month']);
$year = intval($_POST['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.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);
…[/php]

or maybe better (because shorter and easyer to 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’])."’";
if(isset($_POST[‘year’]) && !empty($_POST[‘year’))
$sql.=" AND YEAR(od_date) = ‘".intval($_POST[‘year’])."’";

$result = dbQuery($sql);
…[/php]
this would add the posibillity to select just a month or a year.

theBrent · September 2, 2007, 4:41pm

there you go! thank you sir! hope to hear from you soon!! thanks a lot!