linking tables

[Johnnyboy123]

So I have 3 tables: student, course_student and course. The student table is a student's registration info and course is the different courses the student can register for. The course_student table should contain the id fields of the other 2 which I should then use to link the tables and for example display all student's registered for current courses.  Student table has an id field: sno and course table has : cid. Course_student contains both. How do I go about linking the tables so the sno id field of the student table and the cid from course updates with those in the course_table?

[Johnnyboy123]

Heres my full code for the registration of the student. The entered info is successfully updated in the table.

<?php
if (isset($_POST['submit'])) {

	// forms inputs set to variables




$sname = mysql_real_escape_string($_POST['sname']); 
$init = mysql_real_escape_string($_POST['init']); 
$fname = mysql_real_escape_string($_POST['fname']); 
$title = mysql_real_escape_string($_POST['title']); 
$msname = mysql_real_escape_string($_POST['msname']); 
$dob = mysql_real_escape_string($_POST['dob']); 
$sex = mysql_real_escape_string($_POST['sex']);  
$lang = mysql_real_escape_string($_POST['lang']); 
$idno = mysql_real_escape_string($_POST['idno']); 
$telh = mysql_real_escape_string($_POST['telh']); 
$telw = mysql_real_escape_string($_POST['telw']); 
$cell = mysql_real_escape_string($_POST['cel']); 
$fax = mysql_real_escape_string($_POST['fax']); 
$email = mysql_real_escape_string($_POST['email']);
$address = mysql_real_escape_string($_POST['address']); 

$errorstring =""; //default value of error string


if (!$sname)
	$errorstring = $errorstring . "<b>Surname:" .blankfield() ;

if (!$fname)
	$errorstring = $errorstring . "<b>First name:".blankfield();

if (!$title)
	$errorstring = $errorstring . "<b>title:" .blankfield();


if (!is_numeric($dob))
	$errorstring = $errorstring . "<b>Date of birth:".nrfield();



if (!$sex)
	$errorstring = $errorstring . "<b>sex:" .blankfield();

if (!$idno)
	$errorstring = $errorstring . "<b>id number:" .nrfield();

if (!$email)
	$errorstring = $errorstring . "<b>email address:".blankfield();

if (!$address)
	$errorstring = $errorstring . "<b>address:".blankfield();


if ($errorstring!="")
	echo "<h1> Please click <a href='student_reg.php'>here</a> to return to the form page and fill out the following fields: </h1><br>$errorstring";

else
{


	// query
$sql = "INSERT INTO student (sno, sname, init, fname,
						title, msname, dob, sex, lang, idno,
						telh, telw, cel, fax, email, address
						)



	VALUES 				('', '$sname', '$init', '$fname', 
						'$title', '$msname', '$dob', '$sex','$lang',
						'$idno', '$telh', '$telw', '$$cell', '$fax',
						'$email', '$address')";






          mysql_query($sql) or die('Error:' . mysql_error()); 
	  
	  echo "You have successfully registerd. Click <a href='index.php'> here</a> to return to the home page";

} 
?>

 

The sno field is auto_increment and i cannot change the db structure. The sno updates fine in the student table but how do I edit this so that the sno field, which is generated by the new student when he registers, gets entered in the sno field of my other table course_student aswell? Anyone? Thanks in advance.

[wildteen88]

You can get the generated id of the previously inserted row with mysql_insert_id

[Johnnyboy123]

Ah thanks, can you use that in a short example for me so I can get the idea please? 

[wildteen88]

if the sno field auto_increments you'd do

          mysql_query($sql) or die('Error:' . mysql_error());
          $sno_id = mysql_inser_id();

$student_id will now hold the id of the student just inserted into your database.

 

You can assign $student_id to a session so you have access to it throughout your site.

$_SESSION['student_id'] = $student_id;

In order for sessions to work properly make sure you call session_start as the first line of your PHP scripts.

 

[Johnnyboy123]

Ah ok cool thanks seems to work fine.  I'm trying to get the cid from the course the student registers for now. Theres a course table with the availible courses and their cid's. I want the cid of the cname (course name) which the students selects to register for to be sent to the course_student table along with the sno. This sno and cid will represent the student and what course he/she is studying for in the course_student table. I'm trying something like this:

 

$cname = mysql_real_escape_string($_POST['cname']);

$sql3 = "SELECT cid FROM course WHERE $cname = 'cname'

INSERT INTO course_student (cid)";

mysql_query($sql3) or die('Error:' . mysql_error());

 

Is this on the right path?

[wildteen88]

