How not to enter repeated records?

Question

I’m making a login system in which each user creates a URL that sends to the bank, only that I need to make it does not repeat information, if there is already similar information in the bank it returns result (URL indisponível).

INPUT CODE:

MEUSITE.COM/<input class="subbutton" name="url" type="text" value="<? echo("$prof[url]");?>" size="40">

PHP:

<? }

elseif($update==update)
{
$nomedoevento = clean($_POST[nomedoevento]);
$descricaodoevento = clean($_POST[descricaodoevento]);
$datadoevento = clean($_POST[datadoevento]);
$horadoevento = clean($_POST[horadoevento]);
$localdoevento = clean($_POST[localdoevento]);
$enderecodoevento = clean($_POST[enderecodoevento]);
$bairrodoevento = clean($_POST[bairrodoevento]);
$cidadedoevento = clean($_POST[cidadedoevento]);
$estadodoevento = clean($_POST[estadodoevento]);
$habilitarevento = clean($_POST[habilitarevento]);
$url = clean($_POST[url]);
$updatenomedoevento = mysql_query("update usr_users set nomedoevento = '$nomedoevento', descricaodoevento = '$descricaodoevento', datadoevento = '$datadoevento', horadoevento = '$horadoevento', localdoevento = '$localdoevento', enderecodoevento = '$enderecodoevento', bairrodoevento = '$bairrodoevento', cidadedoevento = '$cidadedoevento', estadodoevento = '$estadodoevento', habilitarevento = '$habilitarevento', url = '$url' where email = '$_SESSION[usr_email]'");
    echo("Suas informações foram atualizadas!");
} ?>

Answer 1

Will always have some "crazy" proposing to give a SELECT to see if a certain record exists to enter later, do not waste time with these solutions because usually whoever does this has no idea what they are doing. There is no guarantee that there will not be an insertion by another process between the SELECT and the INSERT next.

What we need (after all, which is the goal to be achieved) is to avoid inserting duplicate records, and DB already has the right mechanism for this, which is the only key.

First, you must create an index UNIQUE for the column URL. This is your guarantee of oneness.

The UNIQUE is not limited to just one column. If it were another situation where two or more columns could not repeat, like a doctor’s schedule, in the same way could create the same UNIQUE index using both the doctor’s ID column and the time (the same time could be used by two different doctors, or the same doctor attending two different times, but never two schedules with same time and same doctor).

After you created your UNIQUE, just compare the return Mysql with value 1062 - ER_DUP_ENTRY to know if the URL is repeated:

    $useremail = $_SESSION[usr_email]; // pra usar na query

    mysqli_query ( $db, " 
       UPDATE usr_users SET
          nomedoevento = '$nomedoevento',
          descricaodoevento = '$descricaodoevento',
          datadoevento = '$datadoevento',
          horadoevento = '$horadoevento',
          localdoevento = '$localdoevento',
          enderecodoevento = '$enderecodoevento',
          bairrodoevento = '$bairrodoevento',
          cidadedoevento = '$cidadedoevento',
          estadodoevento = '$estadodoevento',
          habilitarevento = '$habilitarevento',
          url = '$url'
       WHERE email = '$useremail'
    ");

    $errno = mysqli_errno($db); 

    // Vamos usar um define() só para não por o 1062 diretamente no if.
    // nada impede de usar if( $errno == 1062), só que fica menos claro.

    define("MYSQL_ER_DUP_ENTRY ", 1062);

    if ( $errno == 0 ) {
       echo( 'Suas informacoes foram atualizadas!' );
    } else if ( $errno == MYSQL_ER_DUP_ENTRY ) {
       echo( 'Este URL ja existe!' );
    } else {
       echo( 'Ocorreu um erro: ' . mysqli_error($db) );
    }

Note that I did not tidy up your code to avoid SQL Injection by not being the focus of the question. As it stands, a malicious form can send data to delete your BD, or even steal information.

If you want, you can change the if by a switch.