PHP and MySQL question

Hello

I would appreciate some help here....

I have a MySQL database with the name "countries" which consists
of a number of tables where each table has the name of a country,
like "Belgium", "Germany", etc. Not all tables are completed, and I
will continue to add more tables. Each table contains 7 fields (we can
call the fields "field1" to "field7"). There's no need for a unique
key, and several of the fields contains rendundant data (as they have
been imported from a texfile, and the storage is not a problem).

From a HTML + PHP page I want to choose country from a pull down
menu like "select - option value" in HTML. Then when the country has
been choosen I want to search for a name in field1, after which all
matches should be printed out on the screen including the content of
field2 to field7 in a row.

I have gone through many examples of PHP and MySQL, but they are
either to basic and simple, or to advanced and complicated. And I believe
that what I want to do is possible to do in PHP, because I know that
I can do it with Perl and a text-based database, but now want to learn
to work with MySQL and PHP.

I believe that an example of what I want to do here should be
interesting for many others.

Thanks in advance for any suggestions and examples.

Carl S.

By making a table for every country, you're making it extra difficult. Is there no way to put all the countries in one table by using an extra 'country'-field.
An even better design will be to make a table which consists of two fields: one 'country'-field and one field that contains the name of the table belonging to that country. In this case you can query this table to create the pulldown menu in an easy why, and after the user selected the country of his choice, you can open the table with the information of that specific country.

Hi!

Thanks Joska for your reply

Yes, what you suggest can solve the problem,
but if we only look at the MySQL database
it's pre-built in a logical way:

- One Database
- One single table for each country data (this
also makes it easy to add more countries as
the database grows)
- Each table contains seven fields as follows:
- Location name
- Geographical longitude in printable format
- Geographical latitude in printable format
- Time equation (longitude measured in time)
- Geographical longitude in calculation format
- Geographical longitude in calculation format
- Location type (town, bay, dock, etc).

So if I add all tables together the database
will be one huge table, and all experience
says to me that it's easier and more efficient
to first identify one table out of many and
then search through it instead of searching
through one huge single table. Note: Each country
table is between 1 Mb to 10 Mb large!

So the problem as I see it, is with PHP and
HTML as such, and not the database.

I can accept the fact that the easiest way
to solve this is to to pre-define the table
names (country names) in the select pull-down
menu. However, the main problem is still there:
How to read-in the table names from the database,
list them in the select pull-down menu, let the
user choose one of the tables, then show an empty
field and have the user to write in a searchable
text (in this case a location name), then open
that table and search for any and all locations
found and list the other fields therein.

As I said in my earlier post, I know how to do
this with Perl/CGI and a textbased database, but
now want to learn how to do it with PHP and MySQL.

Any suggestions?


Per Dahlin