Help in Getting Data back from Database

[dmhall0]

I have a profile page where the user answers a list of about 20 questions.  These questions are then put into a mysql table with username, questionid, and answer.  I can store the answers to the table, but I cannot figure out how to get them back to view and edit.

My form is built from basic HTML.  I know how to pull answers from a db table with only 1 row of results, where each field is a different question, but this one is different, as it will pull 20 rows, and each row is for a different question.

Here is how I populate the questions and then fill in the answers.

// If this user has never set their profile, insert empty questions into the database
  $query1 = "SELECT * FROM u_profile WHERE username = '" . $_SESSION['username'] . "'";
  $data = mysqli_query($dbc, $query1);
  if (mysqli_num_rows($data) == 0) {
    // First grab the list of questionids
    $query2 = "SELECT questionid FROM questions ORDER BY q_order";
    $data = mysqli_query($dbc, $query2);
    $questionids = array();
    while ($row = mysqli_fetch_array($data)) {
      array_push($questionids, $row['questionid']);
    }

    // Insert empty question rows into the u_profile table, one per question
    foreach ($questionids as $questionid) {
      $query3 = "INSERT INTO u_profile (username, questionid) VALUES ('" . $_SESSION['username']. "', '$questionid')";
      mysqli_query($dbc, $query3);
    }
  }

  // If the questionnaire form has been submitted, write the responses to the database
  if (isset($_POST['submit'])) {
    // Write the questionnaire response rows to the response table
    foreach ($_POST as $answer_id => $answer) {
      $query4 = "UPDATE u_profile SET answer = '$answer' WHERE username = '" . $_SESSION['username'] . "' AND questionid = '$answer_id'";
      $uprofile_set = "CALL uprofile_set('" . $_SESSION['username'] . "')";
      mysqli_query($dbc, $query4) or die( "Connection Error1" . mysqli_error($dbc) ) ;
      mysqli_query($dbc, $uprofile_set) or die( "Connection Error2" . mysqli_error($dbc) ) ;
    }
      $races = "SELECT * FROM u_raceschedule WHERE username = '" . $_SESSION['username'] . "'";
      $data = mysqli_query($dbc, $races);
      if (mysqli_num_rows($data) > 0) {
      	 set_time_limit(30);
    	   $buildplan = "CALL tplan('" . $_SESSION['username'] . "')";
  	     mysqli_query($dbc,$buildplan) or die("Connection Error2" . mysqli_error($dbc) ) ;

 

Would LOVE any help.  I am really new to this whole coding thing.

[SergeiSS]

I think you would better forget your code and try to understand how to do it. Then you will create new tables and write new code.

 

Here you need 2 tables. One (1) is for relation between users and a set of questions and other (2) for answers. And, maybe, 3-d table for questions and correct answers. And or course you'll need a separate table (4) for users.

 

In the table 1 you have to create a unique id for a set of questions. Here you'll write userID, create a unique id for this set of questions (it will be used in the second table). In the table 2 you have to write every answer in a separate row, just 2 columns are enough (id of a set of questions from table 1 and answer). Or you may add 3-d column here in order to show the correct answer... It's up to you.

 

I've described it in short. And I hope you understand main idea.

[dmhall0]

Hi SergeiSS

Thanks for the reply.

 

I have 2 tables already.  Table 1 has questionid and question.  Table 2 has questionid and answer.  The answers are not static, so creating a table with all possible answers as you suggest is impossible.

 

I guess what I am looking for is some php code that does 2 things:

1.  extracts the "questionid" and "answer" fields in an array with a row for each questionid/answer combination (easy, I can do this part)

2.  then sets the value of a textbox equal to the answer that matches a specific questionid (almost something like value = answer where questionid = 'Q15')

[ddubs]

So if you have a table questions with fields (question_id, question) and another table answers with (answer_id, answer, question_id, user_id). You would do:

 

SELECT question_id.questions, question.questions, answer.answers 
FROM questions, answers
WHERE user_id.answers = $user_id
AND question_id.answers = question_id.questions
ORDER BY question_id.questions

 

Then loop over the results you get back from the DB:

for($i=0;$i<count($results);$i++) {
  echo "<span>{$results['question']}:</span><input type=\"text\" value=\"{$results['answer']}\" name=\"answer\"></input>\n";
}

 

SQL has its own built in logic. Utilizing it is waaaay more efficient that trying to keep the logic in PHP.

[dmhall0]

Interesting...

And if a new user is on the profile page that does not yet have data in the db how with this work?

Will it fail or still build the form, but values will be blank?

 

Thanks for the help!  This is a pretty cool idea.

[dmhall0]

I should also add that some of the questions do have drop-down selection.

So how can I take the above code suggestion and make that work across all options?

This seems complicated to me, but what do I know! ha

[SergeiSS]

It seems that you have to read this http://en.wikipedia.org/wiki/Database_normalization

[dmhall0]

My tables are pretty well normalized.  I have a question table, and answer table, and a question/answer table for those questions that have a drop down.  For those questions that do not and can be anything (like someone's name), I don't of course.

So for the questions that have a dropdown, how do I retrieve the user's answer; and for the questions totally dynamic, how do I make that work.

Thanks for the help!

[dmhall0]

Hi ddubs

 

The loop you suggested doesn't work.  It only returns the first row of results a bunch of times.

Suggestions on how to fix?

[ddubs]

I'd need to see how you implemented it into your code, can you post some including some of the DB query/fetching.

[dmhall0]

Here is the code:

 

$profile = "SELECT questions.questionid, questions.question, u_profile.answer, questions.q_order " .
                "FROM questions, u_profile WHERE u_profile.username = '" . $_SESSION['username'] . "'" .
                "AND questions.questionid = u_profile.questionid AND questions.q_order IS NOT NULL " .
                "ORDER BY questions.q_order ASC";
  $data = mysqli_query($dbc, $profile) or die("Connection Error3" . mysqli_error($dbc) ) ;
  $results = mysqli_fetch_assoc($data);
  
  for($i=0; $i < count($results); $i++) {
  echo "<span>{$results['question']}</span><input type=\"text\" value=\"{$results['answer']}\" name=\"answer\"></input><br />";
}

 

I wasn't sure if using mysqli_fetch_assoc was correct as that part wasn't provided.

[ddubs]

Here is the code:

 

<?php
$profile = "SELECT questions.questionid, questions.question, u_profile.answer, questions.q_order " .
                "FROM questions, u_profile WHERE u_profile.username = '" . $_SESSION['username'] . "'" .
                "AND questions.questionid = u_profile.questionid AND questions.q_order IS NOT NULL " .
                "ORDER BY questions.q_order ASC";
  $data = mysqli_query($dbc, $profile) or die("Connection Error3" . mysqli_error($dbc) ) ;
  $results = mysqli_fetch_assoc($data);
  
  for($i=0; $i < count($results); $i++) {
  echo "<span>{$results['question']}</span><input type=\"text\" value=\"{$results['answer']}\" name=\"answer\"></input><br />";
}

 

I wasn't sure if using mysqli_fetch_assoc was correct as that part wasn't provided.

 

You need to loop through your DB results:

 

<?php
$profile = "SELECT questions.questionid, questions.question, u_profile.answer, questions.q_order " .
                "FROM questions, u_profile WHERE u_profile.username = '" . $_SESSION['username'] . "'" .
                "AND questions.questionid = u_profile.questionid AND questions.q_order IS NOT NULL " .
                "ORDER BY questions.q_order ASC";
  $data = mysqli_query($dbc, $profile) or die("Connection Error3" . mysqli_error($dbc) ) ;
// like this
while($row = mysql_fetch_assoc($data)) {
  $results[] = $row;
}

  for($i=0; $i < count($results); $i++) {
  echo "<span>{$results['question']}</span><input type=\"text\" value=\"{$results['answer']}\" name=\"answer\"></input><br />";
}

// OR... to simplify, change the loop to:

while($results = mysql_fetch_assoc($data)) {
  echo "<span>{$results['question']}</span><input type=\"text\" value=\"{$results['answer']}\" name=\"answer\"></input><br />";
}
?>

mysql_fetch_assoc only returns one row at a time from the query. Look into using a DB library like MDB2 for more robust DB handling. (things like fetch_all which returns all results, etc...)