Joins and Tables

frankobi

Hi friends,

I need some help with joins. I am trying to create a system to allow my users to obtain their records from several tables. Each user is required to log in with a studentid and a password to obtain his/her record. But I get an error message: “Sorry, could not log you in. Wrong login information.”

I am using two files and they are included below. Thank you in advance.

--Frank

============================
**login3.php***

<?php
session_start();
// dBase file
include "dbConfig2.inc";

$rs = mysql_select_db("lhsnycne_schedule",$dbh) 
	or die("error connecting to the database");


	if ($_GET["op"] == "login")
		{
		if (!$_POST["studentid"] || !$_POST["password"])
			{
			die("You need to supply a username and password.");
			}

		// Create query
		//$q = "SELECT * FROM `student` "

$q="SELECT student.studentid, Teaching.Classcode, Teacher.Teacherlname, Class.Period, Class.Room, Class.Days
FROM
Teacher
INNER JOIN
((Student INNER JOIN ((Classname INNER JOIN Class ON Classname.ClassID = Class.ClassID)
INNER JOIN
SSchedule ON Class.Classcode = SSchedule.Classcode) ON Student.Studentid = SSchedule.Studentid)
INNER JOIN
Teaching ON Class.Classcode = Teaching.Classcode) ON Teacher.Teacherid = Teaching.TeacherID
WHERE
(student.Studentid = '::student.Studentid::')"

			."WHERE `studentid`='".$_POST["studentid"]."' "
			."AND `password`=PASSWORD('".$_POST["password"]."') "
			."LIMIT 1";
		// Run query
		$r = mysql_query($q);

		if ( $obj = @mysql_fetch_object($r) )
			{
			// Login O.K., create session variables
			$_SESSION["valid_lname"] = $obj->lname;
			$_SESSION["valid_fname"] = $obj->fname;
			$_SESSION["valid_id"] = $_POST["studentid"];
			$_SESSION["valid_time"] = time();
			$_SESSION["valid_osis"] = $obj->osis;

			// Redirect to member page
			Header("Location: members3.php");
			}
		else
			{
			// Login not successful
			die("Sorry, could not log you in. Wrong login information.");
			}
	}
	else
		{
		echo "<form action=\"?op=login\" method=\"POST\">";
		echo "Studentid: <input name=\"studentid\" size=\"15\"><br />";
		echo "Password: <input type=\"password\" name=\"password\" size=\"8\"><br />";
		echo "<input type=\"submit\" value=\"Login\">";
		echo "</form>";
		}
	?>

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

****members3.php*****

<?php
session_start();

if (!$_SESSION["valid_id"])
{
// User not logged in, redirect to login page
Header("Location: login3.php");
}

// Display Member-only stuff
// ...
// ...
// ...

// Display Member information
echo "User ID: " . $SESSION["valid_id"];
echo "First Name: " . $SESSION["valid_fname"];
echo "Full Name: " . $SESSION["valid_lname"]. ", " . $SESSION["valid_fname"];
echo "Logged in: " . date("m/d/Y", $SESSION["valid_time"]);
echo "osis: " . $SESSION["valid_osis"];

// Display logout link
echo "<a href=\"logout3.php\">Click here to logout!</a>";
?>

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

nemonoman

remember that INNER JOIN must find correct data in each table.

If any part of the join fails to find the requested element, the query returns no rows.

Example:
SELECT classroster.studentid, student.name
FROM student, classroster
WHERE classroster.studentid=student.id
AND classroster.id='Bio 101'

Finds all the rows where classroster.id='Bio101'

THEN finds all the rows where 'classroster.studentid' has a matching record in the student table.

No null values are returned. If there's a studentid in the classroster table, but no = student.id, no rows are returned.

So you have to check 2 things:
your SQL logic
your DATA

You need to create a test case where you prove to yourself that correct data exists in each table based on your query expectations.

Then you need to test the query against that set of data.

With so many joins involved, it's very likely that incomplete or mismatched data is your problem, not the query only.