Hello,

I'm working on a website and needed some help with my php. My website basically allows a user to register for an account then add a college course to their page through scheduler.php


I have three sql scripts: accountInformation, course, and account_courses.

I need account_courses to hold the two primary keys in accountInformation and course tables because its a many to many. Students can have many courses and courses can have many students.

Here are my sql scripts:

CREATE TABLE courses(
	courseID int primary key not null auto_increment,
	courseName varchar(25),
	courseDescription text,
	courseDepartment varchar(4),
        courseStartTime time,
	courseEndTime time,
	courseSemester varchar(20),
	courseBuilding varchar(20),
	courseRoomNumber int
);
CREATE TABLE accountInformation(
	accountID int primary key not null auto_increment,
	username varchar(25),
	password varchar(20),
	email varchar(30)
,	firstname varchar(20),
	lastname varchar(20),
	lastLogin timestamp DEFAULT 0);
CREATE TABLE account_courses(
	accountID int, 
	courseID int,
	foreign key(accountID) references accountInformation(accountID),
	foreign key(courseID) references courses(courseID), 
	primary key(accountID,courseID)
);

This is my scheduler which allows the user to create a course. each course created refers to a ID. such as "http://localhost/1.1/course.php?courseID=1" How can I tie the accounts and the courses together using the account_courses table in php??

<?php
/**
*/

	require_once './lib/libs.php.inc';
	
	/* short variable */
	$cn  		        = $_POST['courseName'];
	$cd                 = $_POST['courseDescription'];
	$cdep               = $_POST['courseDepartment'];
	$st					= $_POST['startTime'];
	$et					= $_POST['endTime'];
    $cs				    = $_POST['courseSemester'];
	$bld				= $_POST['building'];
	$rn					= $_POST['roomNumber'];
	
	$query = "INSERT INTO courses VALUES('','$cn','$cd','$cdep','$st','$et','$cs','$bld','$rn')";
	$result = query($query);
	

	/*Return to Scheduler*/
	echo '<a href="/1.1/scheduler.php">Add Another Course</a>.';	
	
	/* error checking */
	if($cn == null) {
		echo "The username field cannot be null";
	}
	
	if($cd == null) {
		echo "The course description field cannot be null";
	}

This is many to many relation ship. So instead of creating page for account_course table, you should allow user to enter students for course in course entry page, and allow user to enter course for students in student entry page. I mean header detail kind of form. I hope I understood your question properly.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.