?
Solved

mysql update query

Posted on 2009-05-18
14
Medium Priority
?
185 Views
Last Modified: 2013-12-12
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
0
Comment
Question by:RupertA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
14 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 24413058
Can you show us the code you have tried for this?  Also, what is the application for?  Thanks, ~Ray
0
 

Author Comment

by:RupertA
ID: 24413197
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 24413341
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:RupertA
ID: 24413406
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
 
LVL 14

Expert Comment

by:racek
ID: 24414792

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
 
LVL 14

Expert Comment

by:racek
ID: 24414856
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
 
LVL 14

Expert Comment

by:racek
ID: 24414867
sorry I will complete it with order between modules ....
0
 
LVL 14

Expert Comment

by:racek
ID: 24414910
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
 
LVL 14

Expert Comment

by:racek
ID: 24414943
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
 
LVL 14

Accepted Solution

by:
racek earned 2000 total points
ID: 24415071
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
 

Author Comment

by:RupertA
ID: 24416117
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
 
LVL 14

Expert Comment

by:racek
ID: 24416391
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
 

Author Comment

by:RupertA
ID: 24422383
your solution is working grear racek, I have one last tweak to make then I will give out points thanks.
0
 

Author Comment

by:RupertA
ID: 24429496
thanks for all your help racek. Your solution worked wonderfully!
0

Featured Post

Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question