RE: Checking for duplicate records before update?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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:jason.gerfen@xxxxxxxxxxxx]
Sent: 30 December 2003 22:32
To: php-db@xxxxxxxxxxxxx
Subject:  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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux