This is an alternate approach, and may be more elegant and efficient

Define hostname, mac, and ip fields as UNIQUE.

then INSERT IGNORE into the table. Check to see if a  row was inserted using


if the row was inserted then all is well, otherwise ask to resubmit

I think this should still work with auto ids, but check.



-----Original Message-----
From: Jas [mailto:[EMAIL PROTECTED]
Sent: 30 December 2003 22:32
Subject: [PHP-DB] Checking for duplicate records before update?

Problem. I have a database table that looks like this:
| id   | hostname | mac               | ip            | vlan    |
| 1014 | MTPC-01  | 00:02:B3:A2:9D:ED |  | Vlan-29 |
| 1015 | MTPC-02  | 00:02:B3:A2:B6:F4 |  | Vlan-29 |

This table will hold a very large number of entries, all unique.  I have
created a simple HTML form which updates the entries but before it does
it checks to see if records already exist that contain either the same
IP value, the same MAC value or the same Hostname value.  If any of
these items exist it currently discards the posted informaiton and
prompts the user to re-enter because I cannot have duplicate entries of
either the hostname, mac, ip fields.

Here is my function:
$x = mysql_query("SELECT * FROM $table WHERE hostname =
'$_POST[hostname]' OR ip = '$_POST[ip]' OR mac = '$_POST[mac]' NOT id =
'$_SESSION[id01]'")or die(mysql_error());
$num = mysql_num_rows($x);
   if($num == "0") {
     require 'dbase.inc.php';
     $table = "hosts";
     $sql = @mysql_query("UPDATE $table SET hostname =
\"$_POST[hostname]\", mac = \"$_POST[mac]\", ip = \"$_POST[ip]\", vlan =
\"$_POST[vlan]\" WHERE id = \"$_SESSION[id01]\"")or die(mysql_error());
     echo "Form worked!";
} elseif ($num != 0) {
   echo "Form didn't work because 1 of the 3 fields were present in
another record!!! Please try again.";
} else {
   echo "RTM again please!"; }

I think what I really need to know is if there is a quick way to sort
through the results of the current database records and do comparisons
against the form.  If any one has done something like this before could
you please show me an example or point me to a good resource.  Thanks in

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to