up vote 1 down vote favorite

I was just wondering if there is the best way of doing this query?

$searchTerm = explode(' ', $search);
$wall_sql = "SELECT filename, category, name, downloads FROM wallpapers WHERE (pending='0' AND mediaType='0') AND ( ";
for($i=0;$i<count($searchTerm);$i++){
    if($i != 0){
        $wall_sql .= " OR ";    
    }
    $wall_sql .= " (category LIKE '%".$searchTerm[$i]."%') OR (filename LIKE '%".$searchTerm[$i]."%') OR (tags LIKE '%".$searchTerm[$i]."%') OR (name LIKE '%".$searchTerm[$i]."%') ";        
}
$wall_sql .= " ) ORDER BY ordernum DESC ";

What this script does it it takes in a search term $search and splits it into an array using the explode function in PHP. I then loop on that array to build up the query. This is the only way I can think to do it, I'm just not sure it's the best way of doing it. Also would any indexes work on this query if so what would they be?

share|improve this question
1  
FYI: your code has the potential for SQL injection. – Daniel A. White Mar 13 '11 at 19:54
feedback

4 Answers

up vote 1 down vote

The way you describe your task you're doing it all right. Indexes are out of the window though if you use LIKE '%search_term%'.

Could refactor the code a bit for readability:

$searchTerm = explode(' ', $search);
foreach ($searchTerm as $s){
    $f[] = "category LIKE '%".$s."%'";
    $f[] = "filename LIKE '%".$s."%'";
    $f[] = "tags LIKE '%".$s."%'";
    $f[] = "name LIKE '%".$s."%'"; 
} 
$wall_sql = " 
SELECT filename, category, name, downloads   
  FROM wallpapers   
  WHERE pending='0' AND mediaType='0'
    AND (".implode(" OR ",$f).")   
ORDER BY ordernum DESC";
share|improve this answer
feedback
up vote 0 down vote

I would create several virtual tables based on the search condition, on join against the tables columns.

Something like

SELECT w.filename, 
       w.category, 
       w.name, 
       w.downloads 
FROM   wallpapers w 
       LEFT JOIN (SELECT 'term1' AS name 
             UNION 
             SELECT 'term2' AS name 
             UNION 
             SELECT 'term3' AS name)  cat 
         ON cat.name = w.category 
       LEFT JOIN (SELECT 'filename1' AS name 
             UNION 
             SELECT 'filename2' AS name 
             UNION 
             SELECT 'filename3' AS name)  f 
         ON f.name = w.filename 
WHERE  w.pending = '0' 
         AND w.mediatype = '0' 
         AND (cat.name IS NOT NULL OR f.name IS NOT NULL)
GROUP by w.id
ORDER  BY w.ordernum DESC

In this case you would dynamically construct the virtual table values as static hardcoded values (term1,2,3 and filename1,2,3), and you need to add indexes on the following columns

  1. (pending,mediatype)
  2. (ordernum)
  3. (category)
  4. (filename)
  5. assuming you have primary key id
share|improve this answer
feedback
up vote 0 down vote

The query you give is a normal search query, which may do the job well. If you want to improve performance, you can add a fulltext index and use fulltext search or use a tool like Sphinx. Sphinx indexes your database so that fulltext searches become very fast. However, you need to install and maintain it.

share|improve this answer
feedback
up vote 1 down vote

Your query will not be able to use any indexes and using LIKE can give false matches (for example, a search for x LIKE '%red%' will match hired).

If you haven't already considered it, you might want to look at a full text search as this type of search can use a FULLTEXT index.

share|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.