webguync Posted April 9, 2009 Share Posted April 9, 2009 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? Quote Link to comment Share on other sites More sharing options...
webguync Posted April 9, 2009 Author Share Posted April 9, 2009 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'; Quote Link to comment Share on other sites More sharing options...
schilly Posted April 9, 2009 Share Posted April 9, 2009 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. Quote Link to comment Share on other sites More sharing options...
webguync Posted April 9, 2009 Author Share Posted April 9, 2009 yes, have name, email address and PWID stored into a MySQL db. Quote Link to comment Share on other sites More sharing options...
webguync Posted April 9, 2009 Author Share Posted April 9, 2009 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']; } } /* Quote Link to comment Share on other sites More sharing options...
schilly Posted April 9, 2009 Share Posted April 9, 2009 so just add a where into your query that matches the username from the form. Quote Link to comment Share on other sites More sharing options...
webguync Posted April 9, 2009 Author Share Posted April 9, 2009 $username = $_POST['username']; how would I capture that in my WHERE clause Quote Link to comment Share on other sites More sharing options...
schilly Posted April 10, 2009 Share Posted April 10, 2009 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"; ?> Quote Link to comment Share on other sites More sharing options...
webguync Posted April 10, 2009 Author Share Posted April 10, 2009 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; } }*/ } ?> Quote Link to comment Share on other sites More sharing options...
xtopolis Posted April 10, 2009 Share Posted April 10, 2009 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... Quote Link to comment Share on other sites More sharing options...
webguync Posted April 10, 2009 Author Share Posted April 10, 2009 How would I create an Alias, instead of using the full table name? Quote Link to comment Share on other sites More sharing options...
xtopolis Posted April 10, 2009 Share Posted April 10, 2009 .... 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 Quote Link to comment Share on other sites More sharing options...
webguync Posted April 11, 2009 Author Share Posted April 11, 2009 gotcha, thx. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.