Working with sessions and trying to join tables

PHP
1

I’m creating a website that allows users to log on, answer some questions and submit answers to a database. The user can log in at a later date and review the answers they have submitted.

I have organised the information into two tables:

  • user - contains the user login information and a unique user_id number.
  • answers - contains the submitted answers and an author_id column, which will be equal to user_id number.

In theory, when the answers are displayed on ‘answers.php’, all rows from answers TABLE where the user_id is equal to author_id will be displayed.

I’m having two issues.

Issue the first:
When submitted the answers, the user_id FROM TABLE user is not being recorded in the author_id of TABLE answers. I’m getting 0 in that field.

Here is the code I’m using for the ‘submit_answers.php’ page I’ve attached to the form.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<title>Answers Submitted</title>
</head>

<body>
<?php
include('dbcn.php');
session_start();

// uses session to recover user_id and store in variable for assignment to author_id later.
$userUsername = $_SESSION['loggedInUser'];
$authorID = $_SESSION['user_id'];
$sql = "SELECT user_id FROM user WHERE user_username = '" . $userUsername . "'";
$result = mysql_query($sql $cn) or
	die(mysql_error($cn));



// Whatever was entered onto the form in questions.php is linked to the variables listed below. 
$q1Response = $_POST['q1Response'];
$q2Response = $_POST['q2Response'];
$q3Response = $_POST['q3Response'];


// Protection
$q1Response = mysql_real_escape_string(stripslashes($q1Response));
$q2Response = mysql_real_escape_string(stripslashes($q2Response));
$q3Response = mysql_real_escape_string(stripslashes($q3Response));

// Adds answers to answers db and places user_id FROM users into author_id.
$sql = "INSERT INTO answers
		(answer_q1,
		answer_q2,
		answer_q3,
		author_id)
		VALUES
		('" . $q1Response . "',
		'" . $q2Response . "',
		'" . $q3Response . "',
		'" . $authorID . "')";
$result = mysql_query($sql, $cn) or
	die(mysql_error($cn));
			
	echo "<p><strong>Your answers have been submitted. Please return to the <a href='profile.php'>profile area</a>.</strong></p>";
?>
</body>
</html>

Issue the second:
When retrieving the answers and displaying them on 'answers.php, I keep getting syntax error but I cannot see where the issue is. (Perhaps I’ve been staring at this code for too long.) It might be more likely related to issue the first, but I thought I’d ask if I’d missed something just in case.

Here’s the code I’ve used for ‘answers.php’.

<?php
include('dbcn.php');
session_start();

$userUsername = $_SESSION['loggedInUser'];

// Select all answers linked to logged in user.
$sql = "SELECT author_id, user_id FROM answers, user WHERE
	user_id = '". $userUsername."'";
$result = mysql_query($sql, $cn) or
	die(mysql_error($cn));
$row = mysql_fetch_assoc($result);

// organise information into array
$q1Response= $row['answer_q1'];
$q2Response= $row['answer_q2'];
$q3Response= $row['answer_q3'];
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title><?php echo $userUsername; ?>'s Answers</title>
</head>
<body>
	<h1>Answer Page</h1>
		<h2>Welcome <?php echo $userUsername; ?></h2>
			<h3><a href="logout.php">[LOGOUT]</a></h3>
			<br />
			<br />
			<h3>Your responses to the following questions:</h3>
			<p>Q1: What is your name?</p>
            <p><?php echo $q1Response ?>
            <p>Q1: What is your quest?</p>
            <p><?php echo $q2Response ?>
            <p>Q1: What is your favourite colour?</p>
            <p><?php echo $q3Response ?>
        	<p>Click <a href="profile.php">here</a> to go to the profile page</p>
</body>
</html>

Any advice, tips or pointing in the right direction would be greatly appreciated. I’m wondering if I should have a third table in there to help with the linking of the two tables, which is my next attempt of fixing this.

2

  1. Put session_start on top of both script (even before the doctype line and the include).
    And do a print_r of $_SESSION to see what your session contains.

  2. Can you post the exact error you’re getting?

3

Syntax error is occurring on the ‘submit_answers.php’, not ‘answers.php’. Oops! Probably linked to the other problem I’m having.

This is the error anyway.

Parse error: syntax error, unexpected T_VARIABLE in /home/gemzcooke/gemzcooke.com/php/submit_answers.php on line 16

I’ll give the session_start() thing a try when I look over the script again later this afternoon. Will report back if it worked.

Thanks!

4

that’s a bad query, but it has no syntax error, it will actually run

unfortunately, it will return all the authors in the answers table, regardless of whether they match the specified user_id

5

How would you suggest I fix it? How could I make it more efficient?

I’ll be honest, I haven’t used PHP/MySQL since university and that was quite some time ago! Any tips are welcome.

6

i’m not sure what that query is supposed to do

why would you want to return the author_id for a given user_id?

i think you need to rethink what the query is supposed to accomplish

7

When you join two tables you have to specify the join criteria, otherwise the result will be a cross join (each row from the first table linked to each row of the second).
So it would become


$sql = "
  SELECT 
      author_id
    , user_id 
  FROM answers
  INNER JOIN user
  ON answers.author_id = user.user_id
  WHERE user_id = '$userUsername'
";

But this query still makes little sense. It will give you the same author_id and user_id (which are the same) a number of times. So all it does is tell you is how many answers that user has.
But since you want to display the answer data ($row[‘answer_q1’] etc), you’ll have to specify the column names you want to extract.
And you are confronting the user name with user_id. Shouldn’t that be user_username? :


$sql = "  
  SELECT 
      answers.answer_q1
    , answers.answer_q2
    , answers.answer_q3
  FROM answers
  INNER JOIN user
  ON answers.author_id = user.user_id
  WHERE user_username = '$userUsername'
";
8

The query is meant to look at the user and answers tables and compare user_id and author_id. If they match, then the information contained in the rows where they match will be displayed.

9

you’ll need to work on improving your sql skills a bit, because what you wrote doesn’t come close – it’s missing the relevant columns in the SELECT clause, and it doesn’t join the tables properly in the FROM clause

10

I think we already established that!

Right, time to dig out the old text books and try this again.

Thanks anyway.