MySQL/PHP: How to insert logged in user id into another table that is gathering data from a form that the user is inputting.

Question

For the first time I am needing to join information from two tables and am quite nervous about doing it without any advice first.

Basically, I am building a secure site that is accessed by authorised users. I have my login table with user_id, username, password

Once the user is on the site, they have the option of inputting data into another table called input. At the moment this table only captures the information that is entered, not the user_id or username of the inputter.

I would like the form to be able to input the user_id and/or username from the login table into the input table.

Please could somebody talk me through this process?

I am sure that once this is amended, I will then be able to use the table to only allow the logged in user to access the information that he or she have inputted, is that correct?

Many thanks

Answer 1

First of all, don't put the user_id or username into the form. Anyone can change a form (yes, even hidden fields) so they could change the username or userID. Use a SESSION variable to store the username and/or userID.

How did you design your tables? With two tables you can do the trick:

logins ( USERID , USERNAME, PASSWORD ) inputs ( USERID, COMMENTS)

On the page processing the login form, after the login is succesful, you put the UserID in the SESSION. Then on your page processing the input form, you just do an INSERT in your inputs with the UserID from the SESSION.

Then later on the page showing the inputs, you can SELECT l.USERNAME, i.COMMENTS WHERE l.USERID = i.USERID etc etc

Answer 2

As Konerak says, you cannot relay on user-submitted data. You've previously authenticated this user there you did at that time know the username and the fact that it was valid - in most applications a good developer would store these facts - because you'll need them to perform any subsequent authorization.

So you just do something like:

 INSERT INTO input (comment, date, user)
 VALUES ('" . mysql_real_escape_string($_POST['comment'] . "',
 NOW(), '" . mysql_real_escape_string($_SESSION['authenticated_user']) . "');

(NB if you are using mod_auth_mysql or similar, where the authentication is hadneld by the webserver, then the authenticated username is contained in $_SERVER['PHP_AUTH_USER']).

C.