Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17
  1. #1
    Member
    Join Date
    Jan 2007
    Location
    Manchester, UK
    Posts
    49
    Member #
    14674
    Hi,

    I am trying to execute a query that has got a lot more complicated than I would have liked it to.

    Essentially, this is what I want to do:

    update my_table
    set g+1
    where (a OR b OR c) = d
    but only if e = f.

    Its the but only if' bit that stumping me. I know you can't use WHERE more that once in a statement so am wondering whether some sort of sub-query might be the answer??

    Please assist!! Thanks

  2.  

  3. #2
    Senior Member
    Join Date
    May 2003
    Location
    UK
    Posts
    2,354
    Member #
    1326
    Easy.

    I created the following table:

    CREATE TABLE `database`.`testtable` (
    `a` VARCHAR( 5 ) NOT NULL ,
    `b` VARCHAR( 5 ) NOT NULL ,
    `c` VARCHAR( 5 ) NOT NULL ,
    `d` VARCHAR( 5 ) NOT NULL ,
    `e` VARCHAR( 5 ) NOT NULL ,
    `f` VARCHAR( 5 ) NOT NULL ,
    `g` INT NOT NULL DEFAULT '0'
    ) ENGINE = MYISAM
    I put in some data, making sure d is equal to b and e is equal to f
    INSERT INTO `database`.`testtable` (
    `a` ,
    `b` ,
    `c` ,
    `d` ,
    `e` ,
    `f` ,
    `g`
    )
    VALUES (
    'a', 'b', 'c', 'b', 'hi', 'hi', '0'
    );
    And then updated where either a, b or c is equal to d AND e = f

    UPDATE testtable SET g = g + 1 WHERE ((a = d OR b = d OR c = d) AND e = f)

  4. #3
    Member
    Join Date
    Jan 2007
    Location
    Manchester, UK
    Posts
    49
    Member #
    14674
    Hi, thanks that seems to have got the syntax correct.

    Another problem...

    First i'll explain how it all works. Its a lottery number checker I have a table with 9 rows:

    membername | no1 | no2 | no3 | no4 | no5 | no6 | userid | matches

    You then get 6 drawn numbers:

    d1, d2, d3, d4, d5, d6

    I want to check how many numbers each user has matched, so I am running this sequence of queries:

    UPDATE the_table SET matches = (matches + 1) WHERE ((no1 = d1 OR no2 = d1 OR no3 = d1 OR no4 = d1 OR no5 = d1 OR no6 = d1) AND userid = f )

    UPDATE the_table SET matches = (matches + 1) WHERE ((no1 = d2 OR no2 = d2 OR no3 = d2 OR no4 = d2 OR no5 = d2 OR no6 = d2) AND userid = f )

    ... and so on up until all 6 drawn numbers have been covered.

    So, in theory, if the drawn numbers are 1,2,3,4,5,6 and member1 (membername) matches 1,2&3, and member 2 matches 4,5&6 then they should both have 3 in the 'matches' column.

    It isn't working out like that though, membera is getting 6 and memberb 0?

  5. #4
    Senior Member
    Join Date
    May 2003
    Location
    UK
    Posts
    2,354
    Member #
    1326
    Can we see the code that you are using?

    To clarify, each member has the lottery numbers generated and stored in their row, and then you generate 6 numbers which are then checked against each users row?

  6. #5
    Member
    Join Date
    Jan 2007
    Location
    Manchester, UK
    Posts
    49
    Member #
    14674
    Each member has six numbers (that were manually entered into the database previously) stored in columns no1 - no6. Six numbers are then drawn and checked against the members numbers to see how many they have matched.

    I am developing in ASP.NET C#, you should be able to get an idea of what is going on from the code here....

    PHP Code:
        protected void drawcheckbtn_Click(object senderEventArgs e)
        {
           
    string initialupdatesql;
           
    string updatesql1;
           
    string updatesql2;
           
    string updatesql3;
           
    string updatesql4;
           
    string updatesql5;
           
    string updatesql6;
           
    string cuser "'" Session["loggedinuser"].ToString() + "'";

           
    initialupdatesql "UPDATE syn_members SET matches = 0";
           
    lotterydrawcheck.UpdateCommand initialupdatesql;
           
    lotterydrawcheck.Update();

           
    updatesql1 "UPDATE syn_members SET matches = (matches + 1) WHERE ((no1 = @drawnball1 OR no2 = @drawnball1 OR no3 = @drawnball1 OR no4 = @drawnball1 OR no5 = @drawnball1 OR no6 = @drawnball1) AND userid = " cuser " )";
           
    lotterydrawcheck.UpdateCommand updatesql1;
           
    lotterydrawcheck.Update();

           
    updatesql2 "UPDATE syn_members SET matches = (matches + 1) WHERE ((no1 = @drawnball2 OR no2 = @drawnball2 OR no3 = @drawnball2 OR no4 = @drawnball2 OR no5 = @drawnball2 OR no6 = @drawnball2) AND userid = " cuser " )";
           
    lotterydrawcheck.UpdateCommand updatesql2;
           
    lotterydrawcheck.Update();

           
    updatesql3 "UPDATE syn_members SET matches = (matches + 1) WHERE ((no1 = @drawnball3 OR no2 = @drawnball3 OR no3 = @drawnball3 OR no4 = @drawnball3 OR no5 = @drawnball3 OR no6 = @drawnball3) AND userid = " cuser " )";
           
    lotterydrawcheck.UpdateCommand updatesql3;
           
    lotterydrawcheck.Update();

           
    updatesql4 "UPDATE syn_members SET matches = (matches + 1) WHERE ((no1 = @drawnball4 OR no2 = @drawnball4 OR no3 = @drawnball4 OR no4 = @drawnball4 OR no5 = @drawnball4 OR no6 = @drawnball4) AND userid = " cuser " )";
           
    lotterydrawcheck.UpdateCommand updatesql4;
           
    lotterydrawcheck.Update();

           
    updatesql5 "UPDATE syn_members SET matches = (matches + 1) WHERE ((no1 = @drawnball5 OR no2 = @drawnball5 OR no3 = @drawnball5 OR no4 = @drawnball5 OR no5 = @drawnball5 OR no6 = @drawnball5) AND userid = " cuser " )";
           
    lotterydrawcheck.UpdateCommand updatesql5;
           
    lotterydrawcheck.Update();

           
    updatesql6 "UPDATE syn_members SET matches = (matches + 1) WHERE ((no1 = @drawnball6 OR no2 = @drawnball6 OR no3 = @drawnball6 OR no4 = @drawnball6 OR no5 = @drawnball6 OR no6 = @drawnball6) AND userid = " cuser " )";
           
    lotterydrawcheck.UpdateCommand updatesql6;  
           
    lotterydrawcheck.Update();
        } 
    You can probably ignore the two lines:

    PHP Code:
           lotterydrawcheck.UpdateCommand updatesql1;
           
    lotterydrawcheck.Update(); 
    that follow each query, they aren't relevant here.

  7. #6
    Senior Member
    Join Date
    May 2003
    Location
    UK
    Posts
    2,354
    Member #
    1326
    I cannot see anything wrong with that. I am familar with ASP.NET/C#, I presume you set the parameters (@drawball1, @drawball2 etc) somewhere to be the picked numbers?

  8. #7
    Member
    Join Date
    Jan 2007
    Location
    Manchester, UK
    Posts
    49
    Member #
    14674
    Yes I set the parameters like you said. And I can't see anything wrong with it either!! :-\

    For example:

    Member A has : 8, 12, 16, 22, 26, 30
    Member B has: 9, 18, 27, 36, 45 and 48.

    The drawn numbers are: 8, 16, 27, 36, 45 and 48.

    Therefore, Member A should get 2 matches, Member B should get 4 matches.

    Instead, when I run those queries Member A gets 6 matches, Member B gets 0. Its as if matching the first drawn ball counts for everything. Its weird. Similarly, if both Member A and Member B had 8 as their first number, they would both get 6 matches?

  9. #8
    Member
    Join Date
    Jan 2007
    Location
    Manchester, UK
    Posts
    49
    Member #
    14674
    Just tried changing the draw to 9, 16, 27, 36, 45 and 48.

    Now Member B is getting 6 matches, and Member A 0. So whats happening is the first member that matches a number gets 6 matches, everyone else 0.

    Baffled.

  10. #9
    Senior Member
    Join Date
    May 2003
    Location
    UK
    Posts
    2,354
    Member #
    1326
    I just created a new table called lotterynumbers with 8 fields. userid, no1, no2, no3, no4, no5, no6 and matches

    The lottery numbers are: 9, 18, 27, 36, 45 and 48

    I added 2 rows:

    Row 1:
    user id: 1
    no1: 9
    no2: 18
    no3: 27
    no4: 36
    no5: 45
    no6: 48
    matches: 0

    Row 2:
    user id: 2
    no1: 9
    no2: 18
    no3: 20
    no4: 21
    no5: 22
    no6: 22
    matches: 0

    I then did the following query

    Code:
    UPDATE lotterynumbers SET matches = matches + 1 WHERE ((no1 = '9' OR no2 = '9' OR no3 = '9' OR no4 = '9' OR no5 = '9' OR no6 = '9') AND userid = '1');
    UPDATE lotterynumbers SET matches = matches + 1 WHERE ((no1 = '18' OR no2 = '18' OR no3 = '18' OR no4 = '18' OR no5 = '18' OR no6 = '18') AND userid = '1');
    UPDATE lotterynumbers SET matches = matches + 1 WHERE ((no1 = '27' OR no2 = '27' OR no3 = '27' OR no4 = '27' OR no5 = '27' OR no6 = '27') AND userid = '1');
    UPDATE lotterynumbers SET matches = matches + 1 WHERE ((no1 = '36' OR no2 = '36' OR no3 = '36' OR no4 = '36' OR no5 = '36' OR no6 = '36') AND userid = '1');
    UPDATE lotterynumbers SET matches = matches + 1 WHERE ((no1 = '45' OR no2 = '45' OR no3 = '45' OR no4 = '45' OR no5 = '45' OR no6 = '45') AND userid = '1');
    UPDATE lotterynumbers SET matches = matches + 1 WHERE ((no1 = '48' OR no2 = '48' OR no3 = '48' OR no4 = '48' OR no5 = '48' OR no6 = '48') AND userid = '1');
    And then
    Code:
    UPDATE lotterynumbers SET matches = matches + 1 WHERE ((no1 = '9' OR no2 = '9' OR no3 = '9' OR no4 = '9' OR no5 = '9' OR no6 = '9') AND userid = '2');
    UPDATE lotterynumbers SET matches = matches + 1 WHERE ((no1 = '18' OR no2 = '18' OR no3 = '18' OR no4 = '18' OR no5 = '18' OR no6 = '18') AND userid = '2');
    UPDATE lotterynumbers SET matches = matches + 1 WHERE ((no1 = '27' OR no2 = '27' OR no3 = '27' OR no4 = '27' OR no5 = '27' OR no6 = '27') AND userid = '2');
    UPDATE lotterynumbers SET matches = matches + 1 WHERE ((no1 = '36' OR no2 = '36' OR no3 = '36' OR no4 = '36' OR no5 = '36' OR no6 = '36') AND userid = '2');
    UPDATE lotterynumbers SET matches = matches + 1 WHERE ((no1 = '45' OR no2 = '45' OR no3 = '45' OR no4 = '45' OR no5 = '45' OR no6 = '45') AND userid = '2');
    UPDATE lotterynumbers SET matches = matches + 1 WHERE ((no1 = '48' OR no2 = '48' OR no3 = '48' OR no4 = '48' OR no5 = '48' OR no6 = '48') AND userid = '2');
    And afterwards - userid 1 had a matches value of 6 (correct) and userid 2 had a matches value of 2 which is correct.

  11. #10
    Member
    Join Date
    Jan 2007
    Location
    Manchester, UK
    Posts
    49
    Member #
    14674
    I see that you have first checked user 1's numbers, than user 2's.

    This I think is where mine differs.

    My database table has 9 fields, membername | no1 | no2 | no3 | no4 | no5 | no6 | userid | matches

    I only want the query to check the numbers of those members related to the logged in user (userid). So I end up checking against more than one members numbers at once.

    Could this be the problem? And if so is there a way around it?

    Thanks for all your help so far btw.


Page 1 of 2 1 2 LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 12:39 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com