Search query in 2 tables

[JeanPhilippe]

Hi,

I'm making a small web-based software and I need help for a search query that looks for results in 2 tables.

I can run it very well like this for a single table :

$query=mysql_query("SELECT * FROM `customers` WHERE `name` LIKE \"%$search%\" ORDER BY `name` ASC");

But, here is the tricky part. I have 2 tables :

Table 1 (customers) :
client_id : 8
name : Johnny Depp
address : bla bla bla
etc...

Table 2 (animals) :
animal_id : 52
client_id : 8 (it refers to the client)
Name : Rex
etc....

So, Johnny Depp has a Dog named Rex. I have a search field, if I type "Johnny Depp", or "Depp", it will give me "Johnny Depp (client_id:8)" as the result, but I want to be also able to get the same result with the name of his dog. If I type "Rex", the result will be "Johnny Depp (client_id:8)", because in table 2, there is a dog named Rex with the client_id 8.

How can I do that? Thank you very much for your help !

[shahidpk]

try this

"select a.client_id,a.name,a.address,b.animal_id,b.name from customers a, animal b
where a.client_id=b.client_id and a.name like '% John%'

here we join two tables customers with name a and animal with name b
and search like from a table . But you can search from any field of any table but use refrence a, or b

Regards,
Shahid Qayyum
Pakistan

[simplypixie]

As @shahidpk has said, use a join but they have missed the search on the animlas table

$query=mysql_query("SELECT c.*, a.* FROM customers c INNER JOIN animals a WHERE c.name LIKE '".%$search%."' OR a.name LIKE '".%$search%."' ORDER BY c.name ASC");

[szabizs]

Joins are good but i'm trying to use separate selects, it is faster sometimes especially for big tables.

I'll show you the simple MySQL version.

$sql = mysql_query("SELECT * FROM table1 WHERE id = '3'");

if(mysql_num_rows($sql) > 0) {
    $arr = mysql_fetch_array($sql);

    // you also can use different loops

    $sql = mysql_query("SELECT * FROM table2 WHERE id = '".$arr['id']."');
}

[JeanPhilippe]

Thanks for all your responses. The code from @simplypixie works :) But I had to invert the table name. It works when I search for the animal name, it gives me the client, but when I search for the client, it gives me the same result multiple times (the number of animals he has)...How can we fix that?

Thanks.

[simplypixie]

Not sure what you mean by invert the table name?? What is your query now please.

[engrmshahzad]

$query=mysql_query("SELECT * FROM customer WHERE name LIKE '%.$customer_name.%' OR id=(select id from animals where name LIKE '%.$pet_name.%')");

[simplypixie]

OK, I think I understand a bit more now (plus I missed off the ON .... from my first query) - you want to return the name of the customer if the search matches their name or their animal's name. If I am correct then your query needs to be

$query=mysql_query("SELECT c.* FROM customers c INNER JOIN animals a ON c.id=a.id WHERE c.name LIKE '".%$search%."' OR a.name LIKE '".%$search%."' ORDER BY c.name ASC");