Filter Table on Dropdown Value change

Hi,

I am using the CC to display the table. I like to filter my table on the change of Dropdown Value.

Steps I have done is.

    {new_record}<br />
    <table style="text-align: left; width: 100%;" border="0" cellpadding="2" cellspacing="0">
      <tbody>
<tr>
<select name="pyear" id="pyear">
  <option value="0" default>Select</option>
  <option value="2000">2000</option>
  <option value="2001">2001</option>
  <option value="2002">2002</option>
  <option value="2003">2003</option>
  <option value="2004">2004</option>
  <option value="2005">2005</option>
  <option value="2006">2006</option>
  <option value="2007">2007</option>
</select>
</tr>
        <tr>
          <td style="font-weight: bold; width: 5%;">ID</td>
          <td style="font-weight: bold; width: 5%;">Project Year</td>
          <td style="font-weight: bold; width: 30%;">Project Name</td>
          <td style="font-weight: bold; width: 10%;">Activity Name</td>
          <td style="font-weight: bold; width: 15%;">
Project_Initiator</td>
          <td style="font-weight: bold; width: 15%;">School Name Code</td>
          <td style="font-weight: bold; width: 10%;">Village</td>
          <td style="font-weight: bold; width: 15%;">Estimated Cost Rs</td>
        </tr>
      </tbody>
    </table>
    <div class="clr" style="border-bottom: 3px solid #222; padding: 3px; margin-bottom: 10px;"></div>

In "header" section I have pasted this coding.

    <table style="text-align: left; width: 100%;" border="0" cellpadding="2" cellspacing="0">
      <tbody>
        <tr<?php if ($i % 2) echo ' style="background-color: #EBEBEB;"';?>>
          <td style="width: 5%;">{edit_record} {fabrik_internal_id}</td>
          <td style="width: 5%;">{Project_Year}</td>
          <td style="width: 30%;">{Project_Name}</td>
          <td style="width: 10%;">{Activity_Name}</td>
          <td style="width: 15%;">{Project_Initiator}</td>
          <td style="width: 15%;">{School_Name_Code}</td>
          <td style="width: 10%;">{Village}</td>
          <td style="width: 15%;">{Estimated_Cost_Rs}</td>
        </tr>
      </tbody>
    </table>
    <?php $i++ ?>

In "Body" section I have this coding.

I get the Table Data's with the Dropdown.

I need to Filter my table will the Dropdown Value on change event.

I don't Know what is the code to Filter the table on change.

My "Where" Section is Empty now.

Kindly help me on this.

Regards,
Shoaib.

Hi shoaib,

There are several existing threads on doing this. You need to add a submit button to the Header box code.

Then in the WHERE box add the code to check the value of the select and filter the results:

<?php
$pyear = JRequest::getInt('pyear', '', 'post');
if ( $pyear > 0 ) {
    echo " WHERE `pyear` = '$pyear' ";
}
?>

Bob

Hi Bob,

Thanks for your reply. It works fine now. But if I want to use Subquery here how it is possible.

$result = mysql_query( "SELECT v.Village,v.Mandal,v.District,v.State,p.Estimated_Cost_Rs,p.Project_Academic_Year,DATE_FORMAT(p.Expected_Start_Date,'%m-%d-%Y') ,DATE_FORMAT(p.Expected_Complete_Date,'%m-%d-%Y'),p.fabrik_internal_id FROM Project as p JOIN Project_Location as v ON v.fabrik_internal_id = p.Village WHERE p.Activity_Name='Study Centres' AND p.Status_Id='Compleated'" )

This is my Subquery. How it is possible to apply this to the CC. Because I need the Dropdown filter here.

Regards,
Shoaib🙂

Hi Shoaib,

The current release of ChronoForms doesn't support subqueries. It may be possible to use a View to get the same result (I've not tested that yet). And there is a hacked version on ChronoConenctivity in the forums here that does accept sub-queries - but I've not tested it, nor, as far as I now, has Max.

Bob

Grayhead

how do you get the submit button to work ? i put the code in my form, but is doesnt'do anything

Hi stefandeblock ,

Please check that you have "Enable Form Tags" set to 'Yes'

Bob

Hello grayhead, i can't get it to work. I have put my code in here. I want a form that gives the selection of different sportsgames. This works. it display the choices. It gets them from another field from the dbase. What i want is that i select a sportsgame and press on submits it displays the users who have subscribed to this sports game. the sportsgames are having 2 fields in the dbase to see which they are. first field is : 'wedstrijd'. This is the name of the sportsgame. the second is a wedstrijd_id. Which is a number. One of them can be used to make the selection.

WHERE :

