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:


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.


// 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


$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)

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?

oh, absolutely!! :smiley:

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 :wink:

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.

// 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.

Methinks I done scared everyone away with my big post. :sick:

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

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

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?

could you show us one example of an actual question that has to have 4 different answers

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 to calculate “Value Saved”: (24052 - X = 1022)

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

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?