union select on match against-CodePudding

I have the below code, I'm confused about binding the parameters on this query, I have 3 select statements and all three statements use the same $value;

The part I am confused about is here;

$select->bindValue(':search', $value, PDO::PARAM_STR);
$select->execute([':search' => $value]);

How do I bind the parameters to all three of my select statements, Thanks.

$query = "(SELECT *, 'archive' as flag 
          FROM `archiveArticles` 
          WHERE `stage` = 1 AND 
            MATCH(title,description) 
            AGAINST (:search IN BOOLEAN MODE);) UNION 
            (SELECT *, 'market' as flag 
          FROM `marketNewsArticles` 
          WHERE 
            MATCH(title,description) 
            AGAINST (:search IN BOOLEAN MODE);) UNION 
            (SELECT *, 'business' as flag 
          FROM `businessNewsArticles` 
          WHERE 
            MATCH(title,description) 
            AGAINST (:search IN BOOLEAN MODE);)";

$select = $dbh->prepare($query);                          
$select->bindValue(':search', $value, PDO::PARAM_STR);
$select->execute([':search' => $value]);

CodePudding user response：

The simple unambiguous way is to call them all something unique, then it does not matter about which driver you use or whether you have it configured to allow repeated placeholders.

$query = "  (SELECT *, 'archive' as flag 
            FROM `archiveArticles` 
            WHERE `stage` = 1 AND 
                MATCH(title,description) 
                AGAINST (:search1 IN BOOLEAN MODE)
            ) 
        UNION 
            (SELECT *, 'market' as flag 
            FROM `marketNewsArticles` 
            WHERE 
                MATCH(title,description) 
                AGAINST (:search2 IN BOOLEAN MODE)
            ) 
        UNION 
            (SELECT *, 'business' as flag 
            FROM `businessNewsArticles` 
            WHERE 
                MATCH(title,description) 
                AGAINST (:search3 IN BOOLEAN MODE)
            )";

$select = $dbh->prepare($query);                          
$select->bindValue(':search1', $value, PDO::PARAM_STR);
$select->bindValue(':search2', $value, PDO::PARAM_STR);
$select->bindValue(':search3', $value, PDO::PARAM_STR);
$select->execute();

Or to use the parameters in the execute call

$select->execute([':search1' => $value, ':search2' => $value, ':search3' => $value]);

And then remove the ; between queries, they are not necessary and PDO cannot execute multiple queries, and they would make this query into 3 seperate queries.