MySQL: Searching through tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
raymedia
Forum Commoner
Posts: 27
Joined: Thu Oct 09, 2003 6:36 am
Location: Melbourne, Australia

MySQL: Searching through tables

Post 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
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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.
Post Reply