Change SQL Query to a prepared statement with condition

Question

I'm trying to change this query to a query with prepared statement, but I have some problem because of conditions. This is my basic query :

function ResponseByQuery($link,$idQuery,$Boutique=null, $agency=null){
    $from_agence = "";
    $req_agence = "";
    $req_boutique = "";

    if($Boutique!=null){
        $req_boutique = " AND C.idUser ='" . $Boutique . "' ";  
    }

    if($agency!=null){
        $from_agence = ", infos_client as IRC2";
        $req_agence = " AND IRC.idClient = IRC2.idClient                    
                    AND IRC2.valueInfo = '". $agency."'";

    }           
    $sql = "SELECT  distinct(C.idClient), R.indiceRequete
            FROM    `infos_client` as IRC, client as C, user as U, requete as R ".$from_agence." 
            WHERE   IRC.idQuery='" . $idQuery . "'".
            $req_boutique. 
            "AND IRC.idCl = C.idCl          
            AND C.idUser=U.idUser".$req_agence;     
    $result = mysqli_query($link,$sql) or die("Query (- $sql -) failed");
    $count = mysqli_num_rows($result);   
}

I changed it to this :

function ResponseByQuery($link,$idQuery,$Boutique=null, $agency=null){
    $from_agence = "";
    $req_agence = "";
    $req_boutique = "";

    if($Boutique!=null){
        $req_boutique = " AND C.idUser ='" . $Boutique . "' ";  
    }

    if($agency!=null){
        $from_agence = ", infos_client as IRC2";
        $req_agence = " AND IRC.idClient = IRC2.idClient                    
                    AND IRC2.valueInfo = '". $agency."'";

    }           
    $sql = "SELECT  distinct(C.idClient), R.indiceRequete
            FROM    `infos_client` as IRC, client as C, user as U, requete as R ".$from_agence." 
            WHERE   IRC.idQuery =?".
            $req_boutique. 
            "AND IRC.idCl = C.idCl          
            AND C.idUser=U.idUser".$req_agence;     
    $stmt = $link->prepare($sql);
    $stmt->bind_param('i', $idQuery);
    $result = $stmt->execute() or die("Query (- $sql -) failed");
    $result = $stmt->get_result();
    $count = mysqli_num_rows($result);   
}

but I don't know how can I change conditions($req_boutique,$req_agence) to prepared statement?

Answer 1

You can replace the inlined variables in your $req_boutique and $req_agence conditions with placeholders, and then conditionally bind values to them:

if($Boutique!=null){
    $req_boutique = " AND C.idUser = ? ";  
}

if($agency!=null){
    $from_agence = ", infos_client as IRC2";
    $req_agence = " AND IRC.idClient = IRC2.idClient                    
                AND IRC2.valueInfo = ? ";

}           
$sql = "SELECT  distinct(C.idClient), R.indiceRequete
        FROM    `infos_client` as IRC, client as C, user as U, requete as R ".$from_agence." 
        WHERE   IRC.idQuery =? ".
        $req_boutique. 
        "AND IRC.idCl = C.idCl          
        AND C.idUser=U.idUser".$req_agence;     
$stmt = $link->prepare($sql);
$types = 'i';
$vars = [$idQuery];
if ($Boutique != null) {
    $types .= 's';
    $vars[] = $Boutique;
}
if ($agency!= null) {
    $types .= 's';
    $vars[] = $agency;
}
$stmt->bind_param($types, ...$vars);