Is it possible to let user choose which table to display?

[wouterblacquiere]

Dear everyone,

 

I am reasonably new to php, so there is a chance that what I am asking for may exceed my grasp.

Nevertheless I'd like to try:

 

Is it possible to let the user choose (by using a drop down listbox for instance) which table from a database to display on a page?

And/or which database to display on a page?

 

Background information: I'm working on a website for my chess club. I've already got a working page (html,css) with a table of the standings (mysql, php) of the latest round (with position number, name, points, wins, losses, etc). By clicking on the column header, it sorts the respective column. What I'd finally like to do is let the user choose to display a different round (table), or even a different season (database). I could make a seperate page for each round, but I'd like to know if there's a more elegant way to do this by using only one page and one or more databases.

 

Thanks in advance for any tips and best wishes from The Netherlands,

 

Wouter.

[Brentatechnologies]

Hey There,

 

You may be able to use a ("SELECT * FROM) from database depending on what you are getting from the database

For instance

You have a Table for which player is vsing who, but the viewer wants to know what pieces the players vs'ing have taken

you'd be able to use the

$query = mysql_query("select * FROM "TABLE HERE" WHERE "PLAYER'S VSING HERE" = 'DISPLAY THE PLAYERS and what pieces are taken'"); // Queries the Database to check if the Players have taken a piece and if so what piece has been taken

 

Obviously you'd need to setup your database to have Players, Pieces and whatever else...

 

(I'm thinking you made your own chess game?)

if not let me know and i'll try help with whatever else

[Psycho]

The answer to your question is yes, you can allow the user to select different databases and tables. But, that is NOT what you should be doing. You should not create new tables or databases for different rounds or seasons. Your tables should be built based on the logical associations of data.

 

For example, lets say you need to store the results of a match and each match is associated with a tournament. Then you would want one table for the tournament info and another for the match info. In the tournament table you would have a record for each tournament and each record would have a unique "primary" key. Then in the matches table each match record would include a field to store the value of the tournament id (this would be referred to as as a foreign key reference because it is a key that refers to another table).

 

You can then store all your matches in the same table but each record can be associated with different tournaments. I suggest you do a little reading on designing a relational database before you try and build the wrong solution.

[wouterblacquiere]

Thank you both for thinking along with me!

 

I think I can safely conclude that I should rethink the design of my database.

 

Instead of one database with a long list of tables (each table representing one played round and consisting of 11 columns and 26 rows) I now have one database with only two tables: one table with only one column called round_id (primary key) and one table now consisting of 12 columns (nr, name, points, games played, wins, losses, etc. and an extra column called round_id which is indexed and foreign keyed and linked to the primary key of the first table) and many sets of 26 rows (the number of players each round).

 

Could you point me in the direction of where to go next?  How should I go about displaying portions of the second table (for instance only the 26 rows and 11 columns with the round_id of 10) and let the user choose which round to view (for instance through a list box)?

 

Please do feel free not to post perfect and long lines of code, as I am fully aware that understanding the concepts behind the code is much more valuable than copying and pasting a code you provide without me grasping why it works.

 

All the best for now.

[Psycho]

Well, you wouldn't have a table with only one field. You would want to provide *something* to identify the round - even if it is only the timestamp of when the round was created. As for your second table it would likely need to be separated out into other tables. You state the fields would be something like this:

nr, name, points, games played, wins, losses, etc. and the foreign key (FK) round_id

 

But, if you have people (i.e. the names) that would be associated with different rounds you would want a separate table to store the people and their name. Then use a foreign key (FK) to associates the rounds with the people. And, you would probably have separate tables for the rounds and the games. I am not going to create your DB structure for you, but I'll give an example. I would have to go through all the requirements of your application to really provide the correct approach and that's beyond the time I can invest. Again, you need to do some reading on teh subject of database normalization.

 

Anyway, here goes (PK = primary key, FK = foreign key):

 

Table: Users

Fields user_id (PK), name

 

Table: rounds

Fields: round_id (PK), round_description

 

Tables: Games

Fields: game_id (PK), round_id (FK), player_1 (FK), player_2 (FK), player_1_score, player_2_score, winner_id (FK)

 

I would actually consider breaking out the player details from the Games table.

[wouterblacquiere]

Thanks a lot for your help and for pointing me towards sound database design.  I shall have some reading to do from now on.

 

All the best,

 

Wouter.