PHP Search Function Fails to Return Results

Question

I am in the process of refactoring a PHP script (moving from mysqli to PDO). It's purpose is to create a list of resources for a website and allow users to filter them. There is also a basic search function. Everything in the refactored script is working fine, except the search function. Attempting to search returns no results. The information is present in the database and I can't find any errors in the Apache logs. Here is the code:

require_once('web_misc_config');

...    

$search=(isset($_GET['search']) ? $_GET['search'] : null);
$search= addslashes($search); 
$searchletter=(isset($_GET['searchletter']) ? $_GET['searchletter'] : null);

//This while loop creates the searched version of the A to Z list.
if (!empty($search)){
    $result = $con->prepare("SELECT title,summary,url,coverage,format FROM 

    dbs where title like :search or summary like :search");
    $result->bindParam(':search', $search, PDO::PARAM_STR);
    $result->execute();

    while($row = $result->fetch())
    {
        $url=$row['url'];
        $title=$row['title'];
        $summary=$row['summary'];
        $coverage=$row['coverage'];
        $format=$row['format'];

        echo ('<p><h6><a href="' . $url . '">' . $title . '</a></h6>
                        <br />' . $summary . '</p>');
    } 
}

//This block creates the filtered and searched version of the list.
elseif (!empty($searchletter)) {
    $result = $con->prepare("SELECT title,summary,url,coverage,format,fletter FROM dbs where fletter = :searchletter");
    $result->bindParam(':searchletter', $searchletter);
    $result->execute();

    while($row = $result->fetch())
    {
        $url=$row['url'];
        $title=$row['title'];
        $summary=$row['summary'];
        $coverage=$row['coverage'];
        $format=$row['format'];

        echo ('<p><h6><a href="' . $url . '">' . $title . '</a></h6>
                        <br />' . $summary . '</p>');
    }
}  

//This block loop creates the inital A to Z list.
else {
    $result = $con->prepare("SELECT title,summary,url,coverage,format FROM dbs");
    $result->execute();

    while($row = $result->fetch())
    {
        $url=$row['url'];
        $title=$row['title'];
        $summary=$row['summary'];
        $coverage=$row['coverage'];
        $format=$row['format'];

        echo ('<p><h6><a href="' . $url . '">' . $title . '</a></h6>
                        <br /> ' . $summary . '</p>');
    } 
}   
$result = null;
$con = null;

The ELSEIF and ELSE blocks work fine. The initial, unfiltered list is populated and users can filter it alphabetically. They are included here for completeness and comparison. The problem is with the while loop in the IF block (under the first comment). It is evaluating to false causing a blank screen to appear instead of search results. It should evaluate to true as long as results are retrieved from the database. Can anyone see something I might have missed?

You maybe want to make use of `%` in your binding when using `LIKE`. See if that helps fetch some rows. Here is the SO answer(s): https://stackoverflow.com/questions/2722136/ — Rasclatt, Oct 30 '18 at 21:11
You shouldn't use `addslashes()` when you're using prepared statements. — Barmar, Oct 30 '18 at 21:14
Possible duplicate of [How do I create a PDO parameterized query with a LIKE statement?](https://stackoverflow.com/questions/583336/how-do-i-create-a-pdo-parameterized-query-with-a-like-statement) — mickmackusa, Oct 30 '18 at 22:17

score 3 · Accepted Answer · answered Oct 30 '18 at 21:17

Since $search doesn't contain any wildcard characters, LIKE will be treated like = and look for an exact match. If you want to search for the it anywhere in the column, you need to add wildcards.

if (!empty($search)){
    $search = "%$search%";
    $result = $con->prepare("SELECT title,summary,url,coverage,format 
        FROM dbs 
        where title like :search or summary like :search");
    $result->bindValue(':search', $search, PDO::PARAM_STR);
    $result->execute();

That did it. I knew it had to be something simple. I also removed the addslashes as you suggested. — Mike P, Oct 31 '18 at 15:12

PHP Search Function Fails to Return Results

1 Answers1