<?php
$wedstrijd = JRequest::getInt('wedstrijd', '', 'post');
if ( $wedstrijd > 0 ) {
    echo " WHERE `wedstrijd` = '$wedstrijd' ";
}
?>

HEADER :

<div class="form_item">
  <div class="form_element cf_text"> <span class="cf_text">Actuele inschrijvingen RKHAV atleten</span> </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_text"> <span class="cf_text"></span> </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Wedstrijd :</label>
    <select class="cf_inputbox validate-selection" id="select_2" size="1" title="selecteer wedstrijd"  name="wedstrijd">
    <option value="2">selecteer wedstrijd</option>
      
<?php
$sql= mysql_query("SELECT * FROM wedstrijden")or die(mysql_error());
$nSql = mysql_num_rows($sql);
if($nSql > 0){
  while($fSql = mysql_fetch_assoc($sql)){
    echo '<option value="'.$fSql['wedstrijden_id'].'">'.$fSql['wedstrijdnaam'].'</option>';
  }
}
?>
    </select>    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_text"> <span class="cf_text"></span> </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_button">
    <input value="bekijk inschrijvingen" name="button_1" type="submit" />
  </div>
  <div class="cfclear"> </div>
</div>

BODY :

<table style="text-align: left; width: 100%;" border="1" cellpadding="2" cellspacing="0">

<STYLE TYPE="text/css">
<!--
TD{font-size: 7pt;}
--->
</STYLE>


<tr><td>
{naam}<td>{licentienummer}<td>{geboortejaar}<td>{geslacht}<td>{categorie}<td>{startnummer}<td>{onderdeel1}<td>{pronderdeel1}<td>{onderdeel2}<td>{pronderdeel2}<td>{onderdeel3}<td>{pronderdeel3}<td>{onderdeel4}<td>{pronderdeel4}
<td><tr></table>

<td>

Hi stefandeblock ,

From a quick look the code seems OK. What isn't working?

Bob

Hi stefandeblock,

Just to let you know that I have just posted a new ChronoConnectivity How-To Document on filtering Listings here.

Bob

Hi

well, when i open the form the dbase fields are directly displayed (all). When i select an option and press submit, no data is displayed any more..

it seems "$wedstrijd" does not takes the right value. Check the value first and then filter it.
🙂

i've been looking, didn'tfind it yet. Does the 'fault' also establishes the fact that the whole dbase is directly displayed instead of some part of it after filtering ? I don't want to see any record before selecting.
You said $wedstrijd. It's now a textfield, is that ok , or should it be something else ?

Hi stefandeblock,

I think shaoib is correct here. Looking at the code it seems that you may be getting the name and the id mixed up

