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 !

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

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");

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']."');
}
commented: This is no use for what the OP is trying to do -2

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.

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

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

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");
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.