Mysql LIKE condition in PHP PDO

Asked

Viewed 220 times

0

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
php mysql pdo

1 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:

  • @Eliseub. I’ll leave it the way I did on my machine and please delete that lot of message ...

Browser other questions tagged php mysql pdo

You are not signed in. Login or sign up in order to post.