Multiple where clause, how to handle OR and AND

MaxValembois

Hi everyone,

I have slowly moved all my software to use datatable and editor (v1.9 believe, just discover there is a V2 !).

Overall it works fine for my use but now I have the most complex sql request to handle.

It is an online catalogue with multiples fields that are potentially set by the user (some are fixed)

I managed to transfert all the data to the serverside with a variable :

on the client side I have this to get the data from the form :
"ajax": { url:'<?=$listeAppService?>', type:"POST", data: function ( d ) { d.ludo='<?=$_GET['ludo']?>'; var form = $("#rechercheForm").serializeArray(); d.dataform=JSON.stringify(form); alert(JSON.stringify(form)); } },

That works fine and I get something like that in the dataform variable :
[{"name":"recherche","value":"bou"},{"name":"nombre_joueurs1","value":"3"},{"name":"nombre_joueurs2","value":"20"},{"name":"age1","value":"8"},{"name":"age2","value":"20"},{"name":"optionLieu_de_Collecte","value":"Reignier"},{"name":"empruntables","value":"on"},{"name":"dispos","value":"on"}]

Now comes the bugger, on the serverside script I can do the filtering but It always consider only the "recherche" one and ignore the other if a value is set for "recherche". The rest seems to work fine and to add up to the other.

I think I use wrongly the or_where but I am not sure.
any help would be appreciated thanks a lot
Max

here is the serverside code of course :
`<?php

//include database and get the options we will use
include_once '../includes/sessionsCtle.php';
$optionsAffichees = explode('%%%', managerOptionsGenerales::getValeurText(62));
$optionsPersonnalisees = OptionTable::getListOptionAffichageForType('jeu');
//rollback as $liste[] = array('id' => $donnee['id'], 'nom' => $donnee['nom'], 'affichage' => $donnee['affichage']);

// Alias Editor classes so they are easy to use
use DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate;

$inclureAdherentsRadies = 0;

$editor = Editor::inst($db, $_SESSION['table_prefix'] . 'jeux as j')->fields(
Field::inst('j.id', 'id'),
Field::inst('j.est_un_jouet', 'est_un_jouet'),
Field::inst('j.nom', 'nom'),
Field::inst('j.image1', 'image1Affichage'),
Field::inst('j.image1', 'image1'),
Field::inst('j.image2', 'image2Affichage'),
Field::inst('j.image2', 'image2'),
Field::inst('j.emprunt_en_cour', 'emprunt_en_cour'),
Field::inst('j.nombre_emprunts', 'nombre_emprunts'),
Field::inst('j.emprunt_en_cour', 'emprunt_en_cour'),
Field::inst('j.reservation_en_cour', 'reservation_en_cour'),
Field::inst('j.joueurs_min', 'joueurs_min'),
Field::inst('if(j.joueurs_max<>j.joueurs_min,j.joueurs_max,"")', 'joueurs_max'),
Field::inst('FLOOR(j.age_min)', 'age_min'),
Field::inst('j.duree', 'duree'),
Field::inst('j.prix_achat', 'prix_achat'),
Field::inst('j.date_achat', 'date_achat'),
Field::inst('j.annee', 'annee'),
Field::inst('j.esar', 'esar'),
Field::inst('j.fabriquant', 'fabriquant'),
Field::inst('j.editeur', 'editeur'),
Field::inst('j.editeur2', 'editeur2'),
Field::inst('j.auteur', 'auteur'),
Field::inst('j.auteur2', 'auteur2'),
Field::inst('j.auteur3', 'auteur3'),
Field::inst('j.illustrateur', 'illustrateur'),
Field::inst('j.illustrateur2', 'illustrateur2'),
Field::inst('j.illustrateur3', 'illustrateur3'),
Field::inst('j.url_TTTV', 'url_TTTV'),
Field::inst('j.composition', 'composition'),
Field::inst('j.pitch', 'pitch'),
Field::inst('j.option1', 'option1'),
Field::inst('j.option2', 'option2'),
Field::inst('j.auteur2', 'auteur2'),
Field::inst('j.option3', 'option3'),
Field::inst('j.option4', 'option4'),
Field::inst('j.option5', 'option5'),
Field::inst('j.notes', 'notes'),
Field::inst('j.date_suppression', 'date_suppression'),
Field::inst('IF(j.statut=0,"Sur place",IF(j.emprunt_en_cour=0,"disponible",IF( j.reservation_en_cour=0,"emprunté","réservé")))', 'statutText'),
Field::inst('CONCAT("J-",RIGHT(CONCAT ("000000",j.id),6))', 'codeBarreLudoMAX')
);

$i = 1;
foreach ($optionsPersonnalisees as $optPersonalisee) {
if (in_array($optPersonalisee['id'] , $optionsAffichees)) {
$nomoptiontmp = $optPersonalisee['nom'];
$nomoptiontmp = str_replace('é', 'e', $nomoptiontmp);
$nomoptiontmp = str_replace('è', 'e', $nomoptiontmp);
$nomoptiontmp = str_replace('ê', 'e', $nomoptiontmp);
$nomoptiontmp2 = 'option_' . $nomoptiontmp;

    $editor->leftJoin($_SESSION['table_prefix'] . 'view_options as opt' . $i, 'j.id', '=', 'opt' . $i . '.id_associe AND opt' . $i . '.type="jeu" AND opt' . $i . '.nom="' . $nomoptiontmp . '" AND opt' . $i . '.valeur !=""')
            ->Fields(Field::inst('opt' . $i . '.valeur', $nomoptiontmp2));
}
$i++;

}

---

// here is the interesting part

if (isset($_POST['dataform'])) { //we check if there is data sent

// we uncode the data into a single array of array
$valeurs = json_decode($_POST['dataform'], true);
foreach ($valeurs as $t) {
    $dataform[$t['name']] = $t['value'];
}

// for each case we add the where clause

---

// this is the part that is not working or 'conflicting'

if (isset($dataform['recherche'])&& $dataform['recherche']!='') {
    $motclef=$dataform['recherche'];
    $editor->where(function ($q) use ($motclef) {
        $q->or_where('j.nom', '%'.$motclef.'%', 'LIKE');
        $q->or_where('j.auteur', '%'.$motclef.'%', 'LIKE');
        $q->or_where('j.illustrateur', '%'.$motclef.'%', 'LIKE');
        $q->or_where('j.editeur', '%'.$motclef.'%', 'LIKE');
    }
    );
}

---

// End of the conflicting part, the rest works just fine

if (isset($dataform['nombre_joueurs1'])&& $dataform['nombre_joueurs1']!='/') {
    $motclef=$dataform['nombre_joueurs1'];
    $editor->where(function ($q) use ($motclef) {
        $q->where('j.joueurs_min', $motclef, '<=');
        $q->where('j.joueurs_max', $motclef, '>=');
    }
    );
}

if (isset($dataform['nombre_joueurs2'])&& $dataform['nombre_joueurs2']!='/') {
    $motclef=$dataform['nombre_joueurs2'];
    $editor->where(function ($q) use ($motclef) {
        $q->where('j.joueurs_max', $motclef, '>=');
    }
    );
}

if (isset($dataform['age1'])&& $dataform['age1']!='') {
    $motclef=$dataform['age1'];
    $editor->where(function ($q) use ($motclef) {
        $q->where('j.age_min', $motclef, '>=');
    }
    );
}

if (isset($dataform['age2'])&& $dataform['age2']!='') {
    $motclef=$dataform['age2'];
    $editor->where(function ($q) use ($motclef) {
        $q->where('j.age_min', $motclef, '<=');
    }
    );
}

if (isset($dataform['empruntables']) OR isset($dataform['dispos'])) {
    $editor->where(function ($q) {
        $q->where('j.statut', '1', '=');
    }
    );
}

if (isset($dataform['dispos'])) {
    $editor->where(function ($q) {
        $q->where('j.emprunt_en_cour', '0', '=');
        $q->where('j.reservation_en_cour', '0', '=');
    }
    );
}


  $i = 1; //différentiateur pour les différentes options personnélisées

    foreach ($optionsPersonnalisees as $optPersonalisee) {
        if (in_array($optPersonalisee['id'] , $optionsAffichees)) { //si les options ont été selectionnées
                $option = OptionTable::getOptionIndexWithValues($optPersonalisee['id']);

                foreach ($option as $nom => $valeur) {

                    $nomoptiontmp = $nom; //on modifie le nom pour avoir le nom du champ
                    $nomoptiontmp = str_replace('é', 'e', $nomoptiontmp);
                    $nomoptiontmp = str_replace('è', 'e', $nomoptiontmp);
                    $nomoptiontmp = str_replace('ê', 'e', $nomoptiontmp);
                    $nomoptiontmp2 = 'option_' . $nomoptiontmp;

                     str_replace('_', ' ', $nom);// on modifie le nom pour avoir le nom de la variable a récupérer

                    if ($dataform['option'.$nom] != 'any') {
                        $valeurAchercher=$dataform['option'.$nom];
                        $editor->where(function ($q) use ($i,$valeurAchercher ) {
                            $q->where('opt' . $i . '.valeur', $valeurAchercher, '=');
                            }
                        );
                     }
                }
            }
            $i++;
        }

}

$editor->process($_POST)
->json();

`

MaxValembois

Hi all,

I found out,
I just needed to include the subqueries (the part I would wrote with comas in css) inside a new where function
I hope that will help some other person to figure that out
MAx

 if (isset($dataform['recherche'])&& $dataform['recherche']!='') {
        $motclef=$dataform['recherche'];
        $editor->where(function ($q) use ($motclef) {
            $q->where(function ($r) use ($motclef){
                $r->where('j.nom', '%'.$motclef.'%', 'LIKE');
                $r->or_where('j.auteur', '%'.$motclef.'%', 'LIKE');
                $r->or_where('j.illustrateur', '%'.$motclef.'%', 'LIKE');
                $r->or_where('j.editeur', '%'.$motclef.'%', 'LIKE');
            });
        })
        ;
    }

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

MaxValembois

It does not look like i can make the question solved as I answered Myself.

I am sure Allan will do that
Max

colin

Glad all sorted, and thanks for posting the solution!

Colin