MS SQL Server + PHP : How to make a proper optional Multi Criteria Search with Multiple Tables

Question

CONTEXT :

I'm doing my first multi criteria search with multiple tables, using Angular 5, PHP and MS SQL server.

Basically, the user can choose between 4 fields to find a report :

date of the report
the topic (of the the meeting)
the city (where the meeting was)
the report writer

He can use one field to do his search or more to have a more precise search.

So once the user clicked on the send button, i'm sending a object to PHP with the criteria. For example, looking like this :

Object {

crd_date_reunion: null

crd_sujet: null

nom_com: "AFFOUX"

nom_redacteur: "cher Utilisateur"

}

ISSUE

So once in PHP, i've found some answers on stackoverflow like this one to have a good algorithm to handle the optional multi criteria on several tables. So i would like to adapt it to my case. But i don't get how to adapt the CREATE PROCEDURE part with my PHP object. Here is what i did :

// I get my object here in searchCriteria
    $searchCriteria = json_decode(file_get_contents('php://input'));
    $bdd = connectDb("XXX");

    $cmdSQL = "SELECT
              VUE_COMMUNE.com_nom_court,
              crd_sujet,
              crd_date_reunion,
              VUE_INFO_UTILISATEUR.NomUtilisateur
              FROM COMPTE_RENDU
              INNER JOIN VUE_INFO_UTILISATEUR ON COMPTE_RENDU.CptUtilisateur = VUE_INFO_UTILISATEUR.CptUtilisateur
              INNER JOIN VUE_COMMUNE ON COMPTE_RENDU.com_insee = VUE_COMMUNE.com_insee
              WHERE (:date IS NULL OR (crd_date_reunion = :date))
                AND (:topic IS NULL OR (crd_sujet = :topic))
                AND (:city IS NULL OR (VUE_COMMUNE.com_nom_court = :city))
                AND (:redac IS NULL OR (VUE_INFO_UTILISATEUR.NomUtilisateur = :writer))";

    $stmt = $bdd->prepare($cmdSQL);
    $stmt->bindParam(':date', $searchCriteria->crd_date_reunion, PDO::PARAM_STR);
    $stmt->bindParam(':topic', $searchCriteria->crd_sujet, PDO::PARAM_STR);
    $stmt->bindParam(':city', $searchCriteria->nom_com, PDO::PARAM_STR);
    $stmt->bindParam(':writer', $searchCriteria->nom_redacteur, PDO::PARAM_STR);

    $stmt->execute();
    while($row = $stmt->fetchObject()){
        $return[] = array(
            'com_insee' => formData($row->com_insee),
            'crd_sujet' => formData($row->crd_sujet),
            'crd_date_reunion' => formData($row->crd_date_reunion),
            'CptUtilisateur' => formData($row->CptUtilisateur),
            'crd_n_ref' => formData($row->crd_n_ref),
            'crd_n_affaire' => formData($row->crd_n_affaire),
            'crd_date_redac' => formData($row->crd_date_redac),
            'crd_autre_info' => formData($row->crd_autre_info)
        );
    }

The syntax does not seems good (maybe not adapt for MS SQL server). I've got a good ol' "Syntax error" concerning the WHERE part while trying it.

My adaptation seems wrong. I mean i skipped the CREATE PROCEDURE (+ the option Recompile part) because i don't understand how to use it for my case. My values are simply in my object. But I've maybe misunderstood it.
I think i need some enlightenment on the subject.

Is it doable using this ? Or do i need to try another way ? All i want is, at least, the method i should use.

Thank you very much

score 0 · Answer 1 · answered Jun 13 '18 at 07:09

So i solved my issue by changing my way of doing. I was missing some knowledge on PHP and it take me some time to think about a simple algorithm. So this is what i wrote :

$searchCriteria = json_decode(file_get_contents('php://input')); $bdd = connectDb("XXXXX");

    $conditions = [];
    $parameters = [];

    // conditional statements for each of my fields
    if (!empty($searchCriteria->nom_com))
    {
        $conditions[] = 'VUE_COMMUNE.com_nom_court = ?';
        $parameters[] = $searchCriteria->nom_com;
    }

    if (!empty($searchCriteria->crd_date_reunion))
    {
        $conditions[] = 'crd_date_reunion = ?';
        $parameters[] = $searchCriteria->crd_date_reunion;
    }

    if (!empty($searchCriteria->crd_sujet))
    {
        $conditions[] = 'crd_sujet = ?';
        $parameters[] = $searchCriteria->crd_sujet;
    }

    if (!empty($searchCriteria->nom_redacteur))
    {
        $conditions[] = 'VUE_INFO_UTILISATEUR.NomUtilisateur = ?';
        $parameters[] = $searchCriteria->nom_redacteur;
    }


    $baseQuery = "SELECT
          VUE_COMMUNE.com_nom_court,
          crd_sujet,
          crd_date_reunion,
          NomUtilisateur,
          crd_n_ref,
          crd_n_affaire,
          crd_date_redac,
          crd_autre_info
        FROM COMPTE_RENDU
        INNER JOIN VUE_INFO_UTILISATEUR ON COMPTE_RENDU.CptUtilisateur = VUE_INFO_UTILISATEUR.CptUtilisateur
        INNER JOIN VUE_COMMUNE ON COMPTE_RENDU.com_insee = VUE_COMMUNE.com_insee";

// A condition to build the query with $conditions
    if ($conditions)
    {
        $baseQuery .= " WHERE ".implode(" AND ", $conditions);
    }

    $stmt = $bdd->prepare($baseQuery);
    $stmt->execute($parameters);
    $stmt->fetchObject();

MS SQL Server + PHP : How to make a proper optional Multi Criteria Search with Multiple Tables

1 Answers1