Mysql LIKE condition in PHP PDO

Question

I was used to using the condition LIKE passing the parameter in connection with the MySQL and PHP via MySQLi, and I started using PDO and no result is selected when passing the value to the condition through the parameter.

Returns 0 record passing a parameter in the LIKE condition`

$query = "SELECT po.post_id, po.*, cat.*, 
(SELECT COUNT(*) FROM post_galeria WHERE gal_post_token = po.post_token) AS count_gal 
FROM posts AS po 
LEFT JOIN post_categorias AS cat 
ON po.post_cat = cat.cat_id 
WHERE po.post_cat <> '' 
AND po.post_titulo LIKE '%:termSearch%' 
OR po.post_conteudo LIKE '%:termSearch%' 
AND po.post_capa IS NOT NULL 
ORDER BY po.post_data_cad DESC ;";

$stmt = $myconn->prepare($sql_data);
$stmt->bindParam(":termSearch", $termSearch, PDO::PARAM_STR);
$stmt->execute();
$total = $stmt->rowCount();

echo "COUNT: " . $total; // 0 return

The value of the variable $termSearch has been checked and is being passed correctly, that is not the problem.

Returns all records changing the parameter to a static value in the condition LIKE

$query = "SELECT po.post_id, po.*, cat.*, 
(SELECT COUNT(*) FROM post_galeria WHERE gal_post_token = po.post_token) AS count_gal 
FROM posts AS po 
LEFT JOIN post_categorias AS cat 
ON po.post_cat = cat.cat_id 
WHERE po.post_cat <> '' 
AND po.post_titulo LIKE '%camisetas%' 
OR po.post_conteudo LIKE '%camisetas%' 
AND po.post_capa IS NOT NULL 
ORDER BY po.post_data_cad DESC ;";

$stmt = $myconn->prepare($sql_data);
// $stmt->bindParam(":termSearch", $termSearch, PDO::PARAM_STR);
$stmt->execute();
$total = $stmt->rowCount();

echo "COUNT: " . $total; // Todos os 543 registros

Answer 1

It is a classic error when starting with PDO, in your SQL make these changes in the location where you have LIKE:

"SELECT po.post_id, po.*, cat.*, 
 (SELECT COUNT(*) FROM post_galeria WHERE gal_post_token = po.post_token) 
    AS count_gal 
    FROM posts AS po 
    LEFT JOIN post_categorias AS cat 
    ON po.post_cat = cat.cat_id 
    WHERE po.post_cat <> '' 
    AND po.post_titulo LIKE :termSearch 
    OR po.post_conteudo LIKE :termSearch
    AND po.post_capa IS NOT NULL 
    ORDER BY po.post_data_cad DESC ;";

placing only the location and name for substitution.

When passing the value in the variable also pass the percent (%) as in the following code:

$termSearch = '%camiseta%'; // <-------
$stmt = $myconn->prepare($sql_data);
$stmt->bindParam(":termSearch", $termSearch, PDO::PARAM_STR);
$stmt->execute();
$total = $stmt->rowCount();

_Reference:

• The PDO class
• bindParam
• Using LIKE in bindParam for a Mysql PDO Query