Need help with Q&A Form

doubledee · April 17, 2012

I have a "Thoughts" form where a User can response to 10 Questions, all of which are optional.

I am trying to build a Q&A Form dynamically, instead of hard-coding in the Questions.

Here are my tables...

member

- id

- first_name

answer

- id

- member_id

- question_id

- answer_text

question

- id

- question_no

- question_text

I was able to write a query that lists all of the Questions, and then code that stores things in an array.

I was able to write a query that lists all of the Answers, and then code that stores things in an array.

My problem seems to be connecting the Questions and Answers together?! :confused:

Everytime the form loads, I need to display all 10 Questions (labels) and the (Answer) Input field below, however, I will not be displaying all 10 Answers, because 1.) The User might not have completed the Form, 2.) The User may have opted to only Answer some Questions.

I tried a LEFT join, but since the Q&A ties to a Member's Profile, and the "member_id" is in the Answer table, if there are no Answers yet, then things fail.

It's been a long week, and I've kinda reached a point of frustration and confusion to where this problem is no longer clear, let alone the solution.

I could use some help here...

Thanks,

Debbie

litebearer · April 17, 2012

Have you considered using a multidimensional array?

requinix · April 17, 2012

Just a question about the SQL, or is there a problem with the HTML form(s) too?

Exactly what you do depends how you handle blank answers. If you store them then just use a normal JOIN since the data does necessarily exist.

SELECT q.question_text, a.answer_text FROM question q JOIN answer a ON q.id = a.question_id...

If you don't store the answer at all then you'd need a LEFT JOIN because the data might not exist and you still want to know the question.

SELECT q.question_text, a.answer_text FROM question q LEFT JOIN answer a ON q.id = a.question_id...

(Note how they're identical except for the added "LEFT")

doubledee · April 17, 2012

Just a question about the SQL, or is there a problem with the HTML form(s) too?

I am trying to figure out how to dynamically display the Questions and any existing Answers when the script is loaded.

Exactly what you do depends how you handle blank answers. If you store them then just use a normal JOIN since the data does necessarily exist.
SELECT q.question_text, a.answer_text FROM question q JOIN answer a ON q.id = a.question_id...
If you don't store the answer at all then you'd need a LEFT JOIN because the data might not exist and you still want to know the question.
SELECT q.question_text, a.answer_text FROM question q LEFT JOIN answer a ON q.id = a.question_id...
(Note how they're identical except for the added "LEFT")

When a User goes into his/her Profile and loads the Q&A page, I always need to display 10 Questions. That is easy.

But I also need to display 10 Input Boxes. And I need to load an Answers they already answered earlier.

Your LEFT join won't work because there is an additional

WHERE a.member_id=?

See what I mean?

Debbie

doubledee · April 17, 2012

Here is some of my code...

// *************************************************************
// HANDLE FORM.								 *
// *************************************************************
if ($_SERVER['REQUEST_METHOD']=='POST'){
	// Form was Submitted (Post).

}else{
	// Form was not Submitted (Get).
	// Drop through to display Form.


	// ********************
	// Build Questions.		*
	// ********************

	// Build query.
	$q5 = "SELECT id, question_no, question_text
					FROM bio_question
					ORDER BY question_no";

	// Prepare statement.
	$stmt5 = mysqli_prepare($dbc, $q5);

	// Execute query.
	mysqli_stmt_execute($stmt5);

	// Store results.
	mysqli_stmt_store_result($stmt5);

	// Check # of Records Returned.
	if (mysqli_stmt_num_rows($stmt5) > 0){
		// Questions Found.

		// Bind result-set to variable.
		mysqli_stmt_bind_result($stmt5, $questionID, $questionNo, $questionText);

		// Populate PHP array with Questions.
		$x=1;
		while (mysqli_stmt_fetch($stmt5)){
			$questionsArray[$x] = array('questionID' => $questionID,
								'questionText' => $questionText);
			$x=$x+1;
		}

		echo '<pre>';
		print_r($questionsArray);
		echo '</pre>';


		// Close prepared statement.
		mysqli_stmt_close($stmt5);

	}else{
		// Questions Not Found.
		$_SESSION['resultsCode'] = 'QUESTIONS_NOT_FOUND_2144';

		// Close prepared statement.
		mysqli_stmt_close($stmt5);

		// Set Error Source.
		$_SESSION['errorPage'] = $_SERVER['SCRIPT_NAME'];

		// Redirect to Display Outcome.
		header("Location: " . BASE_URL . "/members/results.php");

		// End script.
		exit();
	}//End of BUILD QUESTIONS

That yields this...

Array
(
    [1] => Array
        (
            [questionID] => 2
            [questionText] => Why did you decide to start your own business?
        )

    [2] => Array
        (
            [questionID] => 4
            [questionText] => What advice would you share with others on what NOT to do?
        )

    [3] => Array
        (
            [questionID] => 3
            [questionText] => What advice would you share with others on what TO do?
        )

    [4] => Array
        (
            [questionID] => 7
            [questionText] => How do you compete against large corporations?
        )

    [5] => Array
        (
            [questionID] => 5
            [questionText] => What Business Structure do you have?
        )

)

I have a lot more code involving a query that gets the Answers, puts them into a second array, and then of course all of my HTML/PHP to display things, but maybe you guys can comment on what I have so far...

Thanks,

Debbie

xyph · April 17, 2012

When a User goes into his/her Profile and loads the Q&A page, I always need to display 10 Questions. That is easy.

[1]But I also need to display 10 Input Boxes. And I need to load an Answers they already answered earlier.

[2]Your LEFT join won't work because there is an additional
WHERE a.member_id=?

1. You can do this using an echo statement. When you echo the questions, simply echo an input box afterwards, within the loop.

2. MySQL is capable of using multiple conditions when JOINing

You should follow requinix's advice. If you want to grab all of the questions, along with any answers a user may or may not have entered, use a LEFT JOIN.

Here's a sample query, with results from the MySQL console.

mysql> SELECT q.question, a.answer
    -> FROM questions as q
    ->    LEFT JOIN answers as a
    ->    ON q.id = a.question_id AND a.user_id = 2;
+---------------------------------------------+--------------------------+
| question                                    | answer                   |
+---------------------------------------------+--------------------------+
| Does PHP rock?                              | NULL                     |
| Is MySQL super handy?                       | Wow, it sure is!         |
| What is the best community for PHP help?    | I love PHPFreaks!        |
| Does requinix know what he's talking about? | He's a guru for a reason |
+---------------------------------------------+--------------------------+
4 rows in set (0.00 sec)

And here's the tables/data I've been using

--
-- Table structure for table `answers`
--

CREATE TABLE IF NOT EXISTS `answers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `question_id` int(10) unsigned NOT NULL,
  `answer` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `answers`
--

INSERT INTO `answers` (`id`, `user_id`, `question_id`, `answer`) VALUES
(1, 1, 1, 'Sure Does'),
(2, 1, 3, 'PHPFreaks, of course'),
(3, 2, 2, 'Wow, it sure is!'),
(4, 2, 4, 'He''s a guru for a reason'),
(5, 2, 3, 'I love PHPFreaks!');

-- --------------------------------------------------------

--
-- Table structure for table `questions`
--

CREATE TABLE IF NOT EXISTS `questions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `question` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `questions`
--

INSERT INTO `questions` (`id`, `question`) VALUES
(1, 'Does PHP rock?'),
(2, 'Is MySQL super handy?'),
(3, 'What is the best community for PHP help?'),
(4, 'Does requinix know what he''s talking about?');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `name`) VALUES
(1, 'matt'),
(2, 'debbie');

doubledee · April 17, 2012

1. You can do this using an echo statement. When you echo the questions, simply echo an input box afterwards, within the loop.

I never finished posting all of my code, because I was hoping for comments on what I had.

Anyway, what I have done so far, is to create a Questions Query and then populate a Questions Array.

And then I have an Answers Query and then populate an Answers Array.

And my problem was "wiring" these together.

But based on your code below, it sounds like you are indirectly telling me to do everything in ONE PASS, right??

(Do you want to see what I have and where I am stuck??)

2. MySQL is capable of using multiple conditions when JOINing

You should follow requinix's advice. If you want to grab all of the questions, along with any answers a user may or may not have entered, use a LEFT JOIN.

Here's a sample query, with results from the MySQL console.

mysql> SELECT q.question, a.answer
    -> FROM questions as q
    ->    LEFT JOIN answers as a
    ->    ON q.id = a.question_id AND a.user_id = 2;
+---------------------------------------------+--------------------------+
| question                                    | answer                   |
+---------------------------------------------+--------------------------+
| Does PHP rock?                              | NULL                     |
| Is MySQL super handy?                       | Wow, it sure is!         |
| What is the best community for PHP help?    | I love PHPFreaks!        |
| Does requinix know what he's talking about? | He's a guru for a reason |
+---------------------------------------------+--------------------------+
4 rows in set (0.00 sec)

I bet your query will fail if there are NO Answers... (Like when a new User creates a Profile that has no Answers in it initially.)

Debbie

xyph · April 17, 2012

You don't have to bet. I've given you what you need to test your theory completely.

Note: There is no user with `id` = 3

mysql> SELECT q.question, a.answer
    -> FROM questions as q
    ->    LEFT JOIN answers as a
    ->    ON q.id = a.question_id AND a.user_id = 3;
+---------------------------------------------+--------+
| question                                    | answer |
+---------------------------------------------+--------+
| Does PHP rock?                              | NULL   |
| Is MySQL super handy?                       | NULL   |
| What is the best community for PHP help?    | NULL   |
| Does requinix know what he's talking about? | NULL   |
+---------------------------------------------+--------+
4 rows in set (0.00 sec)

I'm not interested in how you're doing it, to be honest. I'm not trying to be mean. I can assure you, the way you're doing it isn't as clean or efficient as the way that's been presented here.

If you want us to solve your _specific_ issue, you must isolate it to a _specific_ issue. If you say "This is what I want to accomplish, overall, and this is how I'm approaching it," you will get responses like mine and requinix's. They just might lead you in a different direction than you've been headed.

If you want to ask a generic question and have the answer fit into your specific code block, you probably want to hire a developer, or a teacher.

doubledee · April 17, 2012

xyph,

How can the bolded code work if there are NO Answer records...

SELECT q.question, a.answer

-> FROM questions as q

-> LEFT JOIN answers as a

-> ON q.id = a.question_id AND a.user_id = 2;

Debbie

xyph · April 18, 2012

It's how a LEFT JOIN works in MySQL.

It selects each row on the 'left side', in this case, the table `questions`

For each selected row, it then searches for rows on the 'right side', in this case, `answers`, that also matches the ON clauses.

If none are found, any requested columns from the 'right' table are left NULL (a.answer)

These kinda of answers can be found through Google quite easily. If it's still messing you up, Google up more information on "MySQL Joins"

If you'd like to discuss how MySQL's inner-workings operate, you might want to check the MySQL section of this forum. I assure you, though, it's very very complex. People make careers out of database optimization.

doubledee · April 18, 2012

It's how a LEFT JOIN works in MySQL.

It selects each row on the 'left side', in this case, the table `questions`

I understand that.

For each selected row, it then searches for rows on the 'right side', in this case, `answers`, that also matches the ON clauses.
If none are found, any requested columns from the 'right' table are left NULL (a.answer)

I understand that.

But that doesn't answer my last question...

I asked...

How can the bolded code work if there are NO Answer records...

SELECT q.question, a.answer

-> FROM questions as q

-> LEFT JOIN answers as a

-> ON q.id = a.question_id AND a.user_id = 2;

I get that a LEFT join - in this case - will always display my 10 Questions, even if there aren't any Answers on the right.

But since there are NO Answer records, how can it meet the condition AND a.user_id = 2;???

In my mind, without an Answer records, that condition should fail...

Debbie

xyph · April 18, 2012

If none are found, any requested columns from the 'right' table are left NULL (a.answer)

It does the check row-by-row, from what I understand. The row on the left will be returned, no matter what the conditions of the JOIN are.

I've tried explaining why in English. All I can say is it works, and if you want to continue to believe the contrary through the proof above that it actually does, you're welcome to.

doubledee · April 18, 2012

If none are found, any requested columns from the 'right' table are left NULL (a.answer)

It does the check row-by-row, from what I understand. The row on the left will be returned, no matter what the conditions of the JOIN are.

I've tried explaining why in English. All I can say is it works, and if you want to continue to believe the contrary through the proof above that it actually does, you're welcome to.

I was doing something similar a few days ago with a LEFT join, and it failed them when I had the reference to

a.member=?

so that is why I am not understanding things.

Unfortunately that was many versions ago, so I'm not sure how to recreate what LEFT join code that I had which was failing when I added the extra conditional of needing a specific member...

I was hoping you or someone might be able to shed light on that...

Debbie

xyph · April 18, 2012

You probably used the WHERE clause, while mine uses multiple ON clauses.

As an extra, you'll probably get a good speed boost if you set answer.user_id and answer.question_id as indexes.

doubledee · April 18, 2012

You probably used the WHERE clause, while mine uses multiple ON clauses.

Bingo!! That is what I did!

So, can you explain why my WHERE didn't return any records but your ON does?

As an extra, you'll probably get a good speed boost if you set answer.user_id and answer.question_id as indexes.

Okay, good points.

Debbie

xyph · April 18, 2012

Because the WHERE clause won't allow rows that don't match to be returned.

In this case, the ON clause doesn't determine which rows are actually returned, but instead which data, if any, is returned along with it.

doubledee · April 18, 2012

If none are found, any requested columns from the 'right' table are left NULL (a.answer)

It does the check row-by-row, from what I understand. The row on the left will be returned, no matter what the conditions of the JOIN are.

So revisiting this, in this code...

SELECT q.question, a.answer
        -> FROM questions as q
        ->    LEFT JOIN answers as a
        ->    ON q.id = a.question_id AND a.user_id = 2;

IF q.id=a.question_id THEN an Answer would be displayed IF there is one user_id=2.

Any other Users would be left out, right?

So the ON doesn't stop a Question from being displayed ever - whereas a WHERE would.

And if the two keys match in the ON, then an Answer will be displayed if it also meets the AND requirements.

But in all cases, a Question is displayed assuming there is one to display.

Does that sound right?

Thanks,

Debbie

xyph · April 18, 2012

If you put WHERE 1 = 1, it would work as expected. It's not the WHERE clause that makes it not work, it's what you put there.

You're probably going to use the WHERE clause later, when you have more questions, and want to restrict which 10 you select.

teynon · April 18, 2012

doubledee,

I don't know if this will help you or not, but:

LEFT JOIN: Includes all records from the left table and essentially appends its results to that record.

NATURAL or INNER JOIN: would require that the data match all values.

You can think of a left join as a supportive friend tagging along. He / she doesn't block you, but adds input when necessary.

doubledee · April 18, 2012

doubledee,

I don't know if this will help you or not, but:

LEFT JOIN: Includes all records from the left table and essentially appends its results to that record.

NATURAL or INNER JOIN: would require that the data match all values.

You can think of a left join as a supportive friend tagging along. He / she doesn't block you, but adds input when necessary.

I think I get that much, but what about how I described things in Reply #16?

Debbie

teynon · April 18, 2012

If none are found, any requested columns from the 'right' table are left NULL (a.answer)

It does the check row-by-row, from what I understand. The row on the left will be returned, no matter what the conditions of the JOIN are.

So revisiting this, in this code...
SELECT q.question, a.answer
        -> FROM questions as q
        ->    LEFT JOIN answers as a
        ->    ON q.id = a.question_id AND a.user_id = 2;
IF q.id=a.question_id THEN an Answer would be displayed IF there is one user_id=2.

Any other Users would be left out, right?

So the ON doesn't stop a Question from being displayed ever - whereas a WHERE would.

And if the two keys match in the ON, then an Answer will be displayed if it also meets the AND requirements.

But in all cases, a Question is displayed assuming there is one to display.

Does that sound right?

Thanks,

Debbie

This query is essentially this:

SELECT q.question, null  FROM questions as q

+

SELECT a.answer FROM answers as a WHERE a.user_id = 2

the answers table is being included optionally. Because the answers are optional. (They might not have been answered.)

We use the ON clause because it applies the user_id to the specific table select. If we use the WHERE, it applies itself to the overall selection. So to straight out answer your question:

Yes, the other users would be left out, unless you specifically select them to be included. Yes, what you say there is correct.

Sign In

Need help with Q&A Form

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information