Re: Making links with PHP
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:
Code:
Students table
+-----------+
| ID | (Primary key)
+-----------+
| Firstname |
| Lastname |
+-----------+
Courses table
+-----------+
| ID | (Primary key)
+-----------+
| Name |
+-----------+
Grades table
+----------------+
| StudentID (FK) |
| CourseID (FK) | (Primary keys)
| SemesterID |
+----------------+
| Grade |
+----------------+
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");
while ($row = mysql_fetch_assoc($result))
{
?>
<a href="subjects.php?student=<?php echo $row['StudentID']; ?>"><?php echo $row['FirstName'] . ' ' . $row['LastName']; ?></a>
<?php
}
?>
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");
while ($row = mysql_fetch_assoc($result))
{
?>
<?php echo $row['CourseName']; ?><br />
<?php
}
?>
this may give you something good to start with at the very least.
hopefully I haven't made this more confusing for you! feel free to ask questions if none of this made any sense to you (or even if some did!).
Re: Making links with PHP
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
2 Attachment(s)
Re: Making links with PHP
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)
);
Quote:
…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.
Code:
INSERT INTO `students`.`enrolledsubjects` (`ID`, `SubjectName`, `SudentName`)
VALUES (NULL, 'History, Geography, Mathematics', 'Binta Parks'), (NULL, 'Physics, Mathematics, Geography', 'Zida George');
INSERT INTO `students`.`enrolledsubjects` (`ID`, `SubjectName`, `SudentName`) VALUES (NULL, 'History, Geography, Music', 'Fiona Hills'), (NULL, 'Physics, Mathematics, Arts', 'Dora Steve')
When I view the page, the student's id actually shows up in the URL like the one below.
Code:
http://localhost/menre/students/subjects.php?student=3
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");
while ($row = mysql_fetch_assoc($result))
{
?>
<a href="subjects.php?student=<?php echo $row['StudentID']; ?>"><?php echo $row['FirstName'] . ' ' . $row['LastName'] . "<br/>"; ?></a>
<?php
}
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.
Thanks,
Menre
Re: Making links with PHP
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.
Quote:
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.
Quote:
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:
PHP Code:
mysql_query($sql) or die("Error: " . mysql_error() . "<br />SQL: " . $sql);
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.
Quote:
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.
Re: Making links with PHP
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
Code:
mysql_query($sql) or die("Error: " . mysql_error() . "
SQL: " . $sql);
so that the subjects.php looks like this
Code:
<?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?
Quote:
…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.
Thanks,
Menre
Re: Making links with PHP
you must have skipped over this part of my post:
Quote:
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:
Code:
$sql = "your SQL statement";
$result = mysql_query($sql) or die("Error: " . mysql_error() . "<br />SQL: " . $sql);
Re: Making links with PHP
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.
Menre
Re: Making links with PHP
you have something like this:
Code:
$result = mysql_query("SELECT ...");
I'm telling you to make it look like this instead:
Code:
$sql = "SELECT ...";
$result = mysql_query($sql) or die("Error: " . mysql_error() . "<br />SQL: " . $sql);
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.
Re: Making links with PHP
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.
Code:
http://localhost/menre/student/subjects.php?student=1
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
Re: Making links with PHP
you have this query:
Code:
SELECT Name FROM courses
you need to modify it to join to the grade table:
Code:
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):
Code:
$studentID = mysql_real_escape_string($_GET['student']);
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:
Code:
$studentID = isset($_GET['student']) ? $_GET['student'] : 0;
this is essentially the shorthand equivalent of the following:
Code:
if(isset($_GET['student'])){
$studentID = $_GET['student'];
}else{
$studentID = 0;
}
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).
hope that made some sense.
1 Attachment(s)
Re: Making links with PHP
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 )
So, I inserted the entry below
Code:
INSERT INTO `students`.`grades` (`StudentID`, `CourseID`, `SemesterID`, `Grade`) VALUES ('1', '1', 'A001', '52.78'), ('2', '2', '', '76.3');
I searched it again with this code
Code:
SELECT C.Firstname, C.Lastname, O.CourseID, O.StudentID FROM students AS C, grades AS O;
and I got some repeated results for the code above as shown in the screenshot below.
Why do I get repeated results for this search? How do I make my task to work? I will apreciate further help please.
Menre
Re: Making links with PHP
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.
Re: Making links with PHP
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.
Quote:
...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.
Thanks,
Menre
Re: Making links with PHP
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.
