Solved

mysql update query

Posted on 2009-05-18
14
175 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
  • 7
  • 5
  • 2
14 Comments
 
LVL 108

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 108

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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now