Search every table in a database at once

[bddotnet]

I am now in charge of maintaining www.okfqhr.com. Is it possible to search EVERY table in a MySQL database?

(i.e. If I want to display all the classes that someone has entered in and the results of each class?)

Or, do I have to do a search for each individual table? :(

[Neat Pete]

I guess anything is possible - you could use PHP to build each query and run them in turn.

But I shudder; this next bit needs a tutorial of about 80 pages. Your question implies there is no underlying database design for a relational database, no data modelling, no normalisation, no implementation plan.

Perhaps someone else can suggest good tutorials or FAQ on data modelling. Most books on PHP with chapters on database usage are pretty vile.

SQL was "invented" in 1976 as the query language for relational databases. It sits very firmly on top of set theory mathematics. There does not appear to be much to it, very few statements, and yet there is a lot to be learned and a lot of rules to be obeyed.

Back in the real world, I looked at your entry form a couple of weeks ago. It's complicated, and your "business rules" are not immediately clear to an outsider. It appears that each horse can enter a lot of classes and each class can have a lot of horses entered into it, and some of these horses win prizes or are noted in the official results. Then, this entry thing is repeated year after year, and possibly at different places and/or at different times of the year. Is this the way you see the relationship between horses and classes??

The entry form has a lot of rules on it (must-must-must) so people are only persisting with filling it in because they love their chosen sport. My first thought would be to separate the existing entry form into "rules" and "entry details" which would might evolve into separate documents. During the exercise of doing this you would learn a great deal about your business. This is what a database consultant starting from scratch would first do, plus writing out a one-line definition of every data field on the entry form.

Yes, I know this is not the PHP fix-it-by-programming answer that you wanted to hear.

[bddotnet]

Neat Pete -

Thanks for the very in depth answer. I inherited this website from a lady who is a member of this organization. She is VERY computer literate and has setup the database tables so that they are relational.

If you check http://www.okfqhr.com/ssa/services_sold.php and click on a year, you will see that there is a list of stallions on each page. Each of these stallions are up "for sale" as far as their breeding goes. The owner of the mare pays the indicated stud fee and breeds her mare to that stallion.

The data you see there on those pages for each stallion is relational. The stallion info is in one table, the stallion owner in another, the donation record in another, the pedigree in another. They are all linked by the owner ID assigned to each stallion owner. Evidence of this is best seen in http://www.okfqhr.com/ssa/stallions08.php where there are several stallions owned by the same couple.

I'm not looking for a quick fix, but being able to search an entire database (minus the tables created by phpBB) would be nice so that a person could see everything they've ever entered and how they've progressed in each class over time.

Thanks again! You are a great help and an asset to this forum!:cool:

[Neat Pete]

Good to hear it's relational by design. Keep at it. Try to draw a data model diagram as per this sample...http://www.kittyandthealleycats.com/contacts/contacts.jpg

OK, there are two alternatives..

(a) Design a web page with several sections in the display, each section is from a different table. Use subheadings to tell the user what they are looking at. No display of these headings etc if there are no relevant rows of data.

(b) Use the UNION construction to combine the results of queries from different tables into one big result. Sometimes this gets a bit tricky and it's then better to insert the rows into an intermediate table to hold all the results. Then you can display it easily in any order.

[bddotnet]

Yeah, I like both ideas, Neat Pete! I'm using the first idea now. Thanks!