Jump to content

What are the best practices for search?


Fluoresce

Recommended Posts

I want to add a search feature to my site so that users can search for videos.

Let's say that a user conducts a search and I GET their search query:

$squery = isset($_GET['query']) ? $_GET['query'] : '';

Now what should I do?

 

What are the best practices to ensure the security of my database and to provide the most relevant results to the user?

 

Here's what I've got so far.

// Make sure a search query was entered.
if($squery == '') {
    echo "<p>You didn't enter a search query.</p>";
}
    
// Strip HTML tags.
$squery = strip_tags($squery);

// Trim white space.
$squery = trim($squery);
    
// Set minimum query length.
$min_length = 3;
   
// Make sure query length is more than minimum.    
if(strlen($squery) < $min_length) {
    echo "<p>The search query you entered is too short. The minimum number of characters is ".$min_length.".</p>";
}

// Connect to MySQL.
// Select database.

// Escape search query.
$squery = mysql_real_escape_string($squery);

// Break query into keywords.            
$keywords = explode(' ', $squery);

// Count number of keywords.
$no_of_keywords = count($keywords);

// If just one keyword, then build statement.
if($no_of_keywords == 1) {
    $sql = "SELECT whatever
    FROM `video_table`
    WHERE (col1 LIKE '%.$squery.%' OR col2 LIKE '%.$squery.%')";            
}

// If multiple keywords, then build statement.
else {
    $sql = "SELECT whatever
    FROM `video_table`
    WHERE ";
                        
    for($i = 0; $i < $no_of_keywords; $i++) {
        $sql .= "(col1 LIKE '%.$keywords[$i].%' OR col2 LIKE '%.$keywords[$i].%')";
        if($i < $no_of_keywords) {
            $sql .= " OR ";
        }
    }
}

// Run mysql query.
$raw_results = mysql_query($sql, $con);

// Put results into an array for later use.        
$results = mysql_fetch_array($raw_results);

Can this code's security be improved?

 

How can it be altered to provide more relevant results?

 

Should I omit words such as "to" and "the" from the query? If so, how do I do it?

 

Should I remove punctuation?

 

As always, I appreciate your help. You guys have taught me LOADS! :happy-04:

Edited by Fluoresce
Link to comment
Share on other sites

First off, you should stop using the mysql_ extension - it is deprecated. Use mysqli_ or, better yet, PDO. Then, when you do convert, use Prepared Statements for anything that will include variable data that could be a security risk (such as $_GET values). This is the best way to ensure user data will not create SQL Injection problems.

 

Now, as to the best ways of searching, that is not an easy answer as it all depends on the context of what the user is searching for. Sometimes, you have to make an educated guess on the best business rules and try it out for a while and get some feedback. You can also add some logging of searches done to see any patters. Are users performing multiple searches before selecting a record? What did they search for and what did they ultimately select? How could you change the search logic so that what they ultimately selected would have been in the results of the first search?

 

EDIT: If you are not going to use Full Text searching then you should at least explode the search words and create a multi-conditional where clause. What you have now requires the user to enter the search words in the exact order that they would appear in the fields being searched. E.g.:

 

SELECT whatever
FROM `video_table`
WHERE
    (col1 LIKE '%.$word1.%' AND
     col1 LIKE '%.$word2.%' AND
     col1 LIKE '%.$word3.%')
    OR
    (col2 LIKE '%.$word1.%' AND
     col2 LIKE '%.$word2.%' AND
     col2 LIKE '%.$word3.%')

 

But, yeah, you really need to use Full Text

Edited by Psycho
  • Like 1
Link to comment
Share on other sites

First off, you should stop using the mysql_ extension - it is deprecated. Use mysqli_ or, better yet, PDO.

 

Yes, I'm a bit behind.

 

 

Then, when you do convert, use Prepared Statements for anything that will include variable data that could be a security risk (such as $_GET values). This is the best way to ensure user data will not create SQL Injection problems.

 

Thanks! I shall look into this. I know nothing of prepared statements.

 

 

Now, as to the best ways of searching, that is not an easy answer as it all depends on the context of what the user is searching for. Sometimes, you have to make an educated guess on the best business rules and try it out for a while and get some feedback. You can also add some logging of searches done to see any patters. Are users performing multiple searches before selecting a record? What did they search for and what did they ultimately select? How could you change the search logic so that what they ultimately selected would have been in the results of the first search?

 

Thanks, but I don't need anything that complex. I just need something rudimentary that provides relatively relevant results.

 

 

EDIT: If you are not going to use Full Text searching then you should at least explode the search words and create a multi-conditional where clause.

 

That's what I thought my code above does!

 

It's supposed to generate a search clause like this:

SELECT whatever
FROM `video_table`
WHERE
    (col1 LIKE '%.$word1.%' OR col2 LIKE '%.$word1.%') OR
    (col1 LIKE '%.$word2.%' OR col2 LIKE '%.$word2.%') OR
    (col1 LIKE '%.$word3.%' OR col2 LIKE '%.$word3.%')
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.