How to make search engine query in Mysql&PHP?

Question

Hello,

I want to make a search engine in an intranet. Now i use this clause in PHP.

$k = explode(" ",$_GET[key]);
$sql = "select entreprise.*, employee.* where entreprise.* or employee.* like  '%$k[0]%' or '%$k[1]%'";

But it seems doesn't work. Do you know where is wrong?

Thanks in advance.

Edit:

$sql = "select * from entreprise, site, salarie where entreprise.*, site.*, salarie.* like  '%$k[0]%' or '%$k[1]%'";

I have modified the query clause. With this code, i think you can know what i want to do.

I want to find anything that matches the content in all the columns of entreprise table and the content in all the columns of employee table.

Answer 1

This:

$sql = "select entreprise.*, employee.* where entreprise.* or employee.* like  '%$k[0]%' or '%$k[1]%'";

is not valid SQL. It is hard to guess what you want to do, but I'm trying anyway: you want to find employees, and search them by name or by enterprise that employs them. Is that the case? Or do you want to search employess and/or enterprises?

EDIT

I want to find anything that matches the content in all the columns of entreprise table and the content in all the columns of employee table.

Ok, first of all you should realize that SQL is probably not the best tool for this job. See the other commenter - his suggestions about sphinx and friends are good. But still, if you really want to:

$sql = '
    SELECT e.id, e.name
    FROM   enterprise e
    -- first, look in column1
    WHERE  e.column1 LIKE '."'%".$k[0]."%'".'
    OR     e.column1 LIKE '."'%".$k[1]."%'".'
    ...etc for all entries in k...
    OR     e.column1 LIKE '."'%".$k[N]."%'".' 
    -- then, look in column2
    OR     e.column2 LIKE '."'%".$k[0]."%'".'
    OR     e.column2 LIKE '."'%".$k[1]."%'".'
    ...and so on and so forth for all entries in $k and all columns in enterprise...
    UNION ALL
    SELECT s.id, s.name
    FROM   salarie s
    WHERE  ...and the same for columns of salarie...
    ...
    UNION ALL
    ...any other tables you want to search...
';

As you can see, not something that makes you happy.

Another approach that might give you more joy is having some overnight job to scan all rows in the tables you're interested in, parse the texts you want to search into separate words, and store those in a keyword table, and storing the association between an object from the source database and the keyword in a separate table. You can then search the keyword table and use the id's and table names you find for a collection of keywords to build the actual query to retrieve those rows. This is what I do, and it works great. It works better because there is a relatively small amount of words that you will encounter, whereas the collection of objects is quite possible very large.

Answer 2

It's hard to exactly see what you're trying to do, but you need, in your SQL query, to specify :

• on which tables you are working, with a from clause
• on which fields the search has to be done, in the where clause.
• how the data between employees and enterprises are related :
  • do you want to search for entreprises and their employees ?
  • for employees and there enterprises ?
  • for all enterprises and the employees when the employee or the enterprise contains the words ?

You could use something like this to search for entreprises that contain the word, and get their employees to :

select *
from entreprise
    inner join employee on employee.id_entreprise = entreprise.id
where entreprise.name like '%word%'
    or entreprise.description like '%word%';

Or, to search for employees that match the criteria and get their entreprise too :

select *
from employee
    inner join entreprise on entreprise.id = employee.id_entreprise
where employee.name like '%word%';

(just some ideas -- you'll have to build from there !)