Build a dynamic query for a search form.

[eluser]Sorasora[/eluser]
Hello everybody,
I'm coding a little application with a search form which allows the user to select several options, and I'm having troubles to implement the model part.
I've already made it without codeIgniter, but I want to rebuild all my app in CI, so I would like to use the Active Record. (To have the possibility to switch between POSTgre, mySQL...)
Here is my code in his old version :

Code:

<?php
include 'bd_connexion.php';

$datedebut = htmlspecialchars(trim($_POST['datedebut']));  
$datefin = htmlspecialchars(trim($_POST['datefin']));
$jours = htmlspecialchars(trim($_POST['jours']));
$ferie = htmlspecialchars(trim($_POST['ferie']));
$horairedebut = htmlspecialchars(trim($_POST['horairedebut']));
$horairefin = htmlspecialchars(trim($_POST['horairefin']));
$labo = htmlspecialchars(trim($_POST['labo']));
$corres = htmlspecialchars(trim($_POST['corres']));

//Formation of datedebut and datefin
$i=0;
if($datedebut != 'AAAA/MM/JJ' && $datedebut != ''){
  $choix[$i++] = "DATE(MIN_VAL_PVT)";
  $choix[$i++] = ">='".$datedebut."'";
  }
  
if($datefin != 'AAAA/MM/JJ' && $datefin != ''){
  $choix[$i++] = "DATE(MIN_VAL_PVT)";
  $choix[$i++] = "<='".$datefin."'";
  }

//Formation of jours
$jour = array();
foreach (explode(",",$jours) as $elt){
  $jour[] = $elt;
}

if($jours != "null") {
  $nom=$i++;
  $val=$i++;
  if(count($jour) >1){
  $choix[$nom] = " (JOUR_PVT= '";}
  else{ $choix[$nom] = "JOUR_PVT= '";}
  $j=0;
  $condition="";
  $choix[$val]="";
  while($jour[$j]){
  if($j>0){$condition = "' OR JOUR_PVT = '";}
  $choix[$val].=$condition.$jour[$j];
  $j++;
  }
  $choix[$val].="' ";
  if(count($jour) >1){$choix[$val].=") ";}
}

//Formation of ferie
if($ferie == "oui"){
$choix[$i++] = "DATE(MIN_VAL_PVT) NOT IN (";
$choix[$i++] = "SELECT DATE(JourDate) FROM joursferies)";
}

//Formation of horairedebut and horairefin
if ($horairedebut == "00:00" && $horairefin == "23:59"){}
  else{
  $choix[$i++] = "TIME(MIN_VAL_PVT)";
  $choix[$i++] = ">'".$horairedebut."'";
  $choix[$i++] = "TIME(MIN_VAL_PVT)";
  $choix[$i++] = "<'".$horairefin."'";}

//Formation of labo
$lab = array();
foreach (explode(",",$labo) as $elt){
  $lab[] = $elt;
}

if($labo != "null") {
  $nom=$i++;
  $val=$i++;
  if(count($lab) >1){
  $choix[$nom] = " (LABO_INTERNE= '";}
  else{ $choix[$nom] = "LABO_INTERNE= '";}
  $j=0;
  $condition="";
  $choix[$val]="";
  while($lab[$j]){
  if($j>0){$condition = "' OR LABO_INTERNE = '";}
  $choix[$val].=$condition.$lab[$j];
  $j++;
  }
  $choix[$val].="' ";
  if(count($lab) >1){$choix[$val].=") ";}
}

//Formation of corres
   $corr = array();
foreach (explode(",",$corres) as $elt){
  $corr[] = $elt;
}

if($corres != "null") {
  $nom=$i++;
  $val=$i++;
  if(count($corr) >1){
  $choix[$nom] = " (CODE_CORR= '";}
  else{ $choix[$nom] = "CODE_CORR= '";}
  $j=0;
  $condition="";
  $choix[$val]="";
  while($corr[$j]){
  if($j>0){$condition = "' OR CODE_CORR = '";}
  $choix[$val].=$condition.$corr[$j];
  $j++;
  }
  $choix[$val].="' ";
  if(count($corr) >1){$choix[$val].=") ";}
}
  
  
//FORMATION OF THE QUERY :
if (empty($choix))
$critere = "";
else{
  $critere = " AND ".$choix[0].$choix[1]." ";
  for($j=2;$j<$i;$j+=2){
   $critere .=" AND ".$choix[$j].$choix[$j+1]." ";
  }
}
   $query= "SELECT (AVG(TIME_TO_SEC(TIMEDIFF(MAX_VAL_TEC,MIN_VAL_PVT)))*1000) AS delaitec,
   (AVG(TIME_TO_SEC(TIMEDIFF(MAX_VAL_BIO,MAX_VAL_TEC)))*1000) AS delaibio,
   (AVG(TIME_TO_SEC(TIMEDIFF(MAX_VAL_TEC,MIN_VAL_PVT)))*1000)+(AVG(TIME_TO_SEC(TIMEDIFF(MAX_VAL_BIO,MAX_VAL_TEC)))*1000) AS delaitotal,
   COUNT( MAX_VAL_TEC ) AS Nblignes
   FROM nice_bio_delaimoy
   WHERE CHAP='SANG_LOCAL'".$critere;

   $req  = mysql_query($query) or die("echec");

$rows = array();
while($r = mysql_fetch_assoc($req)) {
    $rows[] = $r;
}

header('Content-type: application/json');
echo json_encode($rows);

// Disconnect
mysql_close();



?&gt;

The variables aren't the problem here,
the biggest problems are how to build the "WHERE" clause with Active Record, and how to translate my complex SELECT clause in CI ("(AVG(TIME_TO_SEC(TIMEDIFF(MAX_VAL_TEC,MIN_VAL_PVT)))*1000)").

Thanks for your help !

[eluser]Glazz[/eluser]
I think you need to use $this->db->query() it's almost the same thing as mysql_query..

[eluser]Sorasora[/eluser]
Yes but if I use it, I lost the Active Record advantage...
I don't want to copy/paste my old code. ^^"
And I also ask for a better approach to construct dynamic query (because mine is ugly and non optimal).