Page 1 of 1

MySQL: Searching through tables

Posted: Mon Feb 16, 2004 9:05 am
by raymedia
Hi guys.. I am doing a website for a local community sports club. Im using PHP and MySQL do to this.

At the moment I have few tables where each tables representing a sports category. IE

1) Sports1
| MemberID | MemberName | DateJoined | Team | Position |

2) Sporrts2
| MemberID | MemberName | DateJoined | Trainer |

and about 20 more ... it has to be in this format because the data will be imported from an excel file. (each spreadsheet represent a table).

my question is, if i want to have a search function here which search through all the tables the following fields - MemberID, MemberName.

For an example if I enter John, I want it to search all the 20 tables for MemberName like John and return back memberID.

I tried
'select MemberID from Sports1, Sports2 where MemberName like "%John%" ' But its not right. Can anyone help me please

Posted: Mon Feb 16, 2004 1:29 pm
by McGruff
I think you should review your planned db structure. For example, it might be better to have a single members table and a categories table. You don't have to replicate the excel files: it would be possible to write translation scripts to send all the data to the appropriate db table.

A well-designed relational db will eliminate any search difficulties.

See this tutorial.