join problem

[stu7398]

hi.
i've been up all night trying to figure out 'joins' forgive me if the code looks a little tatty. i've just got up.

I need a tip as to why when i do the join i'm not getting the individual member data, instead i get any data. I think it's the WHERE i'm having problems with.

here is an example of how i've set up 3 tables.

MEMBERS
id (primary)
login
password

PERSONAL
firstname (primary)
last_name
id (integer)
foriegn key (id) references MEMBERS(id)

CONTACT
phone (primary)
mobile
email
id (integer)
foriegn key (id) references MEMBERS(id)

$website = mysql_real_escape_string($_POST['website']);
$question = mysql_real_escape_string($_POST['question']);
$login = mysql_real_escape_string($_POST['login']);

mysql_query("UPDATE members, personal, contact, security SET firstname='$firstname', last_name='$last_name', address='$address', post_code='$post_code', location='$location', phone='$phone', mobile='$mobile', website='$website', email='$email' WHERE members.member_id = '$SESS_MEMBER_ID'")
or die("Update error: ".mysql_error());
}
list($member_id, $firstname, $last_name, $address, $post_code, $location, $phone, $mobile, $email, $website, $login, $question, $answer, ) = mysql_fetch_array(mysql_query("SELECT firstname,login, post_code,last_name,address,post_code,location, phone,mobile,email,website, question FROM members, personal, contact, security WHERE members.member_id = '$SESS_MEMBER_ID'"));

[laserlight]

Firstly, I think your multiple table update is wrong since you miss out the join conditions. It should be something along the lines of:

mysql_query("UPDATE members, personal, contact, security
    SET firstname='$firstname', last_name='$last_name', address='$address',
        post_code='$post_code', location='$location', phone='$phone',
        mobile='$mobile', website='$website', email='$email'
    WHERE members.id=personal.id AND members.id=contact.id
        AND members.id='$SESS_MEMBER_ID'")
    or die("Update error: ".mysql_error());

Then your select query is wrong for the same reason. It should be something along the lines of:

$result = mysql_query("SELECT firstname, login, post_code, last_name,
        address, location, phone, mobile, email, website, question
    FROM members, personal, contact, security
    WHERE members.id=personal.id AND members.id=contact.id
        AND members.id=security.id
        AND members.id='$SESS_MEMBER_ID'");
$row = mysql_fetch_assoc();
extract($row); // optional

where I assumed that security also has an id foreign key to members.

[stu7398]

thanks, i will have a go at your suggestion.