I Need Some Guidance On Getting A Relational Db To "come Together"

[FoxRocks]

Good day,

 

First, thank you for taking the time to look at my post, I appreciate it and I will try not to waste your time.

 

I have recently been challenged to make an employee management database. I have some experience using mySQL and PHP and consider myself "comfortable" with how it works. This is however the first time I've used a relational db for anything. I am having a hard time getting my head around how it's all going to come together in the end, so I'm hoping that you can fill in some blanks for me and help me see the light, so to speak.

 

Right now I have my tables set up and the primary and foreign keys set up in a database. This is the "schedule" :

 

 

And in the interest of keeping the post short, I'll include just on of the referenced tables, that is "area" :

 

 

So as you can see I've referenced the Area_ID in the schedule table and that is because I want to be able to assign an employee to a work area in the shop.

 

Here is a screen shot of one record in the schedule table:

 

 

So as I mentioned I am having trouble seeing how it all comes together. Specifically I am having trouble seeing how "Area ID - 6" is going to translate into "Area_Name - Paint Shop" (record #6 = Paint Shop).

 

Normally when I query the db for the information, I would do something like:

 

$result = mysql_query("SELECT * FROM schedule");

 

And then from there I would usually do something like:

 

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['Emp_ID'] . "</td>";
echo "<td>" . $row['Cal_Date'] . "</td>";
echo "<td>" . $row['Area_ID'] . "</td>";
echo "<td>" . $row['Shift_ID'] . "</td>";
echo "<td>" . $row['Stat_ID'] . "</td>";
echo "</tr>";
}

 

But in this case instead of "Paint Shop" I would, obviously get "6". This is my brick wall that I can't seem to get my head around. I have been reading about JOINS and still, for some reason, I just can't put two and two together.

 

I would really appreciate any help and or constructive criticism you can give.

 

Cheers,

 

~FOX~

[gristoi]

You join the tables by their relation using joins, see below for an example. If you need any more info then let me know

 

SELECT Emp_ID, Cal_Date, a.Area_Name, shift_ID, Stat_ID FROM schedule s
INNER JOIN area a ON s.Area_ID = a.Area_ID

Edited November 15, 2012 by gristoi

[FoxRocks]

Hi gristoi,

 

Thank you for the reply, and for the example. So with the example you gave, would I call out the parameters as normal? Meaning, would

$row['Area_Name']

work? Also, I don't really understand everything you wrote, specifically, what is "schedule s" and "area a" all about?

 

While I wait for a reply, I'll go and read up some more on JOINS to see if I can answer my own question

 

Cheers!

 

~FOX~

[gristoi]

best you read up on a few sql and join tutorials, the mysql website is a good repo to work through. 'schedule s' is declaring an alias for schedule, so instead of typing schedule.fieldname i can just refer to schedule through its alias s, you can call the alias anything you want as long as it has meaning. And yes, now your tables are joined you can pull any fields from either table. Hope this helps

[FoxRocks]

Funny, I was just coming back to let you know I found some answers and I see you've already replied! I was able to figure out the answer to if $row['Area_Name'] would work, and it does...brilliant!

I hadn't found the answer to the other part about the alias, I'll have to do some more reading obviously, but thank you for that.

 

In short, you were a huge help and I really appreciate it. Thank you so much!!

 

~FOX~

[FoxRocks]

In case you're in the mood, I do have one other question. How would it work if I had a table that was reference by the "employee" table (not previously mentioned in my post) and I needed to get that info up to the "schedule" table? For example, the employees might have a drivers license of a certain class, so I've got a table called "license" that is linked to the "employee" table. The columns are called "Lic_ID" in both the "employee" table and the "license" table.

 

Does that make sense? Hopefully.

 

I want to be able to show the "license" class of the "employee" on the "schedule".

 

Cheers,

 

~FOX~

[gristoi]

you would just join the employee and licence tables aswell using the employee table as the reference, so something like this ( a bit rough but you should get the gist of it

SELECT Emp_ID, Cal_Date, a.Area_Name, shift_ID, Stat_ID, [b]l.licence_type[/b] FROM schedule s
INNER JOIN area a ON s.Area_ID = a.Area_ID
[b]JOIN Employee e ON s.Emp_ID = e.Emp_ID
JOIN licence l on l.Lic_ID = e.Lic_ID [/b]

)

[FoxRocks]

Yes! I totally get it now! Finally...the world makes sense again

 

Thank you so much for your help!

 

~FOX~