mysql update query

got a slightly different question to last which I am struggling on. I have two tables in mysql database as follows.

table 4

username     module1     module2    module3

user1           2                 2               2
user2          0                 2              2
user3          2                 2               1
user4           2                 0               1
user5           0                 2               1
user6           1                 2               2
user7           2                 1               2
user8           1                 2               2
user9           0                 1               2
user10         2                 2               1

table 5

username   completed

user1
user2
user3
user4  
user5
user6
user7
user8
user9
user10


I need to update the completed fields for users in table5. There are already records for the users in table5 just no entry for completed.

The logic needs to work as follows. If there is a '2' for a module then it means that module is complete. And a '1' is added to completed. A '0' or a '1' for any modules means nothing, they do not get 1 added to completed. So in the case of user1, that person has completed of 3 (1 + 1 + 1). HOWEVER, there is a key point. A module is only seen as complete if the preceeding module has a 2 in it. So in the case of user 2, that person's completed is 0 because module 1 has a zero. It is irrelevant that they got a 2 in module 2 and module 3. Could somebody help me out with an update sql statement for completed in table 5?

Just so I show the logic, below is what the completed would be for all 10 users. Thanks.

username   completed

user1         3
user2         0
user3         2
user4         1  
user5         0
user6         0
user7         1
user8         0
user9         0
user10       2
RupertAAsked:
Who is Participating?
 
racekConnect With a Mentor Commented:
small buggs are fixed - you can test it in query browser :-)
drop table table4;
create table table4( username varchar(10) not null primary key, module1 int,  module2 int,  module3 int);
insert into table4 VALUES('a',2,2,2),('b',1,2,2),('c',2,1,2),('d',2,2,0),('e',2,2,0);
drop table table5;
create table table5( username varchar(10)  not null primary key,completed int);
 
 
INSERT INTO table5
SELECT a.username,  IF(a.module1<>'2',0,
                        1 + IF(a.module2<>'2',0,
                        1 + IF(a.module3<>'2',0,1)))
  FROM table4 as a
ON DUPLICATE KEY UPDATE
 completed =  IF(a.module1<>'2',0,
                        1 + IF(a.module2<>'2',0,
                        2 + IF(a.module3<>'2',0,1))) ;
 
select * from table4;
select * from table5;
 
UPDATE table5 SET completed = 0;
UPDATE table5
SET completed = (SELECT IF(a.module1<>'2',0,
                        1 + IF(a.module2<>'2',0,
                        1 + IF(a.module3<>'2',0,1)))
                   FROM table4 a WHERE a.username = table5.username ) ;
 
 
select * from table4;
select * from table5;

Open in new window

0
 
Ray PaseurCommented:
Can you show us the code you have tried for this?  Also, what is the application for?  Thanks, ~Ray
0
 
RupertAAuthor Commented:
Hi Ray,

i don't have any code for this as I don't know how to go about it. i don't know whether a mysql update statement will do it or whether I will have to create a php page which I run which retrieves the record, apply some logic to it with a bunch of statements and then update their record in table.

I can tell you what I am trying to do though. module1, module2, module3 are modules in a course that students sit. Before they could go into any module at any point. It they haven't been in to a module they get a 0. If they have been in and not completed they get a 1 and if they have been in and completed they get a 2. The course has been re-programmed and is now sequential ie. you only get in to module 2 if you have completed module1. table4 will soon be deleted and what will remain is table 5 where they have a completed score.

So that is where I am at.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Ray PaseurCommented:
I would go with a PHP page to iterate over the user names and modules completed.  Trying to do this in one query might be confusing.

I would create an array of users, then iterate over their completed modules.  Here is the sort of logic I would use to check for the completions in order.
// THE NUMBER OF COMPLETED MODULES
$complete = 0;
 
// CHECK THE COMPLETED MODULES IN ORDER
if ($module1 == 2)
{
   $complete++;
   if ($module2 == 2)
   {
      $complete++;
      if ($module3 == 2)
      {
         $complete++;
      }
   }
}

Open in new window

0
 
RupertAAuthor Commented:
yeah thanks ray, sitting here I was coming to a similar conclusion. I am going to break it up into chunks. What I will do is create a completed field in table4. Using your complete++ logic above I am going to update that field with a completed score. Then I will update table5 with the completed from table 4.

I will report back how I am gettting on. Plus if I get totally stuck on something, it is going to be easier to ask advice on a small query rather than the whole thing.

thanks ray.
0
 
racekCommented:

UPDATE TABLE table5
SET completed = (SELECT a.username,  IF(a.module1='2',1,0) + IF(a.module2='2',1.0)+ IF(a.module3='2',1,0) AS hits
                   FROM table4 a WHERE a.username = table5.username ) ;

Open in new window

0
 
racekCommented:
IF you already have rows in table 5.... and if username is a primary key or unique index:
INSERT INTO table5
SELECT a.username,  IF(a.module1='2',1,0) + IF(a.module2='2',1.0)+ IF(a.module3='2',1,0) 
  FROM table4 as a 
ON DUPLICATE KEY UPDATE
 completed = IF(a.module1='2',1,0) + IF(a.module2='2',1.0)+ IF(a.module3='2',1,0) ;

Open in new window

0
 
racekCommented:
sorry I will complete it with order between modules ....
0
 
racekCommented:
wit order from modul1 ...
UPDATE TABLE table5
SET completed = (SELECT a.username,  
                     IF(a.module1<>'2',0,
                        1 + IF(a.module2<>'2',0,
                        2 + IF(a.module3<>'2',0,1))) AS hits
                   FROM table4 a WHERE a.username = table5.username ) ;

Open in new window

0
 
racekCommented:
and if table 5 is empty or has some of rows
INSERT INTO table5
SELECT a.username,  IF(a.module1<>'2',0,
                        1 + IF(a.module2<>'2',0,
                        2 + IF(a.module3<>'2',0,1)))
  FROM table4 as a 
ON DUPLICATE KEY UPDATE
 completed =  IF(a.module1<>'2',0,
                        1 + IF(a.module2<>'2',0,
                        2 + IF(a.module3<>'2',0,1))) ;

Open in new window

0
 
RupertAAuthor Commented:
hey racek,

thanks for all this. I am currently at home (I live in UK).

however when I get in first thing tomorrow, I will try it out and report back. Thanks a lot for the above.
0
 
racekCommented:
ok, I'm in Sweden :-) 1 hour before you.... just tell me if table5 is allways empty and if you have PK or unique index on username a
0
 
RupertAAuthor Commented:
your solution is working grear racek, I have one last tweak to make then I will give out points thanks.
0
 
RupertAAuthor Commented:
thanks for all your help racek. Your solution worked wonderfully!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.