Prepared Statement to place Multiple Rows into Variables

[DoubleDee]

In my User Profiles, I have 10 Questions that Members may answer.

I have decided to model this as a many-to-many relationship where "answer" is the intermediary table between "member" and "question".

For each Member Profile, I need to display the responses to each Question in their Profile, but if I run a query like this...

SELECT response
FROM answer
WHERE member_id = 1;

Then all I will get is a query-set of 10 records like this...

01 I started my own business because...
02 My advice to other entrepreneurs is to get a good lawyer!
:
:
10 I compete with large corporation by knowing my customers and giving exceptional service!

What I need - if possible - is to write a Prepared Statement that would take the 10 records returned - with just one Field - and store each respective record into a variable like $response1, $response2,... $response10 so that ONE QUERY can be used to populate my 10 Text Boxes on the "my_thoughts.php" page in their Profile.

(If I have to write 10 separate Prepared Statements - one for each Question - I will scream!!!)


Can this be done?

If so, how would I do it?

(I'm still not that strong with Prepared Statement, especially fancier things like this...)

Thanks,


Debbie

[Jake Arkinstall]

This isn't about prepared statements. It's a single query that returns ten answers and stores each of them into an array... or ten variables if you really want.

Depends on your DB but I'll assume PDO:

function getResponses(PDO $Database, $UserID){
    $ResponseStatement = $Database->prepare('SELECT response FROM answer WHERE member_id = :uid');
    $ResponseStatement->bindValue('uid', $UserID, PDO::PARAM_INT);
    $ResponseStatement->execute();
    $Responses = $ResponseStatement->fetchAll(PDO::FETCH_COLUMN, 0);
    if(empty($Responses)){
        return false;
    }else{
        return $Responses;
    }
} 

It's just as simple with MySQL:

function getResponses($UserID){
    $UserID = (int)$UserID;
    $ResponseQuery = mysql_query('SELECT response FROM answer WHERE member_id = ' . $UserID);
    $Responses = array();
    while($Response = mysql_fetch_array($ResponseQuery)){
        $Responses[] = $ResponseQuery[0];
    }
    if(empty($Responses)){
        return false;
    }else{
        return $Responses;
    }
} 

To then turn them into variables, if you really need to (arrays are so much more suited here) you'd use something like:

$Responses = getResponses($Database, 1); //assuming userid is 1
if($Responses === false){
    // no responses - handle this
}else{
    extract($Responses, EXTR_PREFIX_ALL, 'Response');
} 

When responses are available, they'll be stored as $Response_0, $Response_1 etc. I'd really recommend keeping it as an array though.

[DoubleDee]

Jake,

Thanks for the response! (If I ever figure out what you are saying, I think I am close to an answer?!)

Have to leave for a few hours, but a quick response for now...

This isn't about prepared statements. It's a single query that returns ten answers and stores each of them into an array... or ten variables if you really want.

But I meant that I am using Prepared Statement syntax and want to stick with that, because it isn't the same as PDO or regular SQL that used to be used in PHP.

Say, slightly off topic, in my User Profile, there will be a tab where the User can answer 10 Open-Ended Questions (e.g. "Why did you decide to go into business yourself?")

What is the easiest way to take 10 Text Boxes that are type varchar(1024) or text and INSERT them into my "answer" table?

Back on topic...

From the little I could understand from your response, I *think* what I want to do is possible. That is, take the result-set which would be 10 records and store them into one array so that when I populate my "My Thoughts" page, I can just use the values in the array, right?

Library is closing?!

Hope we can talk a little later.

Thanks for helping so far!!


Debbie

[ServerStorm]

...

From the little I could understand from your response, I *think* what I want to do is possible. That is, take the result-set which would be 10 records and store them into one array so that when I populate my "My Thoughts" page, I can just use the values in the array, right?
...
Debbie

This is correct.
Steve

[Jake Arkinstall]

As for the input, make life easy for yourself and put the form items in an array:

<input type="text" name="answers[4]" /> 

For the 5th answer, for example.

Then its stored in an array and you can use something like:

$InsertAnswer = $Database->prepare('INSERT INTO answer SET member_id = :uid, question_id = :question, response = :response');
$InsertAnswer->bindValue('uid', $_SESSION['user_id'], PDO::PARAM_INT); //for example
foreach($_POST['answers'] as $questionID => $answer){
    $InsertAnswer->bindValue('question', $questionID);
    $InsertAnswer->bindValue('response', $answer);
    $InsertAnswer->execute();
}