Write information from many tables to page (Following tables dependent on first)

[wroberts]

I am trying to write some data from multiple SQL tables to a page. In the first table is a list of places. I then have more tables that are named after the different places. For example, say my first place in the list is called Place1. I have a table named Place1 with data that corresponds to place1. The data contained in the table named Place1 is a list of things to do in this place. It has 21 columns and each one is something to do in the morning, afternoon, and at night for each day of the week in the place Place1. What I am trying to do is display a sort of weekly calendar as a table on a webpage that lists all of the places in one column and then lists seven days of the week as 7 more columns. Then in each data cell I would want to list the things to do in the morning, afternoon and at night for the certain day of the week and for the place. The problem is that I am creating a CMS to allow other users with no coding knowledge to update events for other places, so I have to display data that could have been altered.

 

The only solution I know of is to do a while loop that gets all of the place names and while there are still place names, write the place names to the page and set a variable equal to the place name. Inside the while loop I would create another while loop that each time the first while loop is executed uses the variable set in the first while loop to know which table to reference and then make a call to that table pulling out the 21 columns and writing them to the page. Each time the outer while loop executes, it would (hopefully) write the place name, and then set the variable as the current place name so that the inner while loop uses the variable to write the rest of the information about that place.

 

I don't know if that would even work and if it did, I know it cannot be the best way to do this. I am pretty stuck here and don't really have a good solution so if anyone proposes a solution that is radically different to anything I have done, I am open to everything. Thank you!

 

[sunfighter]

There are a lot of way of approaching this. You should make up your mind on a course of action before starting to code. What follows are my thoughts.

 

First, forget the CMS and input from users until you have the page figured out. Add it last.

Are you going to limit the activities per division of the day or make the list infinite? Infinite would mean a number of tables in our database, while setting a limit, of say 5 activities would mean one table.

 

How are you going to display the PLACES to begin with? After the user picks a place it's easy to keep the place names in a vertical list or a couple of vertical lists.

 

If we restrict the activities to 5, we would need a table with a column for the place name followed by 21 times 5 [ 105 ] columns for the activities. Then we would query the table for everything in the place_name column and after the selection get everything WHERE place_name = "the selection".

 

No looping needed! Make your design decisions and code it. Then come back for user input help.

[jcbones]

Before you do anything, make sure your database follows the normal form. 

.  Once you have passed that, then you can plan how your script will act, and you should be able to pull everything you need with one query.

[Psycho]

The only solution I know of is to do a while loop that gets all of the place names and while there are still place names, write the place names to the page and set a variable equal to the place name. Inside the while loop I would create another while loop that each time the first while loop is executed uses the variable set in the first while loop to know which table to reference and then make a call to that table pulling out the 21 columns and writing them to the page. Each time the outer while loop executes, it would (hopefully) write the place name, and then set the variable as the current place name so that the inner while loop uses the variable to write the rest of the information about that place.

 

That is absolutely NOT how you should do this. You need to learn how to do JOINs in your queries. The real benefit of a relational database is to be able to pull related records in one call. I'll give a very generic example.

 

Let's say you have two tables: Authors and Book. The authors table has columns for the author_id (primary) and name. The Books table has two columns: book_id (primary), author_id (foreign key) and title. You can then run one query to get a list of the authors and their books

SELECT a.name, b.title
FROM authors AS a
JOIN books USING (author_id)
ORDER BY a.name, b.title

[wroberts]

Yes I knew my solution was not very good even if it even worked at all.

 

Sunfighter I agree that a finite amount of activities would be the best solution. I can definitely work with one table.

[Psycho]

Going with one table to store all this information is a bad idea. Making multiple columns in a table to store the same type of data will make the code much more difficult to create and the solution would not be scalable.

 

Although I'm not totally understanding your data structure, here is an idea:

 

Table: places

This table would have data such as : place_id, place_name, location, etc. Anything that is a one-to-one correspondence

 

Table: Activities

This table would store different types of activities that can be performed at the palaces. The activities are not specific to the places. Field would be something like: activity_id, activity_description, time_of_day (morning, afternoon, evening).

 

Table: place_activities

This table would be used to associate activities to the places. Each record would have two columns to associate each activity to each place. So, if place #1 is Orlando Florida and activity 15 is Disney World and activity 22 is Universal Studios you would have two records such as

place_id | activity_id
1          15
1          22

 

If you go with one table your queries are going to become very complex.

[mikosiko]

+1 with Psycho suggestion... I will only move the time_of_day field from the table Activities (making Activities totally generic) to the table places_activities

[Psycho]

+1 with Psycho suggestion... I will only move the time_of_day field from the table Activities (making Activities totally generic) to the table places_activities

 

Good point. But, without knowing more about the actual data and the context it was only a guess. The time periods for the activities might be dependent on the activities (e.g. dinner would always be a nighttime activity) or they may be dependent upon the location (e.g. scuba-diving may be offered as a daytime or nighttime activity based on location). You really have to do an analysis of your data and have a working knowledge on how data structures work. Else you are going to paint yourself into a corner.