SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    Non-Member happylilcupcake's Avatar
    Join Date
    May 2011
    Location
    UK
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Working with sessions and trying to join tables

    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.

    PHP Code:
    <!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 Code:
    <?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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    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. #3
    Non-Member happylilcupcake's Avatar
    Join Date
    May 2011
    Location
    UK
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by happylilcupcake View Post
    PHP Code:
    $sql "SELECT author_id, user_id FROM answers, user WHERE
        user_id = '"
    $userUsername."'"
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member happylilcupcake's Avatar
    Join Date
    May 2011
    Location
    UK
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by happylilcupcake View Post
    How would you suggest I fix it? How could I make it more efficient?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    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
    PHP Code:
    $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? :
    PHP Code:
    $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. #8
    Non-Member happylilcupcake's Avatar
    Join Date
    May 2011
    Location
    UK
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by happylilcupcake View Post
    If they match, then the information contained in the rows where they match will be displayed.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Non-Member happylilcupcake's Avatar
    Join Date
    May 2011
    Location
    UK
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you'll need to work on improving your sql skills a bit,
    I think we already established that!

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

    Thanks anyway.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •