FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Checkbox and Date filter with mysql and PHP





therimalaya
I got a problem with filtering records by date according to given criteria through Check-box.
I've a HTML form just like below:

Code:

<form>
  <table width="323" border="1">
    <tr>
      <td width="167" scope="col"><label>
        <input type="checkbox" name="firstQuarter" id="firstQuarter" />
        First Quarter          <br />
          <input type="checkbox" name="secondQuarter" id="secondQuarter" />
        Second Quarter<br />
        <input type="checkbox" name="thirdQuarter" id="thirdQuarter" />
        Third Quarter<br />
        <input type="checkbox" name="fourthQuarter" id="fourthQuarter" />
        Fourth Quarter<br />
      </label></td>
      <td width="140" scope="col"><label>
        <input type="checkbox" name="firstMonth" id="firstMonth" />
      First Month<br />
      <input type="checkbox" name="secondMonth" id="secondMonth" />
      Second Month<br />
      <input type="checkbox" name="thirdMonth" id="thirdMonth" />
      Third Month</label></td>
    </tr>
    <tr>
      <td scope="col">Record Count</td>
      <td scope="col"><span id="dispRecord"></span></td>
    </tr>
  </table>
  <p>&nbsp;</p>
  <label></label>
</form>

In this form, if i check out the 'first quarter' and then 'first month' and 'second month', the record of 'January' and 'March' should be displayed. In the similar manner, if i check out the 'third quarter' and 'third month', only the record of 'September' should be returned. How can it be possible. I've a MySQL database with table structure as,

Code:

mysql> describe transaction;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| transID      | int(11)      | NO   | PRI | NULL    | auto_increment |
| Sender       | varchar(255) | YES  |     | NULL    |                |
| Receiver     | varchar(255) | YES  |     | NULL    |                |
| mailNo       | int(11)      | YES  |     | NULL    |                |
| dispatchDate | date         | YES  |     | NULL    |                |
| itemWt       | float        | YES  |     | NULL    |                |
| itemNo       | int(11)      | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
SonLight
I don't see any code here for selecting the records, so I'm assuming you have the transaction table and an idea for the form, and want PHP code to select the proper transactions according to the quarter and month parameters.

I noticed one apparent mistake in your post. I believe you want 'first quarter' 'first month' and 'second month' to select January and February, not March. You apparently want to allow multiple months to be selected. I wondered if you also wanted to allow multiple quarters. My conclusion is why not, since that makes it more general.

An obvious but perhaps inefficient solution would be to select all records and reject those not in the target months. Ideally you'd like to "select trans where month is <targeted>" and the <targeted> part would be generated dynamically as a list of all months specified. I'm not expert about handling dates in php and mysql, but if we develop code that works in the select all -- reject some mode it should be easy to adapt it to build a select string or a temporary auxiliary table.

I would suggest a table for each of the twelve months (possibly an array of constants) specifying what quarter and what month-within-quarter has to be set to select it. Thus,

Jan q1 m1 ... May q2 m2 ... Dec q4 m3

For the "select and reject some" code, extract the month from the current row, probably as a number in the range 1 to 12. Look up that month, see if the required quarter is selected, if so see if the required month-within-quarter is set. Process the row if and only if both conditions are met,.

I hope this gives you a sense of direction, and either the php manual or expert coders here can suggest alternatives for directly selecting exactly the ones you want.
therimalaya
SonLight wrote:
I don't see any code here for selecting the records, so I'm assuming you have the transaction table and an idea for the form, and want PHP code to select the proper transactions according to the quarter and month parameters.

I noticed one apparent mistake in your post. I believe you want 'first quarter' 'first month' and 'second month' to select January and February, not March. You apparently want to allow multiple months to be selected. I wondered if you also wanted to allow multiple quarters. My conclusion is why not, since that makes it more general.

An obvious but perhaps inefficient solution would be to select all records and reject those not in the target months. Ideally you'd like to "select trans where month is <targeted>" and the <targeted> part would be generated dynamically as a list of all months specified. I'm not expert about handling dates in php and mysql, but if we develop code that works in the select all -- reject some mode it should be easy to adapt it to build a select string or a temporary auxiliary table.

I would suggest a table for each of the twelve months (possibly an array of constants) specifying what quarter and what month-within-quarter has to be set to select it. Thus,

Jan q1 m1 ... May q2 m2 ... Dec q4 m3

For the "select and reject some" code, extract the month from the current row, probably as a number in the range 1 to 12. Look up that month, see if the required quarter is selected, if so see if the required month-within-quarter is set. Process the row if and only if both conditions are met,.

I hope this gives you a sense of direction, and either the php manual or expert coders here can suggest alternatives for directly selecting exactly the ones you want.


Thanks For your great suggestion, I'll certainly try this out. I am thinking on using the two dimensional array with months inside each quarter, but I don't have any discrete idea yet, so i'm figuring this out.
Thank you very much for your concern..
therimalaya
I got an idea,

If I submit the form with the quarter check box value as 0, 3, 5 and 8 if it is checked, else 0 and for month check box 1, 2, 3 if checked else 0 then if i add the month and quarter value, i can get the month from 1-12.

But i got a problem. When submitting, the check box always return the same value that was set previously regardless of whether it is checked or not. I tried to make a Jquery code as below, but it is also not working. Some one please suggest.. Am i in right direction?

Code:
$(document).ready(function(){
      $('input#submit').click(function(){
         if($('input.checkBox').attr('checked')==true){
            $('input.checkBox').val($('input.checkBox').val());
         }
         else{
            $('input.checkBox').val(0);
         }
         $.post('dateManipulationBackend.php',{},function(output){
            $('.recordDisp').html(output);      
         });
      });   
   });

I set the class of all check box to 'checkBox' and further I set the value of check boxes value as i mentioned above.
Related topics
Seeking MySQL-php-Apache installer for WinXP
Easy PHP installation
PHP date() format for atom in php 4
mysql/php help please
What is the best way to save a date/timestamp in mysql
server with ready PHP
MySQL, PHP and APACHE
[ Newbie; Makin pages, mySQL, PHP, etc ]
[ Newbie; Makin pages, mySQL, PHP, etc ]
Is mySQL/php in safe mode on Frihosting?
PHP&MySql - Is there any server side caching of queries?
apache php and mysql on mac OS X
MySQL [php Mail()] and FriHost Mass mail rules
Spanish date functions in php and mysql
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.