Searching 2 mysql tables simultaneously

[lukep11a]

Hi, I wonder if anyone can help me, I am building a search form on my site and it currently searches one table and displays the results from that row of the table, what I am trying to get it to do now is search two tables using one query so it will display the results pulled out from table one followed by the results from table two. I think I need to use the JOIN function but am having problems building the query. This is the original code that searches one table:

 

$query = "SELECT * FROM table1 WHERE field1 LIKE \"%$trimmed%\"";

 

This is how I have been trying to apply the code to search two tables with no luck so far:

 

$query = "SELECT * FROM table1, table2 WHERE table1.field1 LIKE \"%$trimmed%\" OR table2.field1  LIKE \"%$trimmed%\"";

 

Can anyone help me to apply the coding I need to search two tables simultaneously. Any help would be very much appreciated..

[AbraCadaver]

There are other ways besides this depending on how you want the results (not tested):

 

$query = "SELECT * FROM table1 WHERE field1 LIKE '%$trimmed%'
UNION SELECT * FROM table2 WHERE field1  LIKE '%$trimmed%'";

[lukep11a]

That code brings up no results and 'couldnt execute query'. The way I want the results displaying is two rows from each table, so sayfor example the first table has names and ages and the second table has names and gender and someone searches for john. The result would be:

 

john, 18

 

john, male

[Mr Hyde]

It sounds like you need something like this:

SELECT *
FROM table1 as t1
LEFT JOIN table2 as t2 
ON t1.field1 = t2.field1
WHERE t1.field1 LIKE \"%$trimmed%\";

 

This joins the two columns where the names are the same. It should return results like:

John, 18, male

 

all on one row. Also, because we are joining the two tables on field1, where they are equal, there is no use searching both columns... only searching one would be sufficient.

[lukep11a]

Thankyou for your response, the thing is that I was hoping to get the results to appear on separate lines rather than on the same line, is this possible?

[Mr Hyde]

Thankyou for your response, the thing is that I was hoping to get the results to appear on separate lines rather than on the same line, is this possible?

 

You mean you want two ROWS right... not two lines?

 

You'll have to do something like AbraCadaver suggested using a UNION, or you could just concatenate two sql strings togther and use mysqli_multi_query() (which would be REALLY close to simultaneous).

 

If you don't mind me asking, why do you want two rows returned if you can have them both returned in one?

[lukep11a]

Say for example I had two tables, one for football players and one for staff, and then a user searches for 'Man Utd', I would want all the players for Man Utd with info about that player to be displayed one after the other followed by all the staff that worked for Man Utd with their info displayed. Does that make it any clearer?

[AbraCadaver]

There are other ways besides this depending on how you want the results (not tested):

 

$query = "SELECT * FROM table1 WHERE field1 LIKE '%$trimmed%'
UNION SELECT * FROM table2 WHERE field1  LIKE '%$trimmed%'";

 

This will do exactly what you want if the tables have the same number of columns.  If not then you need to specify the columns in each select and make sure you specify the same number of columns for each select:

 

SELECT field1, field2, field5 FROM table1 WHERE field8 LIKE '%$trimmed%'
UNION
SELECT field3, field4, field6 FROM table2 WHERE field2  LIKE '%$trimmed%'

 

If either doesn't work then you are doing something wrong.

[Mr Hyde]

Say for example I had two tables, one for football players and one for staff, and then a user searches for 'Man Utd', I would want all the players for Man Utd with info about that player to be displayed one after the other followed by all the staff that worked for Man Utd with their info displayed. Does that make it any clearer?

 

Ah, okay... I assume the table with staff has different columns then the one for the players, otherwise you would probably just combine the two tables and add another column for group ("staff" or "player") then order your results by group.