Export and sqlFilters

Discuss MySQL Ajax Table Editor

Export and sqlFilters

Postby yamkopp » Mon May 23, 2011 3:10 pm

Hello,

I am experiencing a strange behaviour. In my code

Code: Select all
   function initiateEditor()
   {
            $TEST_ID = $_REQUEST['test_id'];

            $tableColumns['id'] = array('display_text' => 'ID', 'perms' => 'X');
            $tableColumns['student_id'] = array('display_text' => 'Nome', 'req'=> 'true', 'perms' => 'EVCTXQM', 'join' => array('table' => 'tg_students', 'column' => 'id', 'display_mask' => "tg_students.name", 'type' => 'left'));
            $tableColumns['test_id'] = array('display_text' => 'Test', 'perms' => '');
            $tableColumns['classe_id'] = array('display_text' => 'Classe', 'req'=> 'true', 'perms' => 'EVCTXQM', 'join' => array('table' => 'tg_classi', 'column' => 'id', 'display_mask' => "concat(tg_classi.anno, ' ', tg_classi.nome)", 'type' => 'left'),);
            $tableColumns['punti'] = array('display_text' => 'Punti', 'req'=> 'true', 'perms' => 'EVCTXQM');
            $tableColumns['maxpunti'] = array('display_text' => 'Max', 'req'=> 'true', 'perms' => 'EVCTXQM');
            $tableColumns['voto'] = array('display_text' => 'Voto/10', 'req'=> 'true', 'perms' => 'EVCTXQM');
            $tableColumns['esito'] = array('display_text' => 'Risposta/Esatta', 'req'=> 'true', 'perms' => 'EVCTXQM', 'textarea' => array('rows' => 10, 'cols' => 50), 'on_add_fun' => array(&$this,'checkFormatDomanda'), 'on_edit_fun' => array(&$this,'checkFormatDomanda'));

            $tableName = 'tg_student_test';
            $primaryCol = 'id';
            $errorFun = array(&$this,'logError');
            $permissions = 'XEDQUI';

            require_once('php/AjaxTableEditor.php');
            $this->Editor = new AjaxTableEditor($tableName,$primaryCol,$errorFun,$permissions,$tableColumns);
            $this->Editor->setConfig('tableInfo','cellpadding="1" width="600" class="mateTable"');
             $this->Editor->setConfig('tableTitle','Riepilogo Test');
            $this->Editor->setConfig('sqlFilters',"test_id = '$TEST_ID'");
        }


everythings work as expected on the screen but the export button produce an empty file.
If I remove the line
$this->Editor->setConfig('sqlFilters',"test_id = '$TEST_ID'");
then the export function works :shock: .
Is it normal?

Thank you,
Luigi
yamkopp
 
Posts: 31
Joined: Sun Jan 24, 2010 11:20 am

Re: Export and sqlFilters

Postby KarelB » Mon May 23, 2011 3:18 pm

Your SQL filter is malformed and I doubt you see anything in the tableview.
Try this:
Code: Select all
$this->Editor->setConfig('sqlFilters',"test_id = '".$TEST_ID."'");
KarelB
 
Posts: 458
Joined: Mon Dec 01, 2008 11:49 pm
Location: The Netherlands

Re: Export and sqlFilters

Postby yamkopp » Mon May 23, 2011 8:14 pm

Thank you for the reply.
I tried your solution but still does not work!
I see result of the query on the screen (i.e. it filters correctly) but the export does not work (file of 0 bytes).
If I remove the row with the sqlfilter I see the table unfiltered and the export works :roll:

Thanks,
Luigi

EDIT: it seems to be related with the syntax. If I use
$this->Editor->setConfig('sqlFilters',"test_id = 28'");
everything work properly. I will try other way to combine the string.
yamkopp
 
Posts: 31
Joined: Sun Jan 24, 2010 11:20 am

Re: Export and sqlFilters

Postby admin » Mon May 23, 2011 8:54 pm

I believe the problem is that on the export, the test_id variable is not getting passed so the sqlfilter is set to
Code: Select all
$this->Editor->setConfig('sqlFilters',"test_id = ''");

which most likely returns nothing.