$wedstrijd = JRequest::getInt('wedstrijd', '', 'post');
if ( $wedstrijd > 0 ) {

getInt and > 0 both expect an integer ID but you are saying that 'wedstrijd' is a text field. perhaps this should be

$wedstrijd_Id = JRequest::getInt('wedstrijd_id', '', 'post');
if ( $wedstrijd_id > 0 ) {
    echo " WHERE `wedstrijd_id` = '$wedstrijd_id' ";
}
?>

Bob

Hi

i changed the code, but is doesn't solve the problem.
This is how the data and fields are in the dbase :

Chrono Connectivity - Inschrijvingen Connection

Inschrijvingen_id Naam Licentienr Geboortejaar Geslacht Categorie Wedstrijd Startnummer Onderdeel1 PR onderdeel1 Onderdeel2 PR onderdeel2 Onderdeel3 PR onderdeel3 Onderdeel4 PR onderdeel4 Wedstrijden_id
1 testpersoon 103961 1974 m Heren masters 35+ Testwedstrijd 1 100 meter 100 200 meter 200 400 meter 400 800 meter 800 1

The first row are the fieldnames,the second the data

Hi stefandeblock,

In the Header box (the select part of the code):

<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Wedstrijd :</label>
    <select class="cf_inputbox validate-selection" id="wedstrijd" size="1" title="selecteer wedstrijd"  name="wedstrijd">
    <option value="2">selecteer wedstrijd</option>
     
<?php
$wedstrijd = JRequest::getInt('wedstrijd', 0, 'post');
$db =& JFactory::getDBO();
$query = "
    SELECT `Wedstrijden_id`, `Wedstrijd`
        FROM `#__wedstrijden` ;
";
$db->setQuery($query);
$data = $db->loadObjectList();
foreach ( $data as $d ) {
  $select = '';
  if ( $d->wedstrijden_id == $wedstrijd ) {
    $select = "selected='selected'";
  }   
  echo '<option value="'.$d->wedstrijden_id.'" $select >'.$d->wedstrijd.'</option>';
}
?>
    </select>   
  </div>
  <div class="cfclear"> </div>
</div>

In the WHERE box:

<?php
$wedstrijd = JRequest::getInt('wedstrijd', '', 'post');
if ( $wedstrijd ) {
    echo " WHERE `wedstrijden_id` = '$wedstrijd' ";
}
?>

Bob

Hi, thanks

i replaced the code. What now happens :
when i open the form it stil directly displays al the dbase data. In the select menu there are no more choices visible, only 'selecteer wedstrijd'. When i press the submit button it filters the table to the data that matches 'wedstrijden_id = 2. (the 'wedstrijden_id' can be a nummber from 1 to.....)

so, somethinh changed, it filters, but without the possibilty to select a sports games

Hi stefandeblock ,

Then I'm afraid that you'll need to debug my code. Apart from anything else I don't have a copy of the database table to test with.

Bob

Hi

fixed some things and now the dropdown field displayes the different choices which represent 'wedstrijd', so that''s ok again. But.. the main problem is still the selecting. When i select a 'wedstrijd' now, and press submit, nothing is displayed. here, the code like it now is. I can't have anything to do with rights ?

WHERE :

<?php
$wedstrijd = JRequest::getInt('wedstrijd', '', 'post');
if ( $wedstrijd ) {
    echo " WHERE `wedstrijden_id` = '$wedstrijd' ";
}
?>

HEADER :

<div class="form_item">
  <div class="form_element cf_text"> <span class="cf_text">Actuele inschrijvingen RKHAV atleten</span> </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Wedstrijd :</label>
    <select class="cf_inputbox validate-selection" id="wedstrijd" size="1" title="selecteer wedstrijd"  name="wedstrijd">
    <option value="2">selecteer wedstrijd</option>
     
<?php
$wedstrijd = JRequest::getInt('wedstrijd', 0, 'post');
$db =& JFactory::getDBO();
$query = "
    SELECT `wedstrijden_id`, `wedstrijdnaam`
        FROM `wedstrijden` ;
";
$db->setQuery($query);
$data = $db->loadObjectList();
foreach ( $data as $d ) {
  $select = '';
  if ( $d->wedstrijden_id == $wedstrijd ) {
    $select = "selected='selected'";
  }   
  echo '<option value="'.$d->wedstrijden_id.'" '.$select.' >'.$d->wedstrijdnaam.'</option>';
}
?>
    </select>   
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_button">
    <input value="bekijk inschrijvingen" name="button_1" type="submit" />
  </div>
  <div class="cfclear"> </div>
</div>

BODY :

<table style="text-align: left; width: 100%;" border="1" cellpadding="2" cellspacing="0">

<STYLE TYPE="text/css">
<!--
TD{font-size: 7pt;}
--->
</STYLE>


<tr><td>
{naam}<td>{licentienummer}<td>{geboortejaar}<td>{geslacht}<td>{categorie}<td>{startnummer}<td>{onderdeel1}<td>{pronderdeel1}<td>{onderdeel2}<td>{pronderdeel2}<td>{onderdeel3}<td>{pronderdeel3}<td>{onderdeel4}<td>{pronderdeel4}
<td><tr></table>

<td>

Hi stefandeblock,

Try putting a debug line in here to see what is being created

<?php
$wedstrijd = JRequest::getInt('wedstrijd', '', 'post');
if ( $wedstrijd ) {
    echo " WHERE `wedstrijden_id` = '$wedstrijd' ";
    $mainframe->enqueuemessage(" WHERE `wedstrijden_id` = '$wedstrijd' ");
}
?>

Bob

ok, will do. I'll get back to you

Hi,problem is solved, the dropdown works. The problem was that 'wedstrijden_id' from the original table was not written rightly in de other table, so the id's didnt match.

I got one more question. It works fine, etc. But is it possible to display not any data until a selection is made. I don't want to see the whole dbase until a selection is made ?

and....thanks again for the help and patience until now..

hi stefandeblock,

I think that if you add default value for the 'wedstrijd' that doesn't exist in the WHERE Box that you can effectively block the display:

<?php
$wedstrijd = JRequest::getInt('wedstrijd', '99999', 'post');
if ( $wedstrijd ) {
    echo " WHERE `wedstrijden_id` = '$wedstrijd' ";
}
?>

Bob

hi, thanks. the blocking works fine. I have another question. I found the script for exporting the data to an csv file. Put it in a chronoform, and it does the trick (found the bugfix🙂.

My question, when looking at the form i have here, how do i combine this (so the selection of this form) to export selective records to a csv file