How to check if a name is in the database?

Question

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

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!";
    }

html form.

    <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 without existing. How to 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.";

Answer 1

If you place a restriction in the database you must resolve. This is done through the clause UNIQUE KEY.

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;

I put in the Github for future reference.

In this way any INSERT that trying to use an existing value in some table row will result in error in query and you can treat this.

When you use UNIQUE might be interesting create an index to expedite the consultation.

ADD UNIQUE INDEX `username` (`username`);