If you are using the free version try searching for the following line of code in AjaxTableEditor.php
Code: Select all
$html .= '<button onclick="window.location=\''.$_SERVER['PHP_SELF'].'?export=1\'">'.$this->langVars->btnExport.'</button>&nbsp;';

and replace it with
Code: Select all
         $exportUrl = $_SERVER['PHP_SELF'];
         $exportUrl .= isset($_SERVER['QUERY_STRING']) > 0 && strlen($_SERVER['QUERY_STRING']) > 0 ? '?'.$_SERVER['QUERY_STRING'].'&export=1' : '?export=1';
         $html .= '<button onclick="window.location=\''.$exportUrl.'\'">'.$this->langVars->btnExport.'</button>&nbsp;';
admin
Site Admin
 
Posts: 1502
Joined: Fri Jul 11, 2008 1:34 am

Re: Export and sqlFilters

Postby yamkopp » Mon May 23, 2011 9:29 pm

Thank you for the answer. Your solution works great.

In the meanwhile I have found even this solution reading the post
http://www.mysqlajaxtableeditor.com/discussmate/viewtopic.php?f=4&t=550&p=2382&hilit=+sqlfilters#p2382

So in the constructor of the class, the 'export' zone, I added the lines between begin and end. Not it works although I have not really understood what I have done. But it should have made 'test_id' visible to the export function.

Code: Select all
      else if(isset($_GET['export']))
      {
         session_start();
         ob_start();
         $this->mysqlConnect(); // Comes from extended Common class
         $this->initiateEditor();
                       // begin ---------------
                        if(isset($_GET['test_id']))
                        {  $_SESSION['test_id'] = $_GET['test_id'];
                           $this->Editor->setConfig('sqlFilters',"test_id = '".$_GET['test_id']."'");
                        }
                        else if(isset($_SESSION['test_id']))
                        { $this->Editor->setConfig('sqlFilters',"test_id = '".$_SESSION['test_id']."'");
                        }
                        // end ------------------
         echo $this->Editor->exportInfo();
         header("Cache-Control: no-cache, must-revalidate");
         header("Pragma: no-cache");
         header("Content-type: application/x-msexcel");         
         header('Content-Type: text/csv');
         header('Content-Disposition: attachment; filename="'.$this->Editor->tableName.'.csv"');
         exit();
      }
yamkopp
 
Posts: 31
Joined: Sun Jan 24, 2010 11:20 am

Re: Export and sqlFilters

Postby admin » Mon May 23, 2011 9:51 pm

I recommend editing the AjaxTableEditor.php class to automatically pass get variables to the export. Saving the get variables in a session variable works, but you would have to do it for each subsequent script that has get variables modifying the mate configuration.
admin
Site Admin
 
Posts: 1502
Joined: Fri Jul 11, 2008 1:34 am

Re: Export and sqlFilters

Postby KarelB » Tue Jun 07, 2011 12:30 pm

I cannot get this method to work in the paid version.
Whatever quoting or escaping I try the & is always translated into &amp;

Any idea's
Karel
KarelB
 
Posts: 458
Joined: Mon Dec 01, 2008 11:49 pm
Location: The Netherlands

Re: Export and sqlFilters

Postby KarelB » Sun Jun 26, 2011 9:28 am

For the paid version I made it work as follows:
In AjaxTableEditor.php find the handleExport call and replace with:
Code: Select all
$extra = isset($_SERVER['QUERY_STRING']) > 0 && strlen($_SERVER['QUERY_STRING']) > 0 ? $_SERVER['QUERY_STRING'] : '';
$html .= '<button onclick="handleExport(\''.$_SERVER['PHP_SELF'].'\',\''.$extra.'\');">'.$this->langVars->btnExport.'</button>&nbsp;';


In ajax_table_editor.js replace the handleExport function with :
Code: Select all
function handleExport(url,extra)
{
   var sessionData = getSessionData();
   if (extra.length){
      window.location = url+'?export=1&'+extra+'&session_data='+sessionData;
   } else {
      window.location = url+'?export=1&session_data='+sessionData;
   }
   
}
KarelB
 
Posts: 458
Joined: Mon Dec 01, 2008 11:49 pm
Location: The Netherlands


Return to Open Discussion

Who is online

Users browsing this forum: No registered users and 11 guests

cron