Recordset and JOIN mysql problem

Addy_31

okay.. im new to this, and need some help, as i have spent all bank holiday trying to sort this.

I have 1 database, with 2 tables.

table 1 = Info & table 2 = User

Both tables have a common primery colume called Member which is the number of the record. so:
User.member '2' and Info.member '2' holds info for that member.

I am tring to create a account page, and need a recordset that if User.member and Info.member equal the same, then filter all the Info information ont that member number. But every time i try, it just shows the first record.....

I hope i have explained this right... please help me if you can.

Thanks Addy

Shrike

I think you might be taking the wrong approach to relationships. The basic idea is that a table can have a primary key (which is unique for that column) and a number of foreign keys (which refer to a primary key in another table). In your case you would have something like this:

//Table User
id_user (int) // primary key
id_member (int) // foreign key

// Table Member
id_member (int) // primary key

In MySQL the primary keys can be set to auto increment, giving the unique value.

Doing things this way the data can be accessed using simple queries:

SELECT t1.*, t2.* // You should really list all columns
FROM User as t1, Member as t2
WHERE t1.id_member = t2.id_member
AND t1.id_user = $id_user // where $id_user is supplied

When inserting data into the database, you should insert the 'outer' tables first (in this case Member) and obtain the primary key for that table (with MySQL you can use the mysql_insert_id() function), and then put the now foreign keys into the inner table (in this case User).

Hope this is of some help.

Addy_31

thank you... I will try it... thou i am new so dont really unstand all the lingo tbh.

I have a primery key in both user.table and info.table but dont know how to make anything foreigh ?

//Table User
id_user (int) // primary key
id_member (int) // foreign key

// Table Member
id_member (int) // primary key

so in the User.table, i make the username prim and not member right ???

Shrike

The terms primary/foreign are just used to describe what the columns do - they aren't actual features of the database.

In most cases every table has a primary key. This is the key used to refer to each row in the table. In order to create a relationship between two tables you insert the primary key of one table into a column of another. In doing this you create what's known as a foreign key in the second table.

Perhaps showing how you would insert data into MySQL might clear this up

$q = "
  INSERT INTO Member
  VALUES (
    null,
    'Some data'
  )
";
mysql_query( $q );
$member_primary_key = mysql_insert_id();
$q = "
  INSERT INTO User
  VALUES (
    null,
    '" . $member_primary_key . "',
    'Some more data'
  )
";
mysql_query( $q );

Here we first insert a record into the Member table. The first value is null because we've specified that column as the primary key, and it will auto increment. The second value is just some data.

Next we get the primary key value of the Member insert using mysql_insert_id()

Then we insert a record into the User table. Again the first value is null as it's the primary key. The second value is the one obtained by mysql_insert_id(). This is the foreign key as far as table User is concerned, Then we add some data.

Edit: I was somewhat wrong as primary keys are most definitely a database feature. Foreign keys refer to primary keys in other tables and are an abstract.