Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6

Thread: Help with mysql

  1. #1
    New to the CF scene
    Join Date
    Sep 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with mysql

    I have the following coding, it generates a 6 digit number, now what I would like to know is, how am I gonna select a single number out of that 6 digits? Say if i want to select the second digit out of the 6? The reason for doing so, I want to add a check digit in MySQL.


    -- Delete the old version of the procedure so we start fresh

    DROP PROCEDURE IF EXISTS mwc_account_batch_generator;





    -- --------------------------------------------------------------------------------

    -- Routine DDL

    -- Routine: mwc_account_batch_generator

    -- Project: mwc account management

    -- Author: Christiaan Botma

    -- Date: 2011-09-20

    --

    -- [Short Description of the routine goes here]

    --

    -- [Define all input parameters here]

    --

    -- [Define what is returned here]

    --

    -- --------------------------------------------------------------------------------





    -- change the delimeter to cause MySQL to see the following block

    -- of statements as a single command

    DELIMITER $$





    -- Create the procedure, give it a name and define the input parameters

    CREATE PROCEDURE `mwc_account_batch_generator` (

    IN in_total_accounts BIGINT ,

    IN in_initial_balance float(10,2)



    -- CONSTRAINT loc_un UNIQUE(location)

    )

    BEGIN



    DECLARE i INT DEFAULT 1;



    drop table if exists tmp_account_generator;





    create temporary table tmp_account_generator

    (

    acc_number BIGINT not null,

    balance float(10,2) not null,

    UNIQUE KEY (acc_number)

    );



    -- Stored procedure code goes here





    -- insert new accounts into temp table with the balance specified by initial balance.

    -- random account number generation rules:

    -- Number must contain 6 digits

    -- Number must not exist in the table, has to be unique





    WHILE i <= in_total_accounts DO

    insert into tmp_account_generator(acc_number, balance) values (100000 + (RAND() * 899999), in_initial_balance);



    SET i = i + 1;

    END WHILE;





    -- select all new generated accounts





    select * from tmp_account_generator;

    END$$











    call mwc_account_batch_generator(10, 10.02);

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    ??? That code seems very very badly BROKEN to me!

    Sure, it guarantees that WHEN YOU RUN IT it will get unique account numbers.

    But because it drops and recreates the temp table each time, there is *NO GUARANTEE AT ALL* that it won't generate the *SAME* random 6-digit account number when it is run again!

    So today you assign account number 817832 to Mr. Jones. No problem. Tomorrow (or next week or next month or next year...or 13 seconds from now!) you end up assigning account number 817832 to Ms. Anderson!

    THIS IS REALLY REALLY BAD!

    I think you need to toss this out and rethink the entire process!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    Oh...and why do you ask for 10 different account numbers, each time you call the SP? What happens if you need new account numbers for, say, 17 new customers? Or 3 new customers?

    This whole thing just screams for rewrite.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    On top of that, a "check digit" is usually *NOT* simply a copy of one of the digits in the account number. Instead, a "check digit" is usually calculated mathematically to ensure that it really *is* a check digit.

    http://en.wikipedia.org/wiki/Check_digit
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New to the CF scene
    Join Date
    Sep 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for being honest, I'm totally new to tis scene. How would you then write a procedure to generate the 6 digit numbers and then add the check digit for it to be UNIQUE? I would realy appreciate the help guys. Thanks!

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    Well, to ensure uniqueness, you would need to keep track of all ALREADY-ISSUED account numbers.

    I would assume you have some table some place where you indeed associate a new user with his/her account number? USERS table or maybe ACCOUNTS table?

    In that table, the account number needs to be either the PRIMARY KEY or at least declared as a UNIQUE INDEX. Doing that will ensure that any attempt to assign a duplicate account number will produce an error.

    At this point, then, it's not important *how* you generate the account number. You could do it in PHP code, for example, instead of in a SQL procedure. You just have to make sure that you test it for uniqueness against that table (above).

    And since manipulating numbers--including finding a check digit value--is going to be easier in PHP (or ASP or JSP) code than in SQL, I'd do it all there, instead.

    So the pseudo-code would be something like this:
    Code:
    (1) in PHP/ASP/JSP, generate a random account number, with check digit
    (2) query your db:  SELECT COUNT(*) FROM accountsTable WHERE accountNumber = [newly generated random number]
    (3) If the COUNT(*) from step (2) comes back as OTHER than zero, go back to step 1.
    Keep it simple like that.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •