Linking Tables?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
n00b
Forum Newbie
Posts: 7
Joined: Tue Dec 24, 2002 1:31 am

Linking Tables?

Post by n00b »

Hi i have 2 tables.

privms
privmstext

Inside privms is

id
usr_from
usr_to
date
subject

and privmstext has

text_id
text

When a member writes a message, the text gets put into privmstext, and the other details put into privms

How do i link them so privms and privmstext are displayed so the intended user can get his/her message?

I read the post about evilcoders ism, but i cant wait for that.
User avatar
mydimension
Moderator
Posts: 531
Joined: Tue Apr 23, 2002 6:00 pm
Location: Lowell, MA USA
Contact:

Post by mydimension »

use a simple table join like so:

Code: Select all

SELECT m.id, m.usr_from, m.usr_to, m.date, m.subject, t.text FROM privms AS m, privmstext AS t WHERE m.id=t.text_id
this stems of the first example here: http://www.mysql.com/doc/en/JOIN.html
n00b
Forum Newbie
Posts: 7
Joined: Tue Dec 24, 2002 1:31 am

Post by n00b »

Thanks but thats double dutch to me. I know PHP not MySQL that well.

WOuld you be able to explain it for me? I went to that site and its even harder to understand.
User avatar
mydimension
Moderator
Posts: 531
Joined: Tue Apr 23, 2002 6:00 pm
Location: Lowell, MA USA
Contact:

Post by mydimension »

okay, i can do that. lets start with:

Code: Select all

FROM privms AS m, privmstext AS t
this is selecting which tables to use in the query and makes it easy for you to write your queries by aliasing them (i.e. privms can now be refered to as m).

Code: Select all

SELECT m.id, m.usr_from, m.usr_to, m.date, m.subject, t.text
the SELECT part of it does just that, it selects which fields you want to return. since you are selecting from two tables you must specify the table and the field for each field (i.e tablename.fieldname or aliasname.fieldname).

Code: Select all

WHERE m.id=t.text_id
the WHERE section is where the actual linking of the tables takes place. here you can relate one field from one table to one field in another table (or even the same table but that is something else entirely). here you are relating the id field from privms to the text_id field from privmstext.

hope this helps and if you need anything, just let me or any of the other helpful people here know.
Post Reply