Drop down selection problem

[UK-1991]

Hello,

I have created a page for users to view their status reports but I have created 2 dropdown 1 for month second for year i want that user click on the month it show only that months status not for the whole table status and at the end of the table they can see their totals like total sales done in this month.

Here is my sql table structure

Table name dailyreport

uid int(11) NOT NULL DEFAULT '0',
uname varchar(255) NOT NULL DEFAULT '0',
rid int(11) NOT NULL AUTO_INCREMENT,
fullname varchar(11) NOT NULL,
date date NOT NULL DEFAULT '0000-00-00',
campaign varchar(255) NOT NULL,
tleads int(255) NOT NULL,
cleads int(255) NOT NULL,
t_sales int(255) NOT NULL,
nsure int(255) NOT NULL,
fdelivered mediumtext NOT NULL,
comments mediumtext NOT NULL,

Here is my php code for the page

<form name="form1" method="post" action="uaccreports.php?show=1&uid=<?php echo $uid?>">
<label>
    <p>Select Month</p>
    <p>
    <select name="select1">
        <option value="1">January</option>
        <option value="2">February</option>
        <option value="3">March</option>
        <option value="4">April</option>
        <option value="5">May</option>
        <option value="6">June</option>
        <option value="7">July</option>
        <option value="8">August</option>
        <option value="9">September</option>
        <option value="10">October</option>
        <option value="11">November</option>
        <option value="12">December</option>
    </select>
    </P>
</label>

<label>
    <p>Select Year</p>
    <p>
    <select name="select2">
        <option value="2004">2004</option><option value="2005">2005</option>
        <option value="2006">2006</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><option value="2019">2019</option>
        <option value="2020">2020</option><option value="2021">2021</option>
        <option value="2022">2022</option><option value="2023">2023</option>
        <option value="2024">2024</option><option value="2025">2025</option>
        <option value="2026">2026</option><option value="2027">2027</option>
        <option value="2028">2028</option><option value="2029">2029</option>
        <option value="2030">2030</option><option value="2031">2031</option>
    </select>
    </p>
</label>

<label>
    <input type="submit" value="Show" name="button" class="button" style="float:left;margin-left:25%;">
</label>

    <br>
    <table border="1" cellpadding="2" cellspacing="2">
    <tr><th>Date</th><th>Total Leads</th><th>Completed Leads</th><th>Sales</th><th>Comments</th></tr>
        <?php 
            $month = $_POST["select1"];
            if($_GET["show"]==1) {
                $month = $_POST["select1"];
                $year = $_POST["select2"];
            }else {
                $month = date("m");
                $year = date("Y");
            }
            if($_GET["can"]==1) { $user = $_GET["user"]; }
            $startday = 1;
            if($month==1||$month==3||$month==5||$month==7||$month==8||$month==10||$month==12) {
            $endday = 31; 
            }
            if($month==4||$month==6||$month==9||$month==11) {
            $endday = 30;
            }
            if($month==2){
            if($year%4==0){
            $endday = 29;
            }else {
            $endday = 28;
            }
            }
            echo "<script language = 'javascript'>
            form1.select1.options[",$month-1,"].selected = true;
            form1.select2.options[",$year-2004,"].selected = true;
            </script>";

                $date1 = $year."-".$month."-".$startday;
                $date2 = $year."-".$month."-".$endday;

        $recordset5 = mysql_query("select * from dailyreport where date between '".$date1."' and '".$date2."' and (uid=0 or (uid=".$uid.")) order by date");
            $days = 0;
        while($record5 = mysql_fetch_array($recordset5)) {
            $days = $days + 1;
        }

        $result3    = mysql_query("SELECT * FROM dailyreport WHERE uid='" .$uid. "'");
        while($row3 = mysql_fetch_array($result3)) {
            echo "<tr><td>" . $date = $row3['date'] . "</td>";
            echo "<td>" . $totalleads = $row3['tleads'] . "</td>";
            echo "<td>" . $totalcomp  = $row3['cleads'] . "</td>";
            echo "<td>" . $sales      = $row3['op_acc'] . "</td>";
            echo "<td>" . $notsure      = $row3['nsure'] . "</td><tr/>";
        }

            $a = $totalcomp ;
            $b =  array_sum($a);
            echo $b;

        echo"</table>";
        echo"<label><p style='width:55%;margin-left:20%;'>Total sales in this Month</p><p>",$b,"</p><label>";
        $recordset3 = mysql_query("select * from users where uid=".$user);
        while($record3 = mysql_fetch_array($recordset3)){
            echo"<script>
            for(k=0; k< form1.select3.length; k++) {
                if(form1.select3.options[k].text =='".$record3["ulogin"]."') {
                form1.select3.options[k].selected = true;
            }
        }
        </script>";
        }                                                       
        ?>
</table>
</form>

Here I hope you undertand what I am trying to do I hope you guyz will help me out with this concirn

[diafol]

Sorry haven't the whole thing just your stuff at the top. You could have a status report entered with today's date (directly as DATETIME DEFAULT CURRENT_TIMESTAMP) or from a datepicker on a form if you wanted to store the whole date. Alternatively, you could extract the YEAR() and MONTH() of a whole date:

INSERT INTO .... YEAR($date), MONTH($date) ....

That way you avoid too much processing.

If you store status dates as Y-m-d then you can extract using the same type of reasoning...

... WHERE YEAR(`date`) = 2015 AND MONTH(`date`) = 4

or the dynamic equivalent...

... WHERE YEAR(`date`) = $searchYear AND MONTH(`date`) = $searchMonth

You use the GROUP BY month, year to get totals such as COUNT(somefield) or SUM(somefield) etc.