start sql select * where fieldName = true

Archived from the Xataface Users forum.

PolderBoy — Thu Aug 20, 2009 5:49 am

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

---

shannah — Thu Aug 20, 2009 11:20 am

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’

---

PolderBoy — Fri Aug 21, 2009 4:01 am

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

---

PolderBoy — Fri Aug 21, 2009 4:48 am

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

---

PolderBoy — Fri Aug 21, 2009 5:34 am

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’));
      }
   }`