I have a database called students. I also have two tables in it called current_students and subjects. One table holds all the names of students while the other holds subjects offered.
I have an HTML page that shows current students. I call it current_students.php. When I view this page it shows all the current students in my database. I want to be able to see the subjects that each current student is enrolled on when I click on their names. So, I created another HTML page and called it subjects.php. At the moment, I have a page that shows all the subjects students are doing.
But my problem now is, how do I show the names of current students and when I click on a particular student another page opens and shows me the subject that particular student is doing? I am not using any form in my design, but you can see the code for the two pages below please.
The code below is for the current_students.php page.
Code:
<html>
<head><title>Making links from one page to another</title>
</head>
<body>
<?php
//set the database login details
$hostname = "localhost";
$username = "root";
$password = "";
//connecting to the database
$conn = mysql_connect($hostname, $username, $password)
or die("There was an error connecting" .mysql_error());
//select the student database to use
mysql_select_db("students", $conn);
$result = mysql_query("SELECT FirstName, LastName FROM current_students ORDER BY FirstName ASC");
echo "<strong>Current students " ."</strong>" ."<br/>";
while ($row = mysql_fetch_array($result))
{
echo $row{'FirstName'} . " ". " " ." " .$row{'LastName'} . "<br/>" ;
}
mysql_close($conn);
?>
</body>
</html>
The code below is for the subjects.php page.
Code:
<html>
<head><title>subjects by current students</title>
</head>
<body>
<?php
//set the database login details
$hostname = "localhost";
$username = "root";
$password = "";
//connecting to the database
$conn = mysql_connect($hostname, $username, $password)
or die("There was an error connecting" .mysql_error());
//select the student database to use
mysql_select_db("students", $conn);
$result = mysql_query("SELECT Subject_Name FROM subjects ORDER BY Subject_Name ASC");
echo "<strong>Subjects enrolled on " ."</strong>" ."<br/>";
while ($row = mysql_fetch_array($result))
{
echo $row{'Subject_Name'} ."<br/>" ;
}
mysql_close($conn);
?>
</body>
</html>
This is the sql code for the structures of the tables.
Code:
//I created the current_students table through phpmyadmin
CREATE TABLE current_students (
id INT (4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR (20) NOT NULL,
LastName VARCHAR (20) NOT NULL);
//I created the subjects table with this code through phpmyadmin
CREATE TABLE subjects (
id INT (4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
Subject_Name VARCHAR (50) NOT NULL,
term VARCHAR (20) NOT NULL);
//I inserted the subjects into the table with the code below throw phpmyadmin
INSERT INTO `students`.`subjects` (`id`, `Subject_Name`, `term`) VALUES (NULL,
'History', 'First term'), (NULL, 'Geography', 'Second term');
INSERT INTO `students`.`subjects` (`id`, `Subject_Name`, `term`) VALUES (NULL,
'Economics', 'Third term'), (NULL, 'ICT', 'Second term');
INSERT INTO `students`.`subjects` (`id`, `Subject_Name`, `term`) VALUES (NULL,
'Mathematics', 'Third term'), (NULL, 'Physics', 'First term');
INSERT INTO `students`.`subjects` (`id`, `Subject_Name`, `term`) VALUES (NULL,
'Programming', 'second term'), (NULL, 'Arts', 'First term');
INSERT INTO `students`.`subjects` (`id`, `Subject_Name`, `term`) VALUES (NULL,
'Swimming', 'Third term'), (NULL, 'Football', 'First term');
INSERT INTO `students`.`subjects` (`id`, `Subject_Name`, `term`) VALUES (NULL,
'Mountain climbing', 'Second term'), (NULL, 'Music', 'Second term');
Looking at the images, what I want to do is, for example, when I click on Anna Benda it show me another page with the subjects she is enrolled on.
Am I on the right process of achieving this goal? If not, could someone suggest to me how to do it please? I will appreciate your help.
sort of. the first thing to note is that these tables are completely separate, meaning that you have no relation between them. in order for you to look up which classes Anna Benda has, you must have data stored in the database that says what courses Anna Benda is actually enrolled in. the best way to do this for your case (since you have a term/semester defined) is to have one more table that represents enrolled subjects (or grades) that keeps track of the student, course, and the semester (which means you'd get rid of the term in your subject table). to keep it as simple as possible, we would have a table structure like so:
in this way, the Grades table is related to both the Students and Courses table and has a composite primary key consisting of the StudentID, CourseID, and SemesterID (that means all of those fields put together are one primary key, rather than having one field be the primary key).
if you don't want to go this route (perhaps you think it's too complicated), you could simply add a student ID field to your subjects table and work with that; the structure I've made above is something you'd most likely use in a large system though. if you're just messing around, it might not mean all that much to you (especially if you don't understand normalization).
I'm going to continue this post as if you were using the new structure of tables I created. you can basically do the same type of thing if you were using your own tables (with the slight modification I mentioned before), though.
now, we can modify the code of your first script just a bit so that when we visit subjects.php we'll know which student we want to look up. in addition to grabbing the first and last name of the student, we also want to get the student's ID to use as a look-up.
PHP Code:
<strong>Current students</strong><br />
<?php
$result = mysql_query("SELECT id as StudentID, FirstName, LastName FROM current_students ORDER BY FirstName ASC");
I also took the liberty of separating your PHP and HTML. anyway, now if we load this page we should be able to click any of the students' names. If we click Anna Benda's name, and let's say that her student ID is 5, we will be visiting the page /subjects.php?student=5. we can then, in the subjects.php script, access this variable and use it to look up that students' courses.
now, the courses table relates to the student table via the grades table, and so to get from the student ID to the course table we'll need to join the three tables together:
Code:
SELECT c.Name
FROM Students s
LEFT JOIN Grades g ON s.ID=g.StudentID
LEFT JOIN Courses c ON g.CourseID=c.ID
WHERE s.StudentID=$StudentID
we also have a where clause to let the database know which student we're actually interested in.
now, we can modify your subjects.php script to use this query and also get the variable we're sending in the query string from the students.php script. first things first: get the variable. we use the $_GET global array to grab the variable from the GET request:
PHP Code:
<strong>Subjects enrolled in:</strong><br />
<?php
// if the query string variable 'student' is set and is numeric, use that. otherwise, default to 0.
$StudentID = isset($_GET['student']) && is_numeric($_GET['student']) ? mysql_real_escape_string($_GET['student']) : 0;
$result = mysql_query("SELECT c.Name as CourseName FROM Students s LEFT JOIN Grades g ON s.ID=g.StudentID LEFT JOIN Courses c ON g.CourseID=c.ID WHERE s.StudentID=$StudentID ORDER BY c.Name ASC");
Alright Kows, thanks for the code and suggestions. I am now working with it. I will get back to you after making the changes. It makes sense, I am waiting for the output.
Menre
Once again, thanks. I am doing it as you have suggested. It seems to be producing the desired results though not completely yet. There are still a few problems with the work. I re-created the tables with the sql code below.
Code:
//I created the Students table through phpmyadmin with the code below.
CREATE TABLE Students (
ID INT (4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
Firstname VARCHAR (20) NOT NULL,
Lastname VARCHAR (20) NOT NULL);
//I created the Courses table with the code below through phpmyadmin
CREATE TABLE Courses (
ID INT (4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR (50) NOT NULL);
//I created the Grades table with the code below through phpmyadmin
//I created it successfully but without setting any FOREIGN KEY
CREATE TABLE Grades (
StudentID INT (4) NOT NULL,
CourseID INT (4) NOT NULL,
SemesterID INT (4) NOT NULL,
Grade DECIMAL (4) NOT NULL,
PRIMARY KEY(StudentID,CourseID,SemesterID)
);
…in order for you to look up which classes Anna Benda has, you must have data stored in the database that says what courses Anna Benda is actually enrolled in. the best way to do this for your case (since you have a term/semester defined) is to have one more table that represents enrolled subjects (or grades) that keeps track of the student, course, and the semester…
I have created another table with the sql code below for EnrolledSubjects.
Code:
CREATE TABLE EnrolledSubjects(
ID INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
SubjectName VARCHAR(255),
SudentName VARCHAR(20)
);
I entered their respective subjects with the following code.
The few problems that I am having now are:
1) I am able to add a grade to the table for only one student. When I try to add a grade for another student, I get an error about duplicate entry.
2) Why am I getting this error message from the subjects page?
Code:
Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\menre\students\subjects.php on line 27
3) How do I make the subjects for respective students appear on the subjects page?
This is the code you posted earlier which I have now used for the students.php page.
[code]<?php echo "<strong>Current students</strong><br />"; ?>
<?php
$result = mysql_query("SELECT id as StudentID, FirstName, LastName FROM students ORDER BY FirstName ASC");
mysql_close($conn);
?>[code]
This is the code you posted earlier which I have now used for the subjects.php page.
Code:
<?php echo "<strong>Subjects enrolled in:</strong><br />";?>
<?php
// if the query string variable 'student' is set and is numeric, use that. otherwise,
default to 0.
$StudentID = isset($_GET['student']) && is_numeric($_GET['student']) ?
mysql_real_escape_string($_GET['student']) : 0;
$result = mysql_query("SELECT c.Name as CourseName FROM Students s LEFT JOIN Grades g ON
s.ID=g.StudentID LEFT JOIN Courses c ON g.CourseID=c.ID WHERE s.StudentID=$StudentID ORDER
BY c.Name ASC");
while ($row = mysql_fetch_assoc($result))
{
?>
<?php echo $row['CourseName']; ?><br />
<?php
}
mysql_close($conn);
?>
You can see the display of the two pages from the images attached please.
The point of the Grades table is to tell you who is enrolled. You don't need an "Enrolled Students" table in the first place. The SemesterID tells you who is currently rolled based on the current semester. The SemesterID is generally not an integer (the schools I've seen using a system like this name their semesters by the year -- 2009A, 2009B, 2010A, 2010B, etc), though you could make it one if you wanted.
1) I am able to add a grade to the table for only one student. When I try to add a grade for another student, I get an error about duplicate entry.
The primary keys for the Grades table are the StudentID, CourseID, and the SemesterID. No combination of these three fields can exist more than once in this table -- one student cannot take the same course multiple times in the same semester. If you're getting a duplicate error, then you may just be doing something wrong.
2) Why am I getting this error message from the subjects page?
mysql_query() will return false if there is an error with your SQL statement. I suggest modifying your statement slightly so that you can see this error message for now:
The error message is important, but also the SQL -- post them both as a reply if you don't know what may be wrong. Also, take note that for ease of doing this you'll need to create a variable ($sql) that holds the SQL, rather than having it as a literal string in the function's parameter.
3) How do I make the subjects for respective students appear on the subjects page?
The code I gave you is already all of the basic code that you would need to use it. I just generally provide abstract examples to give you the idea of how something works. If you continue to have problems, though, I'll probably create this example myself and see what's wrong.
I have deleted the EnrolledSubjects table as it is not needed. I have altered the semester table by changing the SemesterID from INT to VARCHAR as shown below.
Code:
CREATE TABLE Grades (
StudentID INT (4) NOT NULL,
CourseID INT (4) NOT NULL,
SemesterID VARCHAR (6) NOT NULL,
Grade DECIMAL (4) NOT NULL,
PRIMARY KEY(StudentID,CourseID,SemesterID)
);
To test where the sql errors are coming from, I added this code to the page
<?php
//set the database login details
$hostname = "localhost";
$username = "root";
$password = "";
//connecting to the database
$conn = mysql_connect($hostname, $username, $password)
or die("There was an error connecting" .mysql_error());
//select the student database to use
mysql_select_db("students", $conn);
?>
<?php echo "<strong>Subjects enrolled in:</strong><br />";?>
<?php
mysql_query($sql) or die("Error: " . mysql_error() . "
SQL: " . $sql);
// if the query string variable 'student' is set and is numeric, use that. otherwise,
default to 0.
$StudentID = isset($_GET['student']) && is_numeric($_GET['student']) ?
mysql_real_escape_string($_GET['student']) : 0;
$result = mysql_query("SELECT c.Name as CourseName FROM Students s LEFT JOIN Grades g ON
s.ID=g.StudentID LEFT JOIN Courses c ON g.CourseID=c.ID WHERE s.StudentID=$StudentID ORDER
BY c.Name ASC");
while ($row = mysql_fetch_assoc($result))
{
?>
<?php echo $row['CourseName']; ?><br />
<?php
}
mysql_close($conn);
?>
When I viewed the above subjects.php page, the error I got is shown below.
Code:
Subjects enrolled in:
Notice: Undefined variable: sql in C:\xampp\htdocs\menre\students\subjects.php on line 23
Notice: Undefined variable: sql in C:\xampp\htdocs\menre\students\subjects.php on line 24
Error: Query was empty SQL:
How do I correct the errors mentioned above please?
…one student cannot take the same course multiple times in the same semester. If you're getting a duplicate error, then you may just be doing something wrong.
You are right, I was doing something wrong before by trying to make multiple entries for one student and the primary keys StudentID, CourseID, and the SemesterID prevented that.
Also, take note that for ease of doing this you'll need to create a variable ($sql) that holds the SQL, rather than having it as a literal string in the function's parameter.
the point is to replace your mysql_query() call with the call that I made an example of in my post. you need to create a variable called $sql that holds the query in your script:
I seem to be lost here. When I a changed the code to what is shown below, I got one more error message.
Code:
<?php echo "<strong>Subjects enrolled in:</strong><br />";?>
<?php
$sql = mysql_query($sql) or die("Error: " . mysql_error() . "
SQL: " . $sql);
// if the query string variable 'student' is set and is numeric, use that. otherwise,
default to 0.
$StudentID = isset($_GET['student']) && is_numeric($_GET['student']) ?
mysql_real_escape_string($_GET['student']) : 0;
$result = mysql_query("SELECT c.Name as CourseName FROM Students s LEFT JOIN Grades g ON
s.ID=g.StudentID LEFT JOIN Courses c ON g.CourseID=c.ID WHERE s.StudentID=$StudentID ORDER
BY c.Name ASC");
while ($row = mysql_fetch_assoc($result))
{
?>
<?php echo $row['CourseName']; ?><br />
<?php
}
mysql_close($conn);
?>
Code:
Subjects enrolled in:
Notice: Undefined variable: sql in C:\xampp\htdocs\menre\students\subjects.php on line 23
Error: Query was empty SQL:
I think I did not understand what you mean with your last post.
this will show your error and the SQL that caused that error, and if you change the SQL you won't have to update the or die() statement because the SQL will be stored in a variable.
Hello Kows,
Sorry for taking so long to get back to you with the update of my work. I tried to read up on certain areas of PHP and MySQL before in other to understand the process of database normalization.
As for this work, I have amended the code. The code for the students' page is below.
Code:
<?php
//set the database login details
$hostname = "localhost";
$username = "root";
$password = "";
//connect to the database
$conn = mysql_connect($hostname, $username, $password)
or die("There was trouble connecting" .mysql_error());
//select the student database to use
$sql = mysql_select_db("students", $conn);
$result = mysql_query("SELECT id as StudentID, Firstname, Lastname FROM
students ORDER BY FirstName ASC");
echo "<strong>Current students " ."</strong>" ."</br>";
while ($row = mysql_fetch_assoc($result))
{
?>
<a href="subjects.php?student=<?php echo $row['StudentID']; ?>"><?php echo
$row['Firstname'] . " " . $row{'Lastname'} . "<br/>"; ?></a>
<?php
}
$sql = mysql_close($conn);
?>
While the code for the subjects page is placed below.
Code:
<?php
//set your database connection details here
$hostname = "localhost";
$username = "root";
$password = "";
//now make connection to the database
$conn = mysql_connect($hostname, $username, $password)
or die("I was not able to connect!" . mysql_error());
echo "Subjects taken by students <br/>";
//here we select a database to use
$sql = mysql_select_db("students", $conn);
//here we query a table
$result = mysql_query("SELECT Name FROM courses");
while ($row = mysql_fetch_array($result))
{
echo $row['Name'] . "<br/>";
}
?>
When I preview the student page, I get something like what is stated below.
Code:
Current students
Anna Benda
Bill Craig
Binta Parks
Clara Ruppert
Dan Phil
Dora Steve
Emma Newton
Fiona Hills
Jack Penn
Lorna Jones
Monna Jones
Morris Kings
Perry Homes
Tara Zora
Zida George
When I click on any of the names for the students, it takes me to the subjects page like the one shown below.
Code:
Subjects taken by students
Arts
Economics
Football
Geography
History
ICT
Mathematics
Mountain Climbing
Music
Physics
Programming
Swimming
I am quite happy with the way that the url changes and shows.
But my question now is, how do I go ahead with the work so that when I click on a student's name only the subjects he/she is enrolled in will appear on the subjects page and not the whole subjects in the table? I will appreciate your suggestions please.
Thanks,
Menre
SELECT c.Name
FROM Grade g
LEFT JOIN Courses c ON c.CourseID=g.CourseID
WHERE g.StudentID={$studentID}
you can remove the line breaks -- that just made it easier to read. $studentID is a variable that you should create from $_GET['student'] (with mysql_real_escape_string(), because we're also sending raw user input to the database otherwise):
you may notice, however, that if you view your subjects.php script without a query string set, that you could get a warning/notice that $_GET['student'] doesn't exist. we can do simple validation when defining this variable then:
whichever you use doesn't matter. then, your query will run with a studentID of 0 (which will most likely never exist), and just won't return any results. you could also display some sort of error message.
Code:
if(!$studentID){
echo "You didn't pick a student.";
}else{
// the rest of your script would basically go here
}
because 0 evaluates to false in PHP, we don't need to explicitly check if $studentID is equal to zero ($studentID == 0), and instead we can just check if $studentID evaluates to false (!$studentID).
Hi Kows,
Apologies for getting back to you late. I have been working on this task. After many weeks of reading tutorials on database Normalization and working with the suggestions on your last post, I am still not able to get this thing to work for me. However, I have learned more about working with database normalization such as joining tables, but not with this problem that I am trying to solve here.
I used the code you suggested
Code:
SELECT c.Name FROM Grade g LEFT JOIN Courses c ON c.CourseID=g.CourseID WHERE g.StudentID={$studentID}
on my page as the one below.
Code:
<html>
<head><title>Subjects taken by each student</title></head>
<body>
<?php
//set your database connection details here
$hostname = "localhost";
$username = "root";
$password = "";
//now make connection to the database
$conn = mysql_connect($hostname, $username, $password)
or die("I was not able to connect!" . mysql_error());
echo "Subjects taken by students <br/>";
//here we select a database to use
$sql = mysql_select_db("students", $conn);
//here we query a table
$result = mysql_query("SELECT c.Name FROM Grades g LEFT JOIN Courses c ON c.CourseID=g.CourseID WHERE g.StudentID={$studentID}");
while ($row = mysql_fetch_array($result))
{
echo $row['Name'] . "<br/>";
}
?>
</body>
</html>
I got an error message.
Code:
Subjects taken by students
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in
C:\xampp\htdocs\menre\student\subjects.php on line 22
I tried a different aproach in phphmyadmin
Code:
SELECT C.Firstname, C.Lastname, O.CourseID, O.StudentID FROM students AS C, grades AS O;
I got the result:
Code:
MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0011 sec )
the code you posted doesn't work only because you skipped over the part in my post where you define the variable $studentID. you're never defining it, but you are trying to use it. your query ends up being invalid and raises an error.
all you basically have to do is grab the above mentioned code (one of the three definition methods of $studentID) from this post and your script should work fine.
also, you're getting repeated results in your query because you are not filtering anything. all you're doing is selecting all results from the table and joining to another table; a WHERE clause would add a filter if you wanted to show only one student, or only one course, etc. I would also advise you to stay away from that method of table joining, though really it's all about preference. I just consider joins much easier to read and interpret.
Hi Kows,
I have taken the topic of sql as something serious to learn. I am busy with some online MySQL tutorials and a book on sql "Beginning SQL, by Paul Wilton, John Colby". They have a lot of content similar to your previous posts and advice on this thread so far.
...I would also advise you to stay away from that method of table joining,...
I like this advice, as many of the examples used in the book and tutorials also point that way.
One question before I come back to the issue of solving the problem I am working on, does this idea of 'joins' wok only in PHP and MySQL or with other databases tools as well?
Apologies for responding to you late and reviving this post every now and then, but I must admit that it is helping and making me to be serious with learning SQL.
Joins are a part of SQL; they've got nothing to do with PHP. MSSQL (Microsoft SQL), PostgreSQL, SQLite, Oracle and probably every other flavour of SQL also includes joins.
Reply With Quote
