Combing User table with Form table

This site is best viewed in a modern browser with JavaScript enabled.

swalker-1974

I have setup a login system on my site, in which the logged in user (user table) would be able to fill out an insurance form that's saved in the database in a separate table - form_one table.

user table

username - varchar(30), Null: No, Default: None - Primary Key
password - varchar(32), Null: Yes, Default: NULL
userid - varchar(32), Null: Yes, Default: NULL
userlevel - tinyint(1), UNSIGNED, Null: No, Default: None
email - varchar(50), Null: Yes, Default: NULL
timestamp - int(11), UNSIGNED, Default: No, Default: None
valid - tinyint(1), UNSIGNED, Null: No, Default: 0
name - varchar(50), Null: Yes, Default: Null

form_one table

formid - varchar(32), Null: No, Default: None - Primary Key
username - varchar(30), Null: No, Default: None - Foreign Key
email - varchar(50), Null: Yes, Default: NULL
phone - varchar(12), Null: No, Default: None
address - varchar(50), Null: No, Default: None
timestamp - int(11), UNSIGNED, Null: No, Default: None
name - varchar(50), Null: Yes, Default: NULL
quotationinsurance - enum('insurance', 'quotation'), Null: No, Default: None
etc...

Below is my php function for UPDATE & SELECT

function updateUserForm($username, $field, $value){
/$q = "UPDATE ".TBL_FORM_ONE." SET ".$field." = '$value' WHERE username = '$username'";/ - Original code
$q = "UPDATE ".TBL_USERS.", ".TBL_FORM_ONE." SET ".$field." = '$value' WHERE username = '$username'";
return mysql_query($q, $this->connection);
}

function getUserForm($username){
/$q = "SELECT FROM ".TBL_FORM_ONE." WHERE username = '$username'";/ - Original code
$q = "SELECT FROM 'TBL_USERS', 'TBL_FORM_ONE' WHERE 'username' = '$username' AND TBL_USERS.username = 'TBL_FORM_ONE.username'";
$result = mysql_query($q, $this->connection);
/ Error occurred, return given name by default /
if(!$result || (mysql_numrows($result) < 1)){
return NULL;
}

How can I join these two tables where the username in both tables are tied together. In other words, once a user is logged in, they can fill out the form and the data from that form would be tied to each unique user.

Any help would be appreciated! :-)

bionoid

You could do it like this:

SELECT u.*, f.*
FROM TBL_USERS u
JOIN TBL_FORM_ONE f ON f.username = u.username
WHERE u.username = '$username'

If columns in both tables have the same name, the later will take precedence, unless you give them different aliases of course.

swalker-1974

Thanks! I'll give it a shot.

swalker-1974

Would the UPDATE function be done in a similar way?

bionoid

swalker-1974;1186176 wrote:
Would the UPDATE function be done in a similar way?

Although I don't personally do it this way, I believe it works the same:

UPDATE TBL_USERS u
JOIN TBL_FORM_ONE f ON f.username = u.username
SET u.email = 'test@test', f.email = 'test@test'
WHERE u.username = '$username'