For this table to create 3 unique keys I did the following, in case it helps someone else out.Sorry, but now I have one more question about finding the exact field for a duplicate entry...
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| hostname | varchar(100) | | | | |
| mac | varchar(100) | | | | |
| ip | varchar(100) | | | | |
| vlan | varchar(100) | | | | |
+----------+--------------+------+-----+---------+----------------+
UPDATE TABLE hosts ADD UNIQUE mac (mac); UPDATE TABLE hosts ADD UNIQUE hostname (hostname); UPDATE TABLE hosts ADD UNIQUE ip (ip);
+----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | hostname | varchar(100) | | UNI | | | | mac | varchar(100) | | UNI | | | | ip | varchar(100) | | UNI | | | | vlan | varchar(100) | | | | | +----------+--------------+------+-----+---------+----------------+
Now I have used the following to check if duplicate records exist before updating:
<?php
// Try and update with posted fields form html form
$update = mysql_query("UPDATE hosts SET hostname='$_POST[hostname]', mac='$_POST[mac]', ip='$_POST[ip]', vlan='$_POST[vlan]' WHERE id='$_SESSION[id]'",$db);
$rows = mysql_affected_rows();
// Check results of operation if($rows == 0) { echo "No matching records found"; } else { echo "Matching records found"; }
Hope this helps anyone else, and thanks for the tip on MySQL's UNIQUE field, wish I would have known it sooner, I wouldn't be so pissed off from frustration.
Jas
for instance, say you change the mac and hostname and there is a record in the database with the same mac string, how can I flag the field that matched from the 3?
Thanks,
jas
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
