display results filtered by user ID

[webguync]

Hi,

 

I have an exam application where the participants take the application and then can go to a link to view their scores. Currently all of the scores are displayed, but I would want to alter that by creating a login screen and when they login, only they can only view their scores and not others. I think that would be the best way to approach this.

 

the code to display scores for everyone is basically taking the name from a MySQL table

$name[] = $row['name'];

and displaying all. How would I restrict what they see based on their unique login?

 

 

 

[webguync]

more info.

the SQL being used to display all of the results is :

 

$sql = 'SELECT name, total_questions, incorrect_resp, num_correct,  centacor_results.date
	FROM centacor_roster, centacor_log LEFT JOIN centacor_results USING (log_id)
	WHERE centacor_roster.user_id = centacor_log.user_id AND centacor_roster.user_id > 0
	ORDER BY name, date';

[schilly]

do you have some unique identifier for each person taking the test? do you collect any personal info or is it anonymous?

 

could always validate based on email, session id, random string, etc.

[webguync]

yes, have name, email address and PWID stored into a MySQL db.

[webguync]

Hi,

 

here is the code I am currently using to display the results. I have created a login form which captures the username which should always be unique. so, I just need to display the results pertinent to each unque username. Currently all of the results are displayed.

 

<?php
require_once('../protected/databaseClass.php');

$db = new Database('localhost','username','pw','DBName',0);

$sql = 'SELECT name, username,total_questions, incorrect_resp, num_correct,  centacor_results.date
	FROM centacor_roster, centacor_log LEFT JOIN _results USING (log_id)
	WHERE centacor_roster.user_id = centacor_log.user_id AND centacor_roster.user_id > 0
	ORDER BY name, date';

$report = $db->query($sql);

if ($report->get_rows()) {
//loop to create arrays for each column
while ($row = $report->fetch_assoc()) {
	if($row['num_correct']) {
		$name[] = $row['name'];
                        $username[] = $row['username'];
		$numCorr[] = $row['num_correct'];
		$pcnt[] = ($row['num_correct'])/($row['total_questions']);
		$incorr[] = $row['incorrect_resp'];

		$date[] = $row['date'];

		}
	}

/*

[schilly]

so just add a where into your query that matches the username from the form.

[webguync]

$username = $_POST['username'];

 

how would I capture that in my WHERE clause

[schilly]

something like this should work i think

<?php
$sql = "SELECT name, username,total_questions, incorrect_resp, num_correct,  centacor_results.date
	FROM centacor_roster, centacor_log LEFT JOIN _results USING (log_id)
	WHERE centacor_roster.user_id = centacor_log.user_id AND centacor_roster.user_id > 0 AND username='$username'
	ORDER BY name, date";
?>

[webguync]

that does something kinda weird. It says a table doesn't exist that I am not even accessing.

 

I think Having two SQL queries on a page is messing things up.

 

How can I simplify this?

 

<?php
session_start();
if(!isset($_SESSION['username'])){
    header("Location:ExamLogin.php");
    exit;
}

ini_set("display_errors","1");
ERROR_REPORTING(E_ALL);
$con = mysql_connect("localhost","username","pw");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("dbname", $con);




// Start a session. Session is explained below.
//session_start();

// Same checking stuff all over again.
if(isset($_POST['submit'])) {
if(empty($_POST['username']) || empty($_POST['pwid']) ) {
	echo "Sorry, you have to fill in both your name, username and password";
                exit;
}
// Create the variables again.

$username = $_POST['username'];
$pwid = $_POST['pwid'];
// Encrypt the password again with the md5 hash. 
// This way the password is now the same as the password inside the database.
$pwid = md5($pwid);

// Store the SQL query inside a variable. 
// ONLY the username you have filled in is retrieved from the database.
$query = "SELECT username,pwid
		  FROM	 centacor_roster
		  WHERE	 username='$username'";

$result = mysql_query($query);
if(!$result) { 
	// Gives an error if the username given does not exist.
	// or if something else is wrong.
	echo "The query failed " . mysql_error();
} else {
	// Now create an object from the data you've retrieved.
	$row = mysql_fetch_object($result);
	// You've now created an object containing the data.
	// You can call data by using -> after $row.
	// For example now the password is checked if they're equal.
	//if($row->pwid != $pwid) {
		//echo "I am sorry, but the passwords are not equal.";
                      //  exit;
	//}
	// By storing data inside the $_SESSION superglobal,
	// you stay logged in until you close your browser.
	$_SESSION['username'] = $username;
	$_SESSION['sid'] = session_id(); 
	// Make it more secure by storing the user's IP address.
	$_SESSION['ip'] = $_SERVER['REMOTE_ADDR'];
	// Now give the success message.
	// $_SESSION['username'] should print out your username.

echo "<h3>Welcome! You are now logged in " . $_SESSION['username'] . "</h3>";

}
}
?>
<?php
require_once('../protected/databaseClass.php');

$db = new Database('localhost','username',pw','dbname',0);


$sql = "SELECT name, username,total_questions, incorrect_resp, num_correct,  centacor_results.date
      FROM centacor_roster, centacor_log LEFT JOIN _results USING (log_id)
      WHERE centacor_roster.user_id = centacor_log.user_id AND centacor_roster.user_id > 0 AND username='$username'
      ORDER BY name, date";

$report = $db->query($sql);

if ($report->get_rows()) {
//loop to create arrays for each column
while ($row = $report->fetch_assoc()) {
	if($row['num_correct']) {
		$name[] = $row['name'];
                        $username[] = $row['username'];
		$numCorr[] = $row['num_correct'];
		$pcnt[] = ($row['num_correct'])/($row['total_questions']);
		$incorr[] = $row['incorrect_resp'];

		$date[] = $row['date'];

		}
	}

/*
//total elements in each array (currently same for all)
$numResults = count($name);
//counter for creating "scores" subarray
$counter = 0;

//this loop creates an array for each employee
for ($i=0, $k = 0; $k<$numResults; $k++) {
	$employeeResults[$i]['name'] = $empName[$k];
	$employeeResults[$i]['correct'] = $empID[$k];
	$employeeResults[$i]['percent'] = $terr[$k];
	$employeeResults[$i]['scores']['score'][$counter] = $score[$k];
	$employeeResults[$i]['scores']['assessor_id'][$counter++] = $assID[$k];
	//if the next employee is different, then increment the employee counter, and reset score counter
	if (($k<$numResults-1) && (($empID[$k+1] != $empID[$k]) || ($empID[$k]==NULL))) {
		$i++;
		$counter = 0;
		}
	}*/
}

?>

[xtopolis]

That query should have the proper aliases.  When referencing multiple tables, you have to prepend the columns with the table name or alias.  Fix my corrections accordingly.:

$sql = "SELECT cl.name, cl.username, cl.total_questions, cl.incorrect_resp, cl.num_correct,  cr.date
      FROM centacor_roster cr, centacor_log cl LEFT JOIN _results USING (log_id)
      WHERE cr.user_id = cl.user_id AND cr.user_id > 0 AND cl.username='$username'
      ORDER BY cl.name, cl.date";

I'm not sure what to do with the _results join as I don't know your table structures.  Your page seems confusing, and the query equally cryptic...

[webguync]

How would I create an Alias, instead of using the full table name?

[xtopolis]

.... im surprised you didn't deduce it from my example.

 

SELECT tableAlias.column FROM tableName tableAlias

 

So if you have a table named test, and you want to alias it as t, it would be:

SELECT t.column FROM test t

[webguync]

gotcha, thx.