PHP Mysql - duplicate return

Question

PHP Mysql - duplicate return

Navigation

#1 by (0 votes)

2

Well, my question is: How can I be checking to see if there is already such a value in the database?

I have this code here that is already done the verification by the same SQL Query, that is if there is no such value it inserted and if it exists it did 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 not tell the user if it was entered or if the value already exists.

Could someone tell me a functional way to do this check?

php mysql

asked by anonymous 08.02.2016 / 18:15

1 answer

Saving names SQLite with Javascript does not work

score 0 · Accepted Answer

Problem:

This should occur because the function:

if($resultado == 1){

It would be equivalent to this:

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

This will always return true / 1 if query runs successfully, I think. 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 the query construction itself. The most common, in addition to syntax, would be to try to insert null content where it is not allowed, for example.

Solution:

In this case of 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 rows have been changed / inserted, this will actually identify whether the query you entered has been updated.

In 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 have been, this will identify what you want, since if there is any return it is because it already exists.

Notes:

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

A difference between the two is that the num_rows is done in the variable of the result itself ( $resultado ), while affected_rows is done directly from where the connection to the database starts, in the $MySQLi case %.