FRIHOST • FORUMS • FAQ • TOS • BLOGS • DIRECTORY
You are invited to Log in or Register a Frihost Account!

sql schema primary/foreign key question

   Frihost Forum Index -> Scripting -> Php and MySQL
 


ammonkc
can anyone explain to me how to use primary and foreign keys in a mysql schema. how is linking two tables together with primary and foreign keys useful? if I do a select on a table will I be able to pull data from fields in a second table that is linked by a foreign key?
for example:

Table_a
request_id (primary key)
request_type
usr_id (FK)

Table_b
usr_id (primary key)
usr_name
usr_email

can I do this with my select statement:

SELECT usr_name, usr_email, request_type FROM Table_a, Table_b WHERE request_id = '1234'

if not then how would I do this? and what is the advantage of putting my data into 3rd normal form?
I know that I learned this before, but it has been a long time since I've done this stuff, and I need to relearn it. I would really appreciate any explaination. thank you in advanced.
wumingsden
I don't really know anything about this but I guess that its a little like working with tables on Microsodt Excel, isn't it? Have to tables, with data in each and both having primary keys, inabling you to be able to request something and in return you recieve a report in a 3rd table?

Tell me if I'm wrong, I probably am! Crying or Very sad
JustaMin
ammonkc wrote:
can anyone explain to me how to use primary and foreign keys in a mysql schema. how is linking two tables together with primary and foreign keys useful? if I do a select on a table will I be able to pull data from fields in a second table that is linked by a foreign key?
for example:

Table_a
request_id (primary key)
request_type
usr_id (FK)

Table_b
usr_id (primary key)
usr_name
usr_email

can I do this with my select statement:

SELECT usr_name, usr_email, request_type FROM Table_a, Table_b WHERE request_id = '1234'

if not then how would I do this? and what is the advantage of putting my data into 3rd normal form?
I know that I learned this before, but it has been a long time since I've done this stuff, and I need to relearn it. I would really appreciate any explaination. thank you in advanced.


You would do something like this:

Code:

$query="
SELECT usr_name, usr_email
FROM Table_b
INNER JOIN Table_a
ON Table_a.usr_id = Table_b.usr_id
WHERE Table_a.request_id = '1234'
";


You would then extract the rows in a loop:

Code:

$result = mysql_query($query);
while ($row = mysql_fetch_array($result))
{
   extract($row);
        do_whatever;
}


And you would have variables from both tables, ie you could use $usr_name, $usr_email, $request_type etc
Daniel15
Quote:
Table_a
request_id (primary key)
request_type
usr_id (FK)

Table_b
usr_id (primary key)
usr_name
usr_email

can I do this with my select statement:

SELECT usr_name, usr_email, request_type FROM Table_a, Table_b WHERE request_id = '1234'


You're close. You could use this SQL statement:
Code:

SELECT a.request_type, b.usr_email, b.usr_name
FROM table_a AS a, table_b AS b
WHERE a.request_id = '1234'
           AND a.usr_id = b.usr_id


Once you get the row with mysql_fetch_array($result), you would have an array with all the fields you chose (usr_name, usr_email, request_type)

You don't have to do the table aliases (' table AS alias'), and instead use the full table names (SELECT table_a.request_type, table_b.usr_email, table_b.usr_name). But, it's useful for long table names.

Alternatively, you could use a JOIN like JustaMin said. I think that the way I wrote the query is easier to understand.
JustaMin
daniel15 wrote:
I think that the way I wrote the query is easier to understand.


Yep you're probably right there Wink JOIN is useful to know but in this instance that probably is the best way to do it Smile
ammonkc
Thanks guys. that was exactly what I needed. in what kind of situation would it be better to use a join? and do I need to use the dot syntax (a.usrEmail) or could I just put the fields. I know that it is better and less confussing to indicate the table also, but is it required?
JustaMin
ammonkc wrote:
Thanks guys. that was exactly what I needed. in what kind of situation would it be better to use a join? and do I need to use the dot syntax (a.usrEmail) or could I just put the fields. I know that it is better and less confussing to indicate the table also, but is it required?


A join is a more powerful and can help make up more complex queries, but for what you need just the dot syntax works well. A good tutorial on it is found here (scroll through the next few pages too) http://www.1keydata.com/sql/sqljoins.html

You would only use dot syntax where more than 2 tables are involved, for a simple single table query you dont need it.
ammonkc
Quote:

SELECT a.request_type, b.usr_email, b.usr_name
FROM table_a AS a, table_b AS b
WHERE a.request_id = '1234'
AND a.usr_id = b.usr_id


I've been trying to do something like this, but its not really working out for me.
I've got 3 tables that I'm trying to use.

Code:
table_a
requestID (primary key)
usrEmail (FK)
request_type

table_b
usrEmail (PK)
requestID(FK)
usrName
usrDept

table_c
requestID(PK)
request_status
completion_date


and this is the query that I'm trying to use.

Code:
SELECT * FROM table_a AS a, table_b AS b, table_c AS c WHERE
b.request_status = 'pending'
AND c.requestID = 'a.requestID'
AND a.usrEmail = 'b.usrEmail'
ORDER BY c.completion_date


I'm trying to get all of the requests that have a status of 'pending'.
I know you guys explained this earlier, and I thought that I had it down, but this query isn't working for me. I've tried different variations of this query.
ammonkc
Never mind the last question. I tried an INNER JOIN and that worked. you guys were right the INNER Join is pretty powerfull.
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2007 Frihost, forums powered by phpBB.