Making links with PHP

**kows** · Jun 6th, 2010, 05:53 PM

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.

**menre** · Jun 29th, 2010, 05:58 PM

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

Thread: Making links with PHP

Thread Tools

Display

Hybrid View

Re: Making links with PHP

Re: Making links with PHP

Posting Permissions