eanderson

I have searched pages of this forum and can't come up with an answer that's been given that I feel 100% relates to my problem-- I tried posting for help in the mySQL forum, but maybe here is where I need help. I have various different tables -- two notably, instructors and classes. My problem I'm having is how to associate data from the instructors table to the classes table. I don't know if Foreign Keys are the answer, the partial answer, or not an answer at all. I can't seem to handle a good grasp on them. Anyway-- I am trying to give users a drop down menu that lists all of the instructors' names from the instructors table, and have them select one to be submitted in a form which should be saved in a new table (the classes table) to associate each class with an instructor. Here is the code I have for my form selection: Instructor: <select name="instructorid"> <option value="">--</option> <?php $query = mysqli_query("SELECT * FROM instructors ORDER BY instructor_first ASC"); $result = mysqli_query($query) or die (mysqli_error()); while ($r = mysqli_fetch_assoc($result)) { echo "<option value=".$r['instructor_id'].">".$r['instructor_name']."</option>"; } ?> </select> Now, what would be the best way of inserting this information into the database to save the selection so it can be a reference to the class table? Would I create a Foreign Key called instructor_id in the classes table? Is there a better way to do this? I want to eventually be able to pull up the class information to display and instead of it just showing the instructor_id, I want it to be able to display the other instructor information if necessary. I hope this makes sense. Thank you for looking.

I am fairly new at building databases, and am currently putting one together that works with PHP on my server. I run MySQL 5.1.30 and this is my current createtables.php file (which is still in construction): $sql = "CREATE TABLE users( ". user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, ". user_login VARCHAR(20) UNIQUE NOT NULL, ". user_pass CHAR(40) NOT NULL, ". user_firstname VARCHAR(20) NOT NULL, ". user_lastname VARCHAR(20) NOT NULL, ". PRIMARY KEY (user_id)); "; $result = mysqli_query($sql, $life); if (!$result) { die('Could not create table: ' .mysql_error()); } echo "Users table successfully created\n"; $sql = "CREATE TABLE families( ". family_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, ". family_first VARCHAR(20) NOT NULL, ". family_last VARCHAR(40) NOT NULL, ". family_address VARCHAR(90) NOT NULL, ". family_city VARCHAR(40) NOT NULL, ". family_state CHAR(2) NOT NULL, ". family_zip MEDIUMINT(5) NOT NULL, ". family_home1 MEDIUMINT(3) UNSIGNED NOT NULL, ". family_home2 MEDIUMINT(3) UNSIGNED NOT NULL, ". family_home3 MEDIUMINT(3) UNSIGNED NOT NULL, ". family_cell1 MEDIUMINT(3) UNSIGNED NOT NULL, ". family_cell2 MEDIUMINT(3) UNSIGNED NOT NULL, ". family_cell3 MEDIUMINT(3) UNSIGNED NOT NULL, ". family_email VARCHAR(60) NOT NULL, ". family_emergency TEXT NOT NULL, ". PRIMARY KEY (family_id)); "; $result = mysqli_query($sql, $life); if (!$result) { die('Could not create table: ' .mysql_error()); } echo "Family table successfully created\n"; $sql = "CREATE TABLE students( ". student_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, ". family_id INT NOT NULL, ". student_firstname VARCHAR(20) NOT NULL, ". student_lastname VARCHAR(40) NOT NULL, ". student_dob DATE NOT NULL, ". student_enrolldate DATE NOT NULL, ". PRIMARY KEY (student_id); "; INDEX (family_id); "; FOREIGN KEY (family_id) REFERENCES family (family_id); "; ) TYPE = INNODB; "; $result = mysqli_query($sql, $life); if (!$result) { die('Could not create table: ' .mysql_error()); } echo "Students table successfully created\n"; $sql = "CREATE TABLE classes( ". class_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, ". class_type VARCHAR(20) NOT NULL, ". class_start NOT NULL, ". class_day NOT NULL, ". class_time NOT NULL, ". class_duration PRIMARY KEY (student_id)); "; $result = mysqli_query($sql, $life); if (!$result) { die('Could not create table: ' .mysql_error()); } echo "Students table successfully created\n"; $sql = "CREATE TABLE products( ". prod_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, ". prod_number VARCHAR(20) UNIQUE, ". prod_quantity INT NOT NULL, ". prod_cost DEC(6) NOT NULL, ". prod_msrp DEC(6), ". prod_price DEC(6) NOT NULL, ". prod_description TEXT, ". prod_distributor VARCHAR(25) NOT NULL, ". prod_lastedit DATE NOT NULL, ". prod_lastuser VARCHAR(20), ". PRIMARY KEY ( prod_id )); "; $result = mysqli_query($sql, $life); if (!$result) { die('Could not create table: ' .mysql_error()); } echo "Product table successfully created\n"; What I'm having problems with are foreign keys. I know that some of my tables need to be linked to others, and tried to do so with the above code (See the students table): $sql = "CREATE TABLE students( ". student_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, ". family_id INT NOT NULL, ". student_firstname VARCHAR(20) NOT NULL, ". student_lastname VARCHAR(40) NOT NULL, ". student_dob DATE NOT NULL, ". student_enrolldate DATE NOT NULL, ". PRIMARY KEY (student_id); "; INDEX (family_id); "; FOREIGN KEY (family_id) REFERENCES family (family_id); "; ) TYPE = INNODB; "; When I start scripting, this is what I am keeping in mind: For each STUDENT must be connected to 1 FAMILY - Some FAMILIES can have more than 1 STUDENT For each CLASS must have 1 (sometimes more than one) STUDENT For each STUDENT can have 1 CLASS or more Am I on the right track? I am having a hard time figuring out where the remaining foreign keys should be placed.