为什么mysql不告诉我重复是什么？

Question

I have a simple table, two columns and one primary key. Here's the PHP I use to populate the table:

$query = "INSERT INTO alt_upcs (sku, upc) VALUES (".$upc[0].",\"".$upc[1]."\")";
mysqli_query($link, $query);
if ($error = mysqli_error($link))
{
  if (preg_match("+DUPLICATE+i", $error))
  {
    $errQuery = "SELECT * FROM alt_upcs WHERE upc LIKE ".$upc[1];
    $result = mysqli_query($link, $errQuery);
    $errRow = mysqli_fetch_row($result);
    echo "Duplicate UPC found: ".$upc[1]." exists for skus ".$errRow[0]." and ".$upc[0].".
";
  }
  else
  {
    echo $error."
$query
";
  }
}

The $upc array is created from an input file containing lines that start with a sku followed by up to 7 upcs. In some rare cases there are more than one sku with the same UPC and in even rarer cases a single sku can have the same UPC twice. The code above works fine for the former, I get the error message Duplicate UPC found: xxxx exists for skus yyyy and zzzz.

But, when there is a sku with two UPCs, and the above block of code runs for the second UPC, I get Duplicate UPC found: xxxx exists for skus and zzzz.

Obviously, MySQL recognizes the duplicate, so why does the error query not show it?

Some additional info, upc is my unique key (primary index) and I can query the table manually and see the value I am attempting to get in my script

Thanks to Bogdan for leading me to my error: I was using LIKE because the field type (which I neglected to share) is varchar and I didn't encapsulate the string $upc[1] insite of double quotes - MySQL thinks I was sending it an int because the string is numeric , which chops off any leading zeros.

Either of these work, but the second is what I should be doing.

  $errQuery = "SELECT * FROM alt_upcs WHERE upc = ".$upc[1];

This works because MySQL comparing the integer value of the field with the integer value of `$upc[1]

  $errQuery = "SELECT * FROM alt_upcs WHERE upc LIKE \"".$upc[1]."\"";

Properly enclosing the string is what I meant to do to begin with.

Answer 1

Your $errQuery is going to be:

SELECT * FROM alt_upcs WHERE upc LIKE foo

This is a syntax error - the value after the LIKE should be quoted:

SELECT * FROM alt_upcs WHERE upc LIKE 'foo'

Although if you're not going to apply any wildcards, you ought to use a direct equality check:

SELECT * FROM alt_upcs WHERE upc='foo'