Bind parameters with PDO in PHP

Question

I am using PDO in php. But when my query have any keyword like " ' " means hyphen it breaks and through an error. I ready through on internet and find solution to bind parameters with query and it works fine. But the issue is i am building the query in loop and i am not able to bind parameters within loop. Here is code in which i am splitting the array with space and run query on every keyword. The first 3 words will have only like query and more then 3 words i am using loop to concatenate the all array elements and same with more then 6 words i am using MATCH query. Is there any way to escape that hyphen or how can we bind parameters using loop in my case?

        $keyword = ($_POST['keyword']);
        $keyword_array = split(' ',$keyword);

        /* Query For first Three Words */
        if(count($keyword_array)<=3){
                    $sql = "SELECT * FROM faq WHERE question  LIKE '%$keyword%' limit 14";
        }
        /* Query through all array when words are greater then 3 */
        if(count($keyword_array)< 6){
            $sql = "SELECT * FROM faq WHERE question ";
                for($i = 0 ; $i<count($keyword_array); $i++){

                if($i==0){
                                $sql.=" LIKE '%$keyword_array[$i]%'";
                }else{
                                $sql.=" or question LIKE '%$keyword_array[$i]%' ";
                }
            }
                        $sql .= " ORDER BY question ASC LIMIT 0, 8";
        }
        /* Appl FULL TEXT in natual language mode once we have enough phrase */
        else if(count($keyword_array)>=6){
                $sql = "SELECT * FROM faq WHERE ";
                    for($i = 0 ; $i<count($keyword_array); $i++){

                    if($i==0){
                                    $sql.=" MATCH (answer) AGAINST ('$keyword_array[$i]' in natural language mode) ";
                    }else{
                                    $sql.=" or MATCH(answer) AGAINST('$keyword_array[$i]' in natural language mode) ";
                    }
            }
                $sql .= "  limit 0,5";
        }


            $execute_faq_query = $conn->query($sql);
            $execute_faq_query->setFetchMode(PDO::FETCH_ASSOC);

            while ($list = $execute_faq_query->fetch()){
}

Answer 1

When building a dynamic query you need to separate those parts of the query that are static from those that are dynamic.

You can see that the following code is static.

"SELECT * FROM faq ";

The rest of the code is dynamic. When filtering records the WHERE clause is used and the AND & OR operators are used to filter records based on more than one condition. The AND operator displays a record if both the first condition AND the second condition are true. The OR operator displays a record if either the first condition OR the second condition is true. so for the first condition WHERE is used but after that AND or OR must be used(using OR in your example)

// Static code
sql = "SELECT * FROM `faq`"
// Set initial condition to WHERE       
clause = "WHERE";       
if( !empty( filter )){
    Add clause to sql 
    Add condition to sql
    change clause to OR or AND as required
}

Repeat for each filter Note the filter is not changed until a filter is applied and remains changed once changed. The remaining static code, if any,is added after all the filters have been handled.

I have used Switch Case to apply filters and unnamed parameters ?.

Use "lazy" binding when possible - passing data into execute will dramatically shorten your code. See PDO info.

//Test $POST[] remove after testing
$_POST['keyword'] ="one two three four five six";
$keyword = ($_POST['keyword']);
$keyword_array = split(' ',$keyword);
$words = count($keyword_array);
echo $words;
//You need an array to store parameters
 $paramArray =array();
//Initial clause
$clause = "WHERE";
//Start with a basic stub
$sql = "SELECT * FROM faq ";
switch (true) {
    case $words <= 3:
        $sql .= " $clause question LIKE ?";
        $keyword = "%$keyword%";
        array_push($paramArray,$keyword);
        $limit = " LIMIT 14";
        break;

    case $words < 6:
        for($i = 0 ; $i<count($keyword_array); $i++){
            $sql .= " $clause question LIKE ?";
            $keyword = "%$keyword_array[$i]%";
            array_push($paramArray,$keyword);
            $clause = "OR";
            $limit = " ORDER BY question ASC LIMIT 0, 8";
        }
        break;

    case $words >=6:
        $clause = "";
        for($i = 0 ; $i<count($keyword_array); $i++){
            $sql.=" $clause MATCH (answer) AGAINST (? in natural language mode) ";
            array_push($paramArray,$keyword_array[$i]);
            $clause = "OR";
            $limit = "  limit 0,5";
        }
        break;   
}
//echo query and parameter array remove after testing
echo $sql;
echo "<br>";  
print_r($paramArray);

//Prepare and execute query 
$execute_faq_query = $conn->prepare($sql);
$execute_faq_query->execute($paramArray);
$execute_faq_query->setFetchMode(PDO::FETCH_ASSOC);
 while ($list = $execute_faq_query->fetch()){
 }