Single ID Generation with MySQL

Navigation
0

I am using the code below for tests with a login system in PHP and MySQL. However, the nivel_usuario field generates a number from 0 to 2, and I would like it to generate a unique 6-digit ID containing numbers and letters (both uppercase and lowercase) 

CREATE TABLE usuarios(
    usuario_id int(5) NOT NULL auto_increment,
    nome varchar(50) NOT NULL default '',
    sobrenome varchar(50) NOT NULL default '',
    email varchar(100) NOT NULL default '',
    usuario varchar(32) NOT NULL default '',
    senha varchar(32) NOT NULL default '',
    info text NOT NULL,
    nivel_usuario enum('0','1','2') NOT NULL default '0',
    data_cadastro datetime NOT NULL default '0000-00-00 00:00:00',
    data_ultimo_login datetime NOT NULL default '0000-00-00 00:00:00',
    ativado enum('0','1') NOT NULL default '0',
    PRIMARY KEY  (usuario_id)
) ENGINE = MYISAM CHARACTER SET latin1 COLLATE latin1_general_ci COMMENT = '';
    
asked by anonymous 21.07.2017 / 04:45

3 answers

-2

I could do this: 

$aleatorio = rand(6,6); // 6 CARACTERES
$valor = substr(str_shuffle("AaBbCcDdEeFfGgHhIiJjKkLlMmNnPpQqRrSsTtUuVvYyXxWwZz0123456789"), 0, $aleatorio);

Then check if the value already exists in the database, otherwise use the value as a key in the database and if it generates another hash.

All the answers are valid and good, however I answered by following the question of the colleague, he wants to have six digits, uppercase and lowercase letters. My answer is correct because it does what was requested according to the image below:

So I do not understand why the negative score on the answer.

    
21.07.2017 / 04:56
2

You can use unique identifier methods based on timestamp , they are:

PHP uniqid() : 

printf("uniqid(): %s", substr(uniqid(), -6));

MySQL UUID() : 

SELECT LEFT(uuid(), 6);

Since you only want%% of digits, 6 and substr were used to assist in the handling of LEFT by obtaining the number of characters desired.

    
21.07.2017 / 05:15
2

If you want to be unique, assign the UNIQUE to the column, this will prevent duplication, MySQL itself will prevent the insertion of two equal values: 

ALTER TABLE sua_tabela ADD UNIQUE (nome_da_coluna);

To generate such random data you could use random_bytes , it is cryptographically secure.

As you do not want to store in bytes you can use BASE64, which includes A-Za-z0-9+/ and has a = padding. Remembering that it includes / and + in addition to letters and numbers.

21.07.2017 / 05:23
SQL query is not grouping How to do automatic translations?