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?

Please also read both of these links: [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Sep 29 '19 at 22:53

Nick · Accepted Answer · 2019-09-30T10:03:33.743

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);

edited Sep 30 '19 at 10:03

answered Sep 29 '19 at 22:44

Nick

138,499
22
57
95

thanks.I tried some things like this : if(strlen($types) === 1){ $stmt->bind_param($types, $vars[0]); } else if(strlen($types ) === 2){ $stmt->bind_param($types , $vars[0], $vars[1]); } else if(strlen($bindTypes) === 3){ $stmt->bind_param($types , $vars[0], $vars[1], $vars[2]); } $stmt->bind_param($types , $vars); but I steal have an error : Uncaught Error: Call to a member function bind_param() on boolean in .. – WebDev Sep 30 '19 at 09:30
Did you try the code I posted? That error means the prepare failed can you try `echo $link->error;` after the prepare? – Nick Sep 30 '19 at 09:38
Yes Its was an error (space) in my mysql query. I fixed it now and it works. Thank you very much have a nice day :) – WebDev Sep 30 '19 at 10:01
@WebDev if it is working, please consider marking the answer accepted. Then it will rank higher when other users search for similar questions. – Nick Sep 30 '19 at 10:04
Have you any idea plz how to insert an array with a prepared statement? My array : $array_info[] = " (NULL, '" . $idQuery . "', '" . addslashes($header) . "', '" . addslashes($data[($indData - 1)]) . "')"; Initial query $sql = "INSERT INTO `info_client` (`idInfoClient`, `idQuery`, `libInfo`,`valueInfo`) VALUES " . implode($array_info, ",") . ";"; I change it on : $sql = "INSERT INTO `info_client` (`idInfoClient`, `idQuery`,`libInfo`, `valueInfo`) VALUES(?);"; $stmt = $link->prepare($sql); $stmt->param_count('s',implode($array_info[], ",")); – WebDev Sep 30 '19 at 12:45
@WebDev it's hard to be sure but I think you want something like this https://3v4l.org/OWbI8 I think it's worth asking a new question. – Nick Sep 30 '19 at 12:53

Change SQL Query to a prepared statement with condition

1 Answers1