Search code examples
phpjquerymysqlfiltersearch-engine

Filter mysql search results using php and/or jquery

I've built a simple search engine for my site, the way it works is when the user enters a keyword it queries the database and displays the results.

My database table looks like this:

game_id title   developer   publisher   genre   release_date    platform          
rating  image_location  description

I want to allow the user to filter through the results once they have made a search, preferably without refreshing the page(only been coding for a month but have a general understanding of jquery and ajax).

Here is my code, im using includes so the search bar, search results and the page that queries are seperated.

page 1 search bar

<div id=\"top_search\">
            <form name=\"input\" action=\"search.php\" method=\"get\" id=\"search_form\">
            <input type=\"text\" id=\"keywords\" name=\"keywords\" size=\"128\" class=\"searchbox\" value=\"$defaultText\"> &nbsp;
            <select id=\category\" name=\"category\" class=\"searchbox\"> 
";
createCategoryList();
echo '
            </select> &nbsp;
            <input type="submit" value="Search" class="button" /> &nbsp;
            </form>
        </div>
        </header>
';

page 2 display results

<?php
session_start();
include ("includes/search_func.php");


if (isset($_GET['keywords'])){
$keywords = mysql_real_escape_string(htmlentities(trim($_GET['keywords']))); 

$errors = array();

if(empty($keywords)){
    $errors[] = 'Please enter a search term';
}else if(strlen($keywords)<3){
    $errors[] = 'Your search term must be at least 3 characters long';
}else if(search_results($keywords) == false){
            $errors[] = 'Your search for'.$keywords.' returned no results';
}
if(empty($errors)){
function diplay_results($keywords){
    $results = search_results($keywords);
    $results_num = count($results);


    foreach($results as $result ){
        echo '
            <div id="game_result">
                <a href= game_page.php?game_id='.$result['game_id'].'><img src= '.$result['image_location'].' id="image" /></a>
                <div id="main_title">
                    <a href= game_page.php?game_id='.$result['game_id'].'><h2 id="game_title">'.$result['title'].'&nbsp; &nbsp;</h2></a>
                    <h3 id="platform">for  &nbsp;'.$result['platform'].'</h3>
                </div>
                <p id=game_description>'.$result['description'].'</p>
                <div id="right_side">
                <h4 id="rating">'.$result['rating'].'</h4>
                </div>
                <hr id="hr"/>
            </div>  
        ';
}
}
}else{
    foreach($errors as $error){
        echo $error, '<br />';
    }
}

}
?>

<!DOCTYPE html>

<html lang="en">
<head>
    <title>Search</title>
    <link rel="stylesheet" href="css/main.css">
    <link rel="stylesheet" href="css/search.css">
</head>
<body>

    <div id="wrapper">
        <div id="main_aside">
                 //filter navigation will go here
        </div>
        <div id="main_section" class="header">
            <?php diplay_results($keywords)?>
        </div>
    </div>

</body>
</html>

page 3 querying database

<?php 
include 'includes/connect.php';


function search_results($keywords){
$returned_results = array();
$where = "";

$keywords = preg_split('/[\s]+/', $keywords);
$total_keywords = count($keywords);

foreach($keywords as $key=>$keyword){
    $where .= "title LIKE '%$keyword%'";
    if($key != ($total_keywords - 1)){
        $where .= " AND ";
    }   
}
$results ="SELECT * FROM games WHERE $where ";
echo $results;
$results_num = ($results = mysql_query($results)) ? mysql_num_rows($results) : 0;

if($results_num === 0){
    return false;
}else{
    while($row = mysql_fetch_assoc($results)){
        $returned_results[] = array(
            'game_id' => $row['game_id'],
            'title' => $row['title'],
            'platform' => $row['platform'],
            'rating' => $row['rating'],
            'image_location' => $row['image_location'],
            'description' => $row['description'],
        );
    }
    return $returned_results;
}   
}

?>

So in case its not clear i want to allow the user to search for a key term, then it will display from the database any games with the keyword in the title(i already have this part working). Now what cant figure it out is how to let the user filter their results to lets say only action games, along with lets say a certain platform and so on.

Like i said ive only been doing this for a month so if im doing something wrong dont hesitate to call me an idiot, but please help. Any help would be appreciated even links to tutorials or book recommendations.Thanks.

Solution

  • Your approach is proper. To filter the search results on the basis of publisher name or developer, genre, release date,

    1) You can have another "Advanced Search" Page wherein you could plugin the HTML for these attributes, and just tweak the WHERE clause of your query. So there will be two pages for search - a simple search that will allow the user to filter on the basis of just the title name and say the category and an advanced search page to filter on the basis of the other attributes.
    2) You may even choose to provide options on the basic search page itself for filtering on the basis of additional parameters.

    How you choose your interface would be a decision mandated by your requirement specification. Note that only your WHERE clause will change in all cases.