Mysql multiple tables problem

[kvnirvana]

How can I write a mysql command when I want to get data from 2 tables. On one of my pages Iv'e got a search field where the user can write a word an then I want it to search at least 2 tables and return the rows where the word searched for appears? Tried to do it with union but the problem is that it only works if it is the same number of rows.

 

So I got this table called fag_muscles

id              Latin              Description

1                Piriformis        Muscle in your butt

 

 

And this table called  fag_bones

 

id              Latin              Description

1                Pelvis              Bla bla bla

2              Peronius            Bla bla bla

 

If the user searches for the letter 'P'

 

It should return

 

Piriformis

Pelvis

Peronius

[DavidAM]

UNION does not care about number of rows, but it DOES care about columns. All select statements in a union have to have the same number of COLUMNS and they have to be the SAME data-type (in the same order).

 

SELECT id, Latin FROM fag_muscles WHERE Latin LIKE 'P%'
UNION 
SELECT id, Latin FROM fag_bones WHERE Latin LIKE 'P%'

Should get you the data you are looking for (as long as the datatypes match up). Of course you will not know where each entry came from, so you could add a literal into the select:

 

SELECT id, Latin, 'Muscles' AS CameFrom FROM fag_muscles WHERE Latin LIKE 'P%'
UNION 
SELECT id, Latin, 'Bones' FROM fag_bones WHERE Latin LIKE 'P%'

 

That being said, I would suggest thinking about redesigning the database. It might be better with a single table and add in a column for Type (muscles or bones). Of course, that depends on how the rest of your database relates to these tables.

[kvnirvana]

Thank you :=)