Page 1 of 1

start sql select * where fieldName = true

PostPosted: Thu Aug 20, 2009 5:49 am
by PolderBoy
Dear all,

The main table has about 200.000 records at this moment but will go into the millions. The table has also a boolaen value which tells if record is active or not.
The user would like to see only the active records.

Can I set the major SQL statement?
And where?

Thanks,
Polderboy

PostPosted: Thu Aug 20, 2009 11:20 am
by shannah
Best thing is to use a security filter. In your delegate class, define a method:
Code: Select all
init(&$table){
    $table->setSecurityFilter(array('active'=>1));
}


Assuming that the column you want to filter on is named 'active'

almost what the client wants

PostPosted: Fri Aug 21, 2009 4:01 am
by PolderBoy
Thanks it is working like a charm but when the user clicks the 'find' tab and searches for active = 0 then there are no records found.... And there are records.

So when possible it should be the start sql but when the users wants to find the other records it should be possible.

Thanks in advance,
Polderboy

possible solution

PostPosted: Fri Aug 21, 2009 4:48 am
by PolderBoy
Me again.

I think I have found a solution:
I make a column in a table which holds a value: ShowAll YES or NO

Code: Select all
Function init(&$table)
{
      if ($record->val('ShowAll') == 'NO')
      {
          $table->setSecurityFilter(array('active'=>1));
       }
}

Would this work?
I will try anyway. Thanks for the answer.

PolderBoy

This works.

PostPosted: Fri Aug 21, 2009 5:34 am
by PolderBoy
Code: Select all
   function init(&$table)
   {
      include("conf.php");
      
      $con = mysql_connect($host, $user, $password);
      
         if (!$con)
         {
            die('Could not connect: ' . mysql_error());
         }
      
      mysql_select_db($name, $con);
      
      $sql="SELECT ShowAll FROM ShowAll";
      
         $result = mysql_query($sql);
         $row = mysql_fetch_array($result);
         $ShowAll = $row['ShowAll'];
      
      mysql_close($con);

      
      if($ShowAll == 'NO')
      {
         $table->setSecurityFilter(array('GeExporteerd'=>'NEE'));
      }
   }