Page 1 of 1

Checking for Dulplicate entries

Posted: Mon Dec 29, 2008 5:18 am
by Greg19
Hey everybody, I have a php file were information is taken out of the database table full of product numbers and several checkboxes are displayed for every product number, when the user checks multiple boxes and hits submit an array of the products in created, and the username + product numbers are inserted into various rows in the table. Well I would like to be able to check to see if any combinations are duplicates. is it possible to do this with out affecting the other insertions?

here is my code that doesn't check for duplicates:

Code: Select all

<?php
session_start(); 
if(!isset($_SESSION['adminctrl'])){ 
    header('Location: admin.php'); die('<a href="admin.php">Login first!</a>');
   }
$query = mysql_connect("*****.net", "*****", "******") or die(mysql_error());
mysql_select_db('*****', $query) or die(mysql_error());
 
if (isset ($_POST['customer'])) {
    
    $customer = ($_POST['customer']);
    
    foreach (($_POST['product_id']) as $ID){
                  $sql = "INSERT INTO `customers_products` (customer, product_id) VALUES ('$customer','$ID')" ;
            }
     } 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Customer Update</title>
</head>
<html>
<body>
<form method="post" action="test.php">
<?php
 
$conn = "SELECT company FROM `users`";
$result = mysql_query($conn, $query);
 
while ($row = mysql_fetch_row($result)) {
  $user[] = $row[0];
}
 
echo "<select name='customer'>\n";
foreach ($user as $v) {
  echo "<option value='$v'>\n" . $v . "</option>\n";
}
echo "</select>\n";
 
echo "<br/><br/>";
echo "<div class='check_container'>";
$conn = "SELECT product_id FROM `products`";
$result = mysql_query($conn, $query);
 
while ($row = mysql_fetch_row($result)) {
  $product[] = $row[0];
}
 
foreach ($product as $y) {
  echo "<input class='checkbox' type='checkbox'  name='product_id[]' value='$y'>\n" . $y . "</input>\n";
}
 
?>
</div>
<br/><br/>
<input type="submit" name="submit" value="Update" />
 
</form>
</body>
</html>
and here is my attempt at this, naturally when I tried it nothing worked.

Code: Select all

<?php
session_start(); 
if(!isset($_SESSION['adminctrl'])){ 
    header('Location: admin.php'); die('<a href="admin.php">Login first!</a>');
   }
$query = mysql_connect("h41mysql61.secureserver.net", "JohnPiatt", "Jp19414281") or die(mysql_error());
mysql_select_db('JohnPiatt', $query) or die(mysql_error());
 
if (isset ($_POST['customer'])) {
    
    $customer = ($_POST['customer']);
    
    foreach (($_POST['product_id']) as $ID){
        $sql = sprintf("SELECT customer FROM `customers_products` WHERE customer = '$customer' AND product_id = '$ID'");
          if($result = @mysql_query($sql)){
              if(!@mysql_num_rows($result)){
                  $sql = sprintf("INSERT INTO `customers_products` (customer, product_id) VALUES ('$customer','$ID')");
                 }
             }
            }
     } 
?>

Re: Checking for Dulplicate entries

Posted: Mon Dec 29, 2008 5:43 am
by requinix
Congratulations on remembering to remove your username and password. It'd be even better if you removed it from both of the scripts ;)

If you make the customer+product_id unique (either a primary key or a unique key) then MySQL won't let you insert a duplicate: if mysql_affected_rows is zero then it didn't insert.

Re: Checking for Dulplicate entries

Posted: Mon Dec 29, 2008 11:02 am
by Greg19
Thanks a bunch. And yeah I'll make sure to check both scripts next time lol