Refining PDO results from MySQL database with Text Box

Question

I'm currently retrieving data from a MySQL database using PDO. I'm using a foreach to display the data onto a page, and wish for the user to be able to enter a search term into a input field, hit the submit button, and then only results who's title contains the search term are returned.

Here is my current code:

file_functions.php - Where the SQL query function is located

function getbycategory($category, $limit){
    global $db;
    if (isset($category) AND $category != "all") {
        $sm = $db->prepare ("SELECT * FROM parts WHERE main_category = :category");
        $sm->bindParam(':category', $category, PDO::PARAM_INT);
    } else {
        $sm = $db->prepare ("SELECT * FROM parts");
    }
        $sm->execute();
    return $sm->fetchAll();
}

files.php - Where the results are displayed

$files = getbycategory($_GET['filter']); 

foreach($files as $file){    
echo'<div class="col-lg-" id="file-'.$file['id'].'">
        <div class="file-list-item first" id="">
            <img class="file-image" height="120px" width="180px" src="'.$file['image_url'].'" />
            <div class="file-text">
                <h3><strong>'.$file['name'].'</strong></h3>
                Submitted by: '.$file['submitter'].'<br/>
                Author: '.$file['author'].'<br />
                Category: '.ucfirst($file['subcategory']).'<br />
                Description: '.substr($file['description'],0,45).'...
            </div>
            <div class="download">
                <a target="_blank" href="'.$file['download_url'].'" class="btn-success btn btn-default">Download</a>
                <a href="'.baseurl.'/broken.php?id='.$file['id'].'" class="btn btn-default">Report as Broken</a><br /><br />';
                    if($file['is_broken']){
                        echo '<span class="broken"><i data-toggle="tooltip" data-placement="left" id="broken" title="This file has been reported as broken and is awaiting review." class="fa fa-warning fa-2x"></i></span>';
                    }


                echo '

            </div>
        </div>
    </div>';
};

?>

Below is the form used to refine the results. Currently the filter dropdown menu works for the filter, but the search term does not. This is what I wish to implement

<form method="get">
<select name="filter">
    <option <?php if($_GET['filter'] == "all" OR !isset($_GET['filter'])){echo 'selected';} ?> value="all">View All Files</option>
    <option <?php if($_GET['filter'] == "1") {echo 'selected';} ?> value="1">View Vehicles Only</option>
    <option <?php if($_GET['filter'] == "2") {echo 'selected';} ?> value="2">View Lighting Equiptment</option>
</select>
<input type="submit" value="Filter Results"/><br /><br />
<input type="text" name="search" placeholder="Enter a search term" />
<input type="submit" value="Search Results"/>
</form>

To summarise, I wish to use the text field in the bottom snippet of code to refine the results displayed in files.php by comparing their title to the search term.

I wish to compare the search term to the $file['name'].

Many thanks.

For simple *string contains* style matching, use a `LIKE` comparison, eg `AND LOWER(name) LIKE CONCAT('%', LOWER(:search), '%')`. Your collation may not require the `LOWER()` calls (ie, it may be case insensitive). For more complex matching, see http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html — Phil, Oct 16 '14 at 00:42
@Phil, is it possible to have optional sections of a statement, for example: if a parameter is not defined then it will just return all results, but if a search is defined then "LIKE" will be added into the statement? — , Oct 16 '14 at 00:53
Easy, just build the query conditionally. `if (!empty($search)) $query .= ' AND name LIKE...';` — Phil, Oct 16 '14 at 00:58

Phil · Answer 1 · 2014-10-16T02:22:56.213

Ok, let me step you through it using a simple LIKE comparison...

First, the method signature. Globals are a bad idea so we'll include the PDO instance

function getbycategory(PDO $db, $category = null, $search = null, $limit = null)

Now, build the query and collect parameters as you go.

$params = [];
$where = [];
if ($category !== null) {
    $where[] = 'main_category = :category';
    $params[':category'] = [
        'val' => (int) $category,
        'type' => PDO::PARAM_INT
    ];
}
if ($search !== null) {
    $where[] = "LOWER(`name`) LIKE CONCAT('%', LOWER(:search), '%')";
    $params[':search'] = [
        'val' => (string) $search,
        'type' => PDO::PARAM_STR
    ];
}
$query = sprintf('SELECT * FROM `parts` %s', implode(' AND ', $where));
if ($limit !== null) {
    $query .= ' LIMIT :limit';
    $params[':limit'] = [
        'val' => (int) $limit,
        'type' => PDO::PARAM_INT
    ];
}

$stmt = $db->prepare($query);
foreach ($params as $key => $param) {
    // use bindValue to avoid problems with variable references in the loop
    // see http://stackoverflow.com/questions/4174524/binding-params-for-pdo-statement-inside-a-loop
    $stmt->bindValue($key, $param['val'], $param['type']);
}

$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);

Refining PDO results from MySQL database with Text Box

1 Answers1