PDB select with LIKE

PHP
1 
<?php

declare(strict_types=1);
error_reporting(-1);
ini_set('display_errors', '1'); 

$dbName='myDB'; 
$options = [
    PDO:: ATTR_ERRMODE                                        => PDO:: ERRMODE_EXCEPTION,
    PDO:: ATTR_DEFAULT_FETCH_MODE          => PDO:: FETCH_ASSOC,
    PDO:: ATTR_EMULATE_PREPARES                 => false,
]; 
try {  
$dbc = new PDO("mysql:host=localhost;dbname=$dbName;charset=utf8", "******", "********", $options); 
} catch(PDOException $e) {
throw new PDOException($e->getMessage(), (int)$e->getcode()); 
} 

$key='a'; 

$sql="SELECT title FROM myTable 
WHERE title like '%$key%' ORDER BY editDate DESC LIMIT 10";
$searchQ=$dbc-> prepare ($sql); 
while ($searchL=$searchQ->fetch()) {
echo $searchL['title'] .'<br>';
}

My page has the code above.
I expect the page produce the following.

Hwever, the page produce nothing.
I think my code above is NOT the PDO way with “LIKE”.

How can I produce my target result?

2

You prepare the statement, but you never execute it.
I don’t know where $key is coming from, but the method you use will not prevent injection, with the variable directly in the query.
Here is an example of how to use a prepared statement with a variable LIKE clause.

1 Like
3

It’s coming from $_GET[‘key’].

The code below also produces nothing but blank.

$key=$_GET['key']; 
$like='%$key%';

$sql="SELECT title FROM myTable 
WHERE title like ? ORDER BY editDate DESC LIMIT 10";
$searchQ=$dbc-> prepare ($sql);
$searchQ->execute([$like]); 
while ($searchL=$searchQ->fetch()) {
echo $searchL['title'] .'<br>';
}

Do you see what’s wrong in the code above?

4 
$sql = 'SELECT title FROM myTable WHERE title LIKE :key DESC LIMIT 10'; // Set the Search Query:

$stmt = $dbc->prepare($sql); // Prepare the query:

$stmt->execute(['key' => "%" . $key]); // Execute the query with the supplied user's parameter(s):

$records = $stmt->fetchAll();

foreach ($records as $record) {
    echo "Title: " . $record['title'] . "<br>";
}

There might be some rewriting of the code as I just converted from an old php sandbox script that I did.

5

Yes, this:

See https://kodlogs.com/blog/2482/difference-between-single-quotes-and-double-quotes-in-php

2 Likes
6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.