Page 1 of 1

Working on 2 different tables

Posted: Thu May 14, 2009 9:01 am
by fipp
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?

Re: Working on 2 different tables

Posted: Thu May 14, 2009 12:36 pm
by Christopher
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

Re: Working on 2 different tables

Posted: Thu May 14, 2009 1:00 pm
by crazycoders
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:

Code: Select all

SELECT * FROM steps LEFT JOIN goals USING(goal_id) LEFT JOIN users USING(user_id)
Ex #2:

Code: Select all

SELECT * FROM steps LEFT JOIN goals ON goals.id = steps.goal_id LEFT JOIN users ON users.id = goals.user_id
#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:

Code: Select all

$goalid = mysql_insert_id();
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...

Re: Working on 2 different tables

Posted: Thu May 14, 2009 2:46 pm
by fipp
Thank you very much. Very, very helpful!
I really appreciate your time and effort.