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);

??? 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!

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.

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

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!

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:

(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.