MySQL - Search PHP script

Question

I'm currently coding my own CMS and I'm at the point of the search.php. I need to search in 4 tables that haves different columns name within only one query because my server is not the fastest.

There's my DB arrangement:

cmw_admin (users table)

+-----------------+------------+------+-----+---------+----------------+
| Field           | Type       | Null | Key | Default | Extra          |
+-----------------+------------+------+-----+---------+----------------+
| id              | int(11)    | NO   | PRI | NULL    | auto_increment |
| role            | int(11)    | NO   |     | 1       |                |
| username        | text       | NO   |     | NULL    |                |
| password        | text       | NO   |     | NULL    |                |
| email           | text       | NO   |     | NULL    |                |
| phone           | text       | NO   |     | NULL    |                |
| location        | text       | NO   |     | NULL    |                |
| full_name       | text       | NO   |     | NULL    |                |
| bio             | text       | NO   |     | NULL    |                |
| website         | text       | NO   |     | NULL    |                |
| last_login      | datetime   | NO   |     | NULL    |                |
| registered_date | datetime   | NO   |     | NULL    |                |
| registered_ip   | text       | NO   |     | NULL    |                |
| credits         | tinyint(1) | NO   |     | NULL    |                |
| online          | int(11)    | NO   |     | 0       |                |
+-----------------+------------+------+-----+---------+----------------+

cmw_blog (articles table)

+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| title      | text     | NO   |     | NULL    |                |
| content    | text     | NO   |     | NULL    |                |
| date       | datetime | NO   |     | NULL    |                |
| author     | int(11)  | NO   |     | NULL    |                |
| categories | text     | NO   |     | NULL    |                |
| media      | text     | NO   |     | NULL    |                |
| thumb      | text     | NO   |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+

cmw_projects (portfolio)

+-------------+------------+------+-----+---------+----------------+
| Field       | Type       | Null | Key | Default | Extra          |
+-------------+------------+------+-----+---------+----------------+
| id          | int(11)    | NO   | PRI | NULL    | auto_increment |
| owner_id    | int(11)    | NO   |     | NULL    |                |
| title       | text       | NO   |     | NULL    |                |
| content     | text       | NO   |     | NULL    |                |
| date        | date       | NO   |     | NULL    |                |
| link        | text       | NO   |     | NULL    |                |
| img         | text       | NO   |     | NULL    |                |
| type        | int(11)    | NO   |     | NULL    |                |
| start_date  | date       | NO   |     | NULL    |                |
| end_date    | date       | NO   |     | NULL    |                |
| done        | tinyint(1) | NO   |     | 0       |                |
| screenshots | text       | NO   |     | NULL    |                |
+-------------+------------+------+-----+---------+----------------+

cmw_services (services table)

+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| id          | int(11)       | NO   | PRI | NULL    | auto_increment |
| cat_id      | int(11)       | NO   |     | NULL    |                |
| name        | text          | NO   |     | NULL    |                |
| description | text          | NO   |     | NULL    |                |
| img         | text          | NO   |     | NULL    |                |
| price       | decimal(11,2) | NO   |     | NULL    |                |
+-------------+---------------+------+-----+---------+----------------+

In my search, I need to search for string in every name/title columns and in all description/content columns too. In clear, I want to search all those table for one string, in only one query. Is that possible? I know that using JOIN, LEFT JOIN, RIGHT JOIN and INNER JOIN may be easy but I don't know how to use them. Google is not very explicit on that things!

Answer 1

The total cost of four straight queries is in all likelihood much less than the cost of a JOIN plus a search of those same records/fields.

If anything, check the possibilities of a FULLTEXT index.

In any case, you can do this with something like

 SELECT "cmw_admin" as source, id FROM cmw_admin, NULL as date [...] WHERE bio LIKE '%search%'
 UNION
 SELECT "cmw_blog"  as source, id FROM cmw_admin, date as date [...] WHERE
     (title LIKE '%search%' OR content LIKE '%search%')
 UNION
 ...

which will give you a list of the found records, together with a hint of where the record came from ("cmw_admin", "cmw_blog", ...); that will allow you to choose how to present the records from the various sources.

The query will always return the same set of fields, some with valid values, some not, depending on what the "source" is. Then in your PHP script you can do something like,

 $source = $record['source'];
 if (!isset($template[$source]))
     $template[$source] = file_get_contents("templates/search/$source.html");

 $html_view = preg_replace('#{{\s*([^}\s]*)\s*}}#e', 'isset(\$record["\1"])?\$vars["\1"]:""', $template[$source]);

This will take a HTML template containing a HTML fragment like

 <h2>{{ date }}</h2>

and "populate" the field with the [date] entry of the current $record. I find this is a good way to keep HTML and code separated (YMMV). If you already have a templating system, you can (and should!) adapt that one instead.

Answer 2

If you want to search in mysql only, the FULLTEXT index is the way to go.

When searching you should use a single query pr table since you most likely need to build links/result html that is different based on what table the match was found in.

But you should consider looking into a real search engine like Apache Lucene. Zend has a very good implementation for it that does not depend on any external services http://framework.zend.com/manual/en/zend.search.lucene.html/ it will help rank the results better than you can do with mysql and it should scale better.