Hi 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 mysql_affected_rows() http://www.mysql.com/doc/en/mysql_affected_rows.html if the row was inserted then all is well, otherwise ask to resubmit I think this should still work with auto ids, but check. HTH Peter -----Original Message----- From: Jas [mailto:[EMAIL PROTECTED] Sent: 30 December 2003 22:32 To: [EMAIL PROTECTED] Subject: [PHP-DB] Checking for duplicate records before update? Problem. I have a database table that looks like this: eg. +------+----------+-------------------+---------------+---------+ | id | hostname | mac | ip | vlan | +------+----------+-------------------+---------------+---------+ | 1014 | MTPC-01 | 00:02:B3:A2:9D:ED | 155.97.15.11 | Vlan-29 | | 1015 | MTPC-02 | 00:02:B3:A2:B6:F4 | 155.97.15.12 | 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: eg. $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") { unset($_SESSION['search']); 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) { unset($_SESSION['search']); 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 advance. Jas -- 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
