PHP Mysql - return of duplicates

Asked

Viewed 253 times

2

Good staff my question is the following: How can I be doing the check to know if there is already such value in the database?

I have this code here that is already checked by SQL Query itself, ie if there is no such value it inserted and if there is no it does not insert, This is working perfectly.

$sql = 'INSERT INTO usuarios(nome,apelido) 
    SELECT "'.$valor[0].'", "'.$valor[1].'"
    FROM DUAL
    WHERE NOT EXISTS(SELECT nome,apelido FROM usuarios 
       WHERE nome = "'.$valor[0].'" OR apelido = "'.$valor[1].'")';

$resultado = $MySQLi->query($sql) OR trigger_error($MySQLi->error, E_USER_ERROR);

if($resultado == 1) { 
  echo 'sucesso'; 
} else { 
    echo 'já existe esse nome ou nick'; 
}

The problem is to receive this notification and tell the user if it already exists, because it always returns 1 in the $result variable so I can’t tell the user if it was entered or if the value already exists.

Could someone tell me a functional way to make that check?

php mysql

  • I don’t know which API you use to do the queries but the idea is to take the number of rows returned by the query, $resultado It seems to me a Source, then your command is se(resource == 1), when it should be something like se($total_registros >= 1)

  • Fine, I’ll post here the Class I’m using to make the query.

  • http://blog.thiagobelem.net/php-e-mysql-para-iniciantes-consulta-simples

  • Can you tell if it’s Mysql or Mysqli? I don’t have access to the link ... try to get the number of lines with mysqli_num_rows, must look something like this, $resultado = $MySQLi->query(....); echo $resultado->num_rows; should appear some number, then just make the right comparison ;)

  • 1

    if($mysqli->affected_rows >= 1){ echo 'sucesso';} else {echo 'já existe'; };

  • All right, I’ll test it and I’ll get back to you.

  • Diego Felipe, Thank you very much the Code Techo you sent worked perfectly.

Show 2 more comments

1 answer

0


Problem:

This must occur because the function:

if($resultado == 1){

Would be equivalent to that:

if($MySQLi->query($sql) == 1){

This will always return true/1 if the query is successfully executed, I believe. This does not take into account if there is no content insertion/update. The only error that can result is if there is an error in building the query itself. The most common, besides syntax, would be to try to insert a null content where it is not allowed, for example.

Solution:

In this case INSERT/UPDATE/REPLACE/DELETE:

if($MySQLi->affected_rows >= 1){
  echo 'sucesso'; 
}else{ 
    echo 'já existe esse nome ou nick'; 
}

The $resultado->affected_rows will "count" how many lines have been changed/inserted, this will actually identify if there was the insertion/update of the query you want.

In the case of SELECT:

if($resultado->num_rows === 0){
// No caso de select geralmente é invertido, quando não existe resultado (==0) é porque o usuário está disponível, como em APIs de verificação antes do envio do formulário.
  echo 'sucesso'; 
}else{ 
    echo 'já existe esse nome ou nick'; 
}

The $resultado->num_rows will "count" how many returns there have been, this will identify what you want, since if there is any return it is because it already exists.

Notes:

I believe it would be ideal to use the bind_param and the prepare instead of using directly the query, for security purposes.

One difference between the two is that the num_rows is done in the result itself variable ($resultado), as long as the affected_rows is done directly from where part of the connection to the database, in case $MySQLi.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.