3

I am trying to implement a data duplication check on user's inpout from html form before inserting it into mysql DB with the following code (largely lifted from: Best way to test if a row exists in a MySQL table):

$serialNo = $_POST['serialNo'];

$sqldupe = "SELECT EXISTS(SELECT 1 FROM eqpt WHERE serial = '$serialNo')";

    if (mysqli_query($dbcon, $sqldupe)) {
        die('RECORD already exists');
    }

But I keep getting the error message even when I try entering a $serialNo that does not exist in the DB table eqpt.

I do have the serialNo column in the table protected with the UNIQUE option to prevent duplicate entries. So this dupe check is more for a way to provide the user with a clue as to why their attempted insert was rejected if the serialNo entered is already in the DB.

I think I have a grasp of the snippet, but admit that because I have not touched sql/php in over a decade I need to get RTM to shake off some of the rust. But if there is a quick/obvious fix to the problem here I would sure appreciate the help.

Community
  • 1
  • 1
scriptz
  • 515
  • 2
  • 10

2 Answers2

3

For mysqli_query function the return value will be true because "successful SELECT, SHOW, DESCRIBE, or EXPLAIN queries it will return a mysqli_result object. For other successful queries it will return TRUE. FALSE on failure"

Your SQL will always be successful because it is either returning 1 if exists or 0 if not. Removing the Exists will return no result if it doesn't exist and one if you do meaning the rest of the code will work.

Solution:

$serialNo = $_POST['serialNo'];

$result = mysqli_query("SELECT EXISTS(SELECT 1 FROM eqpt WHERE serial = '$serialNo') AS found");

$value = $result->fetch_object();

if ($value->found) {
  die('RECORD already exists');
}
OutOfBorder
  • 66
  • 7
  • You got the cause right, but not the solution. Just fetch the value returned by the query. – Shadow Jan 16 '17 at 03:39
  • Shadow is right, I thought you'd get a false result if the query didn't return anything but you do still get a result object. I'll update my response for a proper code solution. – OutOfBorder Jan 16 '17 at 21:56
1

You can try this

$serialNo = $_POST['serialNo'];

 $sqldupe = "SELECT serial FROM eqpt WHERE serial = '$serialNo'";
if (mysql_num_rows(mysql_query($dbcon, $sqldupe)) > 0) {
    die('RECORD already exists');
}
Adhan Timothy Younes
  • 560
  • 3
  • 12