Search Engine in PHP

PHP
1

Hi Again!!
while coding for a search engine, i wrote a query like

$database=“activecertification”;
$table_name=“clientenquiry”;

$query=mysql_query(“SELECT * FROM $table_name WHERE name like '%”.$query.“%’ OR company like '%”.$query.“%’ OR address like '%”.$query.“%’ OR email like '%”.$query.“%’ OR phone like '%”.$query.“%’ OR budget like '%”.$query.“%’ OR startdate like '%”.$query.“%’ OR message like '%”.$query.“%'”);

but if i want to run the query for multiple tables and databases, is there any better alternative??

Please guide me.

2

Multiple tables: Yes
Multiple databases: No

Do query from multiple tables:


select * from table1 t1, table2 t2 where t1.name like $query OR t2.name like $query OR t1.company like $query......

If you want to query multiple databases, you need to make multiple queries.

Also, make sure all your columns are indexed, or the query will take forever to run.

3

Not necessarily. If the user you used to connect to the database also has access to another database you can run queries like:


SELECT
   database1.sometable.somefield
 , database2.anothertable.anotherfield
FROM
   database1.sometable
 , database2.anothertable
WHERE
   (etc,etc)
4

Why do you think that indexing all columns will make them run any faster?
This is such a bad advice, and creating a search engine like stated above is a terrible terrible solution. It will run crappy no matter what, plus it won’t even yield relevant results.

5

@ScallioXTX - Ah, didnt realise that was possible.

Wouldnt these types of queries be quite slow tho?

6

I don’t see why they would be. AFAIK they won’t be any slower than when the tables you’re querying had both been in the same database.

7

@Blue - If your columns are not indexed then mysql will start at the start of the table and search through every record to find a match, if you columns are indexed, it can jump to any point in the dataset. This will dramatically increase the speed of your queries. See http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

As far as this being a bad way to implement a search engine, we are in agreement.

@ScallioXTX - That makes sense. Have never used multi database queries before, its an interesting concept

8

I’m perfectly aware what indexes are and HOW they work. You on the other hand might want to read about inner workings of indexes and how they are used.
To cut the long story short - if the columns from query at the start of the topic would be indexed - it achieves ZERO in terms of speed.
The fact that MySQL would find at what entry the actual data-set is means nothing because index length would be equal to data length, meaning you achieved nothing, the actual time of search with or without index would be more or less the same.

On the other hand, you’d inflate your data storage by double the amount so yeah - indexing everything definitely isn’t the solution to this problem.