How to check if a name is in the database?

1

I want to check if a name exists in the database; if there is no registration. Using the code below I can register multiple users with the same name.

cadastro.php

    $user = $_POST['user'];
    $pass = md5($_POST['pass']);
    if(empty($user) or empty($pass)) echo "Preencha todos os campos para continuar!";
    else {
    $dbhost = "localhost";
    $dbuser = "user";
    $dbpass = "senha";
    $dbname = "banco";
    $con = mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error());
    $db = mysql_select_db($dbname, $con) or die(mysql_error());
    $query = mysql_query("INSERT INTO tbl_users VALUES (NULL,'$user','$pass')");
    if($query) echo "Sua conta foi criada com sucesso!";
    }

form.html

    <form name="form3" method="post" action="cadastrar.php">
    <label>USER:</label>
    <input type="text"  name="user"/>
    <label>PASS:</label>
    <input type="password" name="pass" />
    <input type="submit" value="CADASTRAR" />
    </form>

    tabela.sql

    CREATE TABLE 'tbl_users' (
    'id' int(11) NOT NULL auto_increment,
    'username' varchar(250) collate latin1_general_ci NOT NULL,
    'password' varchar(250) collate latin1_general_ci NOT NULL,
    PRIMARY KEY  ('id')
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=6 ;

I tried to modify these 3 lines and the registration is not done already jumps to message that the user exists even though it does not exist. How do I fix this?

    $query = mysql_query("INSERT INTO tbl_users VALUES ('$user','$pass')");
    if($query) echo "Sua conta foi criada com sucesso!";
    else echo "Usuário já existe, escolha outro nome.";
    
asked by anonymous 21.12.2014 / 16:56

1 answer

3

If you put a restriction on the database it should resolve. This is done through the UNIQUE KEY clause.

CREATE TABLE 'tbl_users' (
'id' int(11) NOT NULL auto_increment,
'username' varchar(250) collate latin1_general_ci NOT NULL UNIQUE KEY,
'password' varchar(250) collate latin1_general_ci NOT NULL,
PRIMARY KEY  ('id')
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=6 ;

In this way any INSERT that tries to use an existing value in any table row will result in query error and can handle this.

When you use UNIQUE it might be interesting create an index to streamline the query.

ADD UNIQUE INDEX 'username' ('username');
    
21.12.2014 / 17:16