Mysql_query() can only process one query at a time. So you will have to get the cid from the course table first and then insert the cid into your course_student table.

[Johnnyboy123]

damn.. is there maybe another solution or command? Because the cid and sno should be linked together. There are already courses in the course table with cids. When registering, the student selects a course. Upon registration the student generates a sno which should go to the course_student table along with the cid of the chosen course on registration. Because this cid and sno will be linked. On other pages I will need to use this cid and sno to display students registered for a specific course etc. So in the same row of the course_student table the cid and sno should be connected to each other as student and course he registered for. Fun Times 

 

Any suggestions? Thanks for the help til thus far you have really aided me alot on my posts.

[wildteen88]

You just need to separate your queries

 

// get the cid
$cname = mysql_real_escape_string($_POST['cname']); 
$getCID = "SELECT cid FROM course WHERE cname='$cname'";
$result = mysql_query($getCID);

if($result)
{
    $row = mysql_fetch_assoc($result);
    $course_id = $row['cid'];

     // add the student to the course_student table
     $addCID = 'INSERT INTO course_student (cid, sno) VALUES(' . $row['cid'] . ', ' . $YOUR_STUDENT_ID_VARIABLE . ')';
     mysql_query($addCID) or die('Error:' . mysql_error());
}

 

Or If the student is already within your course_student table then you'll run an update query

$addCID = 'UPDATE course_student SET cid=' . $row['cid'] . ' WHERE sno=' . $YOUR_STUDENT_ID_VARIABLE;

[Johnnyboy123]

Dude you're legend. Thanks man

[Johnnyboy123]

Home stretch, I'm almost done with all this linking table nonsense. Just have to do 1 more thing. I'm trying to make a list page. Basicly displaying a course from the course table and when you click on it all the registered students for that course has to be displayed from the student table. This is tricky ( for me atleast ) as I have to get the cid from the course table, then use that cid to determine the students registered for the course in the course_student table ( which contains the cid of a course and the sno of a student registered for the course) to display the fname and sname (first name and surname) from the student table of the student registered for the course.

 

I'm trying something like this :

<?php
$q = "SELECT sno FROM course_student WHERE cid ='".$_GET['cid']."'";
$confirm_query = mysql_query($q);
$rsconfirm = mysql_fetch_assoc($confirm_query);

if ($rsconfirm){

$q2 = "SELECT fname,sname FROM student WHERE $rsconfirm = 'sno'";
$confirm_query2 = mysql_query($q2);
$rsconfirm2 = mysql_fetch_assoc($confirm_query2);

do{
echo "Student:  " . $rsconfirm2['fname'] . "," . $rsconfirm2['sname'];
}

while ($rsconfirm = mysql_fetch_assoc($confirm_query));
}
?>

 

I'm not getting any errors, although it doesn't display any students. I'm guessing somewhere along the lines I lost the value or my query just doesn't make sense?

[Dzherzinsky]

 

 

When a student fills in the form to register for a course, either it is old students or new students. Old students should be able to be pulled from a drop down ajax menu or a Suggest text field. New students just by a text field

 

As per the courses, one student can register at one or more courses, so you will have a Multiple select drop down menu.

 

 

 

Your Associative table looks like this: csid |  sno | cid

 

So, you first send an insert query to the Students table in case it is a new student.

 

and on doing that, you pick that id(sno) afterwards.

 

Then you need a loop where the outer loop is the student and the inner loop the roster of courses he has chosen.

 

that loop is the one that that inserts directly into the Associate Table, that is, you do as many inserts as courses he has chosen.

 

 

Quoting what you wrote

 

$cname = mysql_real_escape_string($_POST['cname']);

$sql3 = "SELECT cid FROM course WHERE $cname = 'cname'

INSERT INTO course_student (cid)";

mysql_query($sql3) or die('Error:' . mysql_error());

 

Is this on the right path?

===========================================

 

Did you notice that there is a wrong in your query, it should have been WHERE 'cname' = $cname and not as you have written, it, swap them

 

but that query alone would not be enough to do multiple inserts, you need the loop (to add several courses)

 

 

[wildteen88]

Your query here is all wrong

$q2 = "SELECT fname,sname FROM student WHERE $rsconfirm = 'sno'";

That code will produce the following query

SELECT fname,sname FROM student WHERE Array = 'sno'

That query will tell MYSQL to fetch the fname and sname fields from the student table where the field Array equals sno.

 

What you want to do is use a JOIN, Example query

SELECT student.fname, 
       student.surname,
      student_course.cname
FROM student
LEFT JOIN student_course
    ON (student_course.sno = student.sno)
WHERE student_course.cid = $cid

Your fixed code

if(isset($_GET['cid']))
{
    $cid = (int) $_GET['cid'];
    $query = "SELECT student.fname, 
                     student.surname,
                     student_course.cname
              FROM student
              LEFT JOIN student_course
                  ON (student_course.sno = student.sno)
              WHERE student_course.cid = $cid";

    $result = mysql_query($q);
    
    if($result)
    {
        if(mysql_num_rows($result) > 0)
        {
            echo 'Student\'s Signed up to course: <b>' . $row['cname'] . '</b><br />';
            while($row = mysql_fetch_assoc($result))
            {
                echo $row['fname'] . ' ' . $row['sname'] . '<br />';
            }
        }
        else
        {
            echo "No results returned!";
        }
    }
    else
    {
        echo "Problem with the query: <pre>$query</pre>Error: " . mysql_error();
    }
}

 

 

[Johnnyboy123]

Here is my code now with your changes:

<?php
$q = "SELECT sno FROM course_student WHERE cid ='".$_GET['cid']."'";
if(isset($_GET['cid']))
{
    $cid = (int) $_GET['cid'];
    $query = "SELECT student.fname, 
                     student.surname,
                     student_course.cname
              FROM student
              LEFT JOIN student_course
                  ON (student_course.sno = student.sno)
              WHERE student_course.cid = $cid";

    $result = mysql_query($q);
    
    if($result)
    {
        if(mysql_num_rows($result) > 0)
        {
            echo 'Student\'s Signed up to course: <b>' . $row['cname'] . '</b><br />';
            while($row = mysql_fetch_assoc($result))
            {
                echo $row['fname'] . ' ' . $row['sname'] . '<br />';
            }
        }
        else
        {
            echo "No results returned!";
        }
    }
    else
    {
        echo "Problem with the query: <pre>$query</pre>Error: " . mysql_error();
    }
}
?>

 

I'm getting the following notices:

 

Notice: Undefined variable: row in C:\Program Files\EasyPHP-5.3.3\www\Project\test.php on line 44

Student's Signed up to course:

 

Notice: Undefined index: fname in C:\Program Files\EasyPHP-5.3.3\www\Project\test.php on line 47

 

Notice: Undefined index: sname in C:\Program Files\EasyPHP-5.3.3\www\Project\test.php on line 47

 

Just to clear up on these following lines. In the query where you specified student_course, did you mean the course_student table or is that a joining of the student and course table ( Ive never used JOIN before ). Also

"student_course.cname" (third line of the query) did you refer to the cname in the course table? as it's the only table with cname in it. I tried editing all these things I pointed out to what I just mentioned you may be referring to, still got the same notices though.

 

[wildteen88]

Sorry the code I gave you is incorrect. I didn't think it through properly. In your query you'll actually need to perform a join on both the course_student and course tables. Try this code

<?php

if(isset($_GET['cid']))
{
    $cid = (int) $_GET['cid'];
    $query = "SELECT student.fname,
                     student.sname,
                     course.cname
              FROM student
              LEFT JOIN course_student
                  ON (course_student.sno = student.sno)
              LEFT JOIN course
                  ON (course_student.cid = course.cid)
              WHERE course_student.cid = $cid";

    $result = mysql_query($query);

    if($result)
    {
        if(mysql_num_rows($result) > 0)
        {
            $courseName = mysql_result($result, 0, 2); // get the course name
            echo 'Student\'s Signed up to course: <b>' . $courseName . '</b><br />';

            mysql_data_seek($result, 0); // reset the internal data pointer back to the first row in the result set.
            while($row = mysql_fetch_assoc($result))
            {
                echo $row['fname'] . ' ' . $row['sname'] . '<br />';
            }
        }
        else
        {
            echo "No results returned!";
        }
    }
    else
    {
        echo "Problem with the query: <pre>$query</pre>Error: " . mysql_error();
    }
}

?>

 

 

[Johnnyboy123]

Hehe thanks man works like a charm now. I'll be sure to study the code over so I know what you did and why you did it also as to learn about JOIN tables. Just really pressed for time with all this as my deadline is Friday, but really thanks for all your help, learning alot

[Dzherzinsky]

1. Where are you leaving the possibility that the student registers for more than one course out of a select list ?  It has to be a loop query like I said.

 

 

2. If you write "isset" that could be cheated by filling a white space and would mess up the query. You would rather use if (!empty) to avoid that.