Searching a table MySQL & PHP.

Question

I want to be able to search through a MySQL table using values from a search string, from the url and display the results as an XML output.

I think I have got the formatting and declaring the variables from the search string down.

The issue I have is searching the entire table, I've looked over SO for previous answers, and they all seem to have to declare each column in the table to search through.

So for example my database layout is as follows:

**filesindex**
-filename
-creation
-length
-wall
-playlocation

First of all would the following be appropriate:

$query = "SELECT * FROM filesindex WHERE filename LIKE '".$searchterm."%'
          UNION
          SELECT * FROM filesindex WHERE creation LIKE '".$searchterm."%'
          UNION
          SELECT * FROM filesindex WHERE length LIKE '".$searchterm."%'
          UNION
          SELECT * FROM filesindex WHERE wall LIKE '".$searchterm."%'
          UNION
          SELECT * FROM filesindex WHERE location LIKE '".$searchterm."%'";

Or ideally, is there an easier way that involves less hardcoding to search a table.

Any ideas?

Thanks

Answer 1

LIKE is slow and inefficient. You may want to consider using full-text search if you are using the MyISAM storage engine.

• Using MySQL Full-text Searching
• Getting Started With MySQL's Full-Text Search Capabilities

Answer 2

Don't use UNION, you can chain search terms using AND or OR. And yes, you need to hard code the fields if you want to keep your code otherwise simple.

$query = "SELECT * FROM filesindex WHERE filename LIKE '".$searchterm."%'
      OR creation LIKE '".$searchterm."%'
      OR length LIKE '".$searchterm."%'
      OR wall LIKE '".$searchterm."%'
      OR location LIKE '".$searchterm."%'";

Answer 3

The query can be rewritten like this

$query = "SELECT * FROM filesindex WHERE filename LIKE '".$searchterm."%'
          OR creation LIKE '".$searchterm."%'
          OR length LIKE '".$searchterm."%'
          OR wall LIKE '".$searchterm."%'
          OR location LIKE '".$searchterm."%'";

if you want to find all the records for which one of the columns contains the search term.

Answer 4

Instead of union, use OR:

$query   = "SELECT  *
            FROM    filesindex
            WHERE   filename LIKE '".$searchterm."%'
            OR      creation LIKE '".$searchterm."%'
            OR      length LIKE '".$searchterm."%'
            OR      wall LIKE '".$searchterm."%'
            OR      location LIKE '".$searchterm."%'";

But -- I'd recommend using a file-based search system like Zend Lucene instead of MySQL.