I have a website that interacts with a mysql database via php. The database has 3 tables. 'user', 'goals', 'steps'
For each record in user there may be many in goals and for each record in goals there may be many in steps.
On the website I have the user log in. They can decide then that they would like to set some goals. So they are directed to the goals form and their information is entered into the goals form and it is linked through their userid which is in both the goals table and the user table. userid is carried as a variable in a session when they log in. My challenge is after they submit the goals form they are then redirected to a steps form where they are supposed to fill out steps towards achieving the goal that they just submitted in the goal form. I need to link the steps to that specific goal (remember there may be many goals by each user and I need to link it to the one they just entered). I wasn't sure exactly what the best method to use would be.
The only thought that I had was to create a randomly generated number using php on the goals form and insert it into the goals table and then carry that variable over to the step form upon completing the goal form. That would then be the link between the step form and the goal form. If so exactly how would you create the random variable & how would you carry it over to the steps form?
Working on 2 different tables
Moderator: General Moderators
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Working on 2 different tables
I think you should link them using the same kind of relation that links goals to users:
User
id
Goal
id
user_id
Step
id
goal_id
User
id
Goal
id
user_id
Step
id
goal_id
(#10850)
-
crazycoders
- Forum Contributor
- Posts: 260
- Joined: Tue Oct 28, 2008 7:48 am
- Location: Montreal, Qc, Canada
Re: Working on 2 different tables
Ok, so you have two questions in there, how to structure your database and how to carry on the information that you want your user to be able to edit.
#1 Database design
Like arboprint stated, linking your tables is the first step. You clearly stated that you want users => goals => steps. Steps are only related to the users via the goals that these users save. The table structure arboprint gave is ok, but i have my own i'd suggest which is easier when it comes to joining tables together in SQL
users
- user_id (AutoIncrement, PrimaryKey, Integer, Unsigned)
goals
- goal_id (AutoIncrement, PrimaryKey, Integer, Unsigned)
- user_id (Integer)
steps
- step_id (AutoIncrement, PrimaryKey, Integer, Unsigned)
- goal_id (Integer)
The reason to use the same names from table to table is to be able to do #1 in a SQL string instead of #2 which can be long when you have long table names:
Ex #1:
Ex #2:
#2 Transfering the info
Next when you wish to transfer information to another page, you usually use the HEADER('LOCATION:') command. Instead of generating the number yourself to identify the goal, use an autoincrement and the use:
Or use the equivalent if using MySQLi...
Then, simply call a header('location: steps.php?goalid='.$goalid); and you will transfer to the next page. Don't forget to always validate the goalid pertains the to user that is logged on because this can be easily altered in the url by the user and they could start manipulating other users' goals and steps.
I think that sums it up good...
#1 Database design
Like arboprint stated, linking your tables is the first step. You clearly stated that you want users => goals => steps. Steps are only related to the users via the goals that these users save. The table structure arboprint gave is ok, but i have my own i'd suggest which is easier when it comes to joining tables together in SQL
users
- user_id (AutoIncrement, PrimaryKey, Integer, Unsigned)
goals
- goal_id (AutoIncrement, PrimaryKey, Integer, Unsigned)
- user_id (Integer)
steps
- step_id (AutoIncrement, PrimaryKey, Integer, Unsigned)
- goal_id (Integer)
The reason to use the same names from table to table is to be able to do #1 in a SQL string instead of #2 which can be long when you have long table names:
Ex #1:
Code: Select all
SELECT * FROM steps LEFT JOIN goals USING(goal_id) LEFT JOIN users USING(user_id)Code: Select all
SELECT * FROM steps LEFT JOIN goals ON goals.id = steps.goal_id LEFT JOIN users ON users.id = goals.user_idNext when you wish to transfer information to another page, you usually use the HEADER('LOCATION:') command. Instead of generating the number yourself to identify the goal, use an autoincrement and the use:
Code: Select all
$goalid = mysql_insert_id();Then, simply call a header('location: steps.php?goalid='.$goalid); and you will transfer to the next page. Don't forget to always validate the goalid pertains the to user that is logged on because this can be easily altered in the url by the user and they could start manipulating other users' goals and steps.
I think that sums it up good...
Re: Working on 2 different tables
Thank you very much. Very, very helpful!
I really appreciate your time and effort.
I really appreciate your time and effort.