SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,930
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Prepared Statement to place Multiple Rows into Variables

    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...
    Code:
    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

  2. #2
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP Code:
    function getResponses(PDO $Database$UserID){
        
    $ResponseStatement $Database->prepare('SELECT response FROM answer WHERE member_id = :uid');
        
    $ResponseStatement->bindValue('uid'$UserIDPDO::PARAM_INT);
        
    $ResponseStatement->execute();
        
    $Responses $ResponseStatement->fetchAll(PDO::FETCH_COLUMN0);
        if(empty(
    $Responses)){
            return 
    false;
        }else{
            return 
    $Responses;
        }

    It's just as simple with MySQL:
    PHP Code:
    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:
    PHP Code:
    $Responses getResponses($Database1); //assuming userid is 1
    if($Responses === false){
        
    // no responses - handle this
    }else{
        
    extract($ResponsesEXTR_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.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,930
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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...


    Quote Originally Posted by Jake Arkinstall View Post
    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

  4. #4
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    ...

    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
    ictus==""

  5. #5
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    As for the input, make life easy for yourself and put the form items in an array:
    PHP Code:
    <input type="text" name="answers[4]" /> 
    For the 5th answer, for example.

    Then its stored in an array and you can use something like:
    PHP Code:
    $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();

    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona


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
  •