Geek Answers Handbook

MySQL - PHP search script

I am currently encoding my own CMS and I am at the point of search.php . I need to search in 4 tables that have only one column name in only one query, because my server is not the fastest.

There is my DB location:

cmw_admin (user 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 (article 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 a row in each column of name / title and in all columns of description / content . In clarity, I want to search all these tables for a single row in only one query. Is it possible? I know that using JOIN , LEFT JOIN , RIGHT JOIN and INNER JOIN can be simple, but I don’t know how to use them. Google is not very clear about this!

+7
source share
2 answers

The total cost of four direct queries is likely to be much less than the cost of JOIN plus the search for the same records / fields.

In any case, check the capabilities of the FULLTEXT index.

Anyway you can do it with

  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 provide you with a list of found entries along with a hint where the entry came from ("cmw_admin", "cmw_blog", ...) allowing you to choose how to submit records from various sources.

A 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]);

It takes an HTML template containing an HTML snippet like

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

and "populate" the field with the [date] entry of the current $ record. I believe this is a good way to keep HTML and code separate (YMMV). If you already have a template system, you can (and should!) Adapt this option instead.

+4
source

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

When searching, you should use the same pr query table, as you will most likely need to create html links / results that are different from which table the match was found in.

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

+1
source

More articles:


All Articles