SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Updating multiple columns and multiple rows with one MySQL query

    Hi SitePoint members

    I have been perusing through the solutions for "updating multiple rows with one query", but I have a pressing question: How would one "SET" multiple column values with one query?

    Here is my example...the normal update command would be:
    Code:
    UPDATE table_FooBar 
    SET   answerOne='$ans1Val', 
          answerTwo='$ans2Val', 
          answerThree='$ans3Val' 
    WHERE member_id='$memberid' AND question_id='$questionid';
    Now I have to do this for up to 20 rows for that individual reponse.
    Normally, it would be nice to do this - but when I put it into a loop, MySQL database only executes the first line of code for some odd reason.
    Code:
    // question ID ranges from 1-20
    
    // $questionid == 1, $member_id==1
    UPDATE table_FooBar 
    SET   answerOne='$ans1Val', 
          answerTwo='$ans2Val', 
          answerThree='$ans3Val' 
    WHERE member_id='$memberid' AND question_id='$questionid';
    
    // $questionid == 2, $member_id==1
    UPDATE table_FooBar 
    SET   answerOne='$ans1Val', 
          answerTwo='$ans2Val', 
          answerThree='$ans3Val' 
    WHERE member_id='$memberid' AND question_id='$questionid';
    
    ...etc.
    Here is the PHP loop
    Code:
    $numQuestions = 20;
    $questionid = 1;
    
    for($x=1;$x<=$numQuestions;$x++)
    {
    	$sql = " UPDATE table_foobar "; 
    	$sql .= " SET answerOne='" . $ans1Val . "' ";
    	$sql .= ", answerTwo = '". $ans2Val ."' ";
    	$sql .= ", answerThree = '". $ans3Val ."' ";
    	$sql .= " WHERE "; 
    	$sql .= " member_id=" . $memberid . " AND question_id= " . $questionid . "; ";		
    	$result = mysql_query($sql, $db); 
    
    	$questionid++;
    }
    And here is what I see parsed when I debug it (I am only showing the first 3 lines of parsed code)
    Code:
    UPDATE table_FooBar SET answerOne='yes', answerTwo='no', answerThree='yes' WHERE member_id = 1 AND question_id = 1;
    UPDATE table_FooBar SET answerOne='no', answerTwo='no', answerThree='yes' WHERE member_id = 1 AND question_id = 2;
    UPDATE table_FooBar SET answerOne='yes', answerTwo='yes', answerThree='no' WHERE member_id = 1 AND question_id = 3;
    BUT>> It only executes the first line of code! Any Tips?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by swissv2 View Post
    Any Tips?
    oh, absolutely!!

    your problem likely lies in the php code, something about constructing variables and looping (i'm guessing -- i don't do php so i only gave your code a cursory glance)

    however, since you asked this question in the mysql forum, let me give you a mysql answer

    redesign your table, normalize it to first normal form

    you have repeating columns in answerOne, answerTwo, answerThree

    these should be three rows in a normalized table

    from there, the php will be slightly different
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello r937!

    Thanks for your quick reply. To address your normalization suggestion, here is my table structure (it goes into a tiny bit more detail; my first example was a very generalized one, this one is also generalized to a certain extent) - and there is a specific reason why I have designed it so...which I will explain in a bit.
    Code:
    // Users Only take the survey 'foobar' once, and can 
    // edit the answers if and only if they have not completed the survey.
    table_member (
     member_id,  
     takingFoobar,
     foobarCompleted,
     foobarStartTime,
     foobarEndTime
    ) 
    
    //foobar is actually a "results" table. ;)
    table_foobar (
     foobar_id,
     member_id,
     question_id,
     answerOne,
     answerTwo,
     answerThree,
     answerFour
    )
    
    //Multiple questions for a single foobarMain survey.
    tbl_foobarQuestion (
     question_id,
     foobarMain_id,
     foobarQuestion
    )
    
    tbl_foobarMain (
     foobarMain_id,
     foobarName,
     foobarDescription
    )
    So now is the BIG explanation of the table structure.
    First, take look at the "Foobar Survey" image below.

    Few very important notes:
    • Every question is pulled from the database

    • The Survey will be displayed on one page (thus the need to put the answers into one table)

    • There is a need to record all answers for each member

    • There will be 1000-2000 people (this I am sure of) that will be taking the "REAL" survey (up to twenty questions) which is based on this question, so I need to execute the query in one big chunk instead of 20 tiny mysql queries.

    • The data has been populating like a charm. All I need to do is update the "foobar" table properly, and I believe that a MySQL Case Query should do the trick...I just don't know how to at this point.


  4. #4
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Methinks I done scared everyone away with my big post.

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    nope. you have made a couple of erroneous assumptions.

    The Survey will be displayed on one page (thus the need to put the answers into one table)
    The data has been populating like a charm.
    your database is not normalised as was mentioned before.

    your answers should be in one table, using an FK to relate them to the questions table.

    btw, why did you make up ficticious table names and then add the explanation? you really ought to help make it easier for poeple to try to help you.

    There are other issues but, I got confused with the table names.

    bazz

  6. #6
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    your database is not normalised as was mentioned before. your answers should be in one table, using an FK to relate them to the questions table.
    My answers are in one table (table_foobar), and does use a FK(question_id) to relate them to the questions table (table_questions). r397 recommended to have the three rows in a normalized table, in first normal form. The problem I find with that structure is there are multiple answers to a single question at the same time an variable number of questions and an variable number of answers plus multiple answers for a single individual that need to be recorded. Sound confusing? Take a look at the example below. Now if there was a single answer to a single question, normalizing it in that fashion would be ideal.


    Example


    Code:
    Member #1 Responses
    - Question #1: yes, 22, no, 34
    - Question #2: no, 24, yes, 42
    - Question #3: yes, 14, no, 42
    
    Member #2 Responses
    - Question #1: no, 42, no, 55
    - Question #2: no, 51, yes, 21
    - Question #3: yes, 11, no, 14
    
    etc.

    But, since you are seeing some errors - how would you restructure the tables?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you show us one example of an actual question that has to have 4 different answers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r397, I see your concern and how you may recommend having only one answer for one question, which makes it so much easier to update SQL and PHP wise!
    Unfortunately, I was tasked to present the survey in the format shown in the screenshot below, which is why I chose to design the table structures the way I did.

    Thing is, there have been no concerns about normalization as I have had no problems with data insertion, querying results, reporting the answers, association of answers with members, etc! The only thing I am trying to solve is updating the database with one query instead of (1 to N) number of insert queries depending on how many "items" are listed.

    Screenshot description:
    - The database must contain a record of answers made by all users
    - Every "Item" has 4 answers associated with them, (for now...)
    - There can be any number of "items" listed on a single survey page (in this screenshot, I only show 3 items, but there may be up to 20 or 30 items that have to be listed)
    - "Value Saved" has a HIDDEN constant that gets calculated with the "Test Value" entered.

    Example (User enters: 24052)
    If "Use Alternative" is checked, then use hidden constant [X] to calculate "Value Saved": (24052 - X = 1022)


  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, so it looks like you are heavily invested in the current design, so leave it

    i guess you must reconcile yourself to changing your php code so that it issues multiple update statements
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, even though I have shown my table structure, if you have a much more logical table structure for this survey, by all means I would love see what you had in mind.

    Back to my original question: How would I format a CASE Statement query to update the database?


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
  •