Re: multiple fields all unique? [almost solved]
| From: | Jas | Date: | Wed, 04 Feb 2004 16:34:49 +0000 |
| Subject: | Re: multiple fields all unique? [almost solved] | ||
| References: | 1 2 3 4 5 6 7 8 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-32789@lists.php.net to get a copy of this message | ||
If I do this statement:
mysql_query("UPDATE hosts SET hostname=\"$_POST[hostname]\", mac=\"$_POST[mac]\", ip=\"$_POST[ip]\", vlan=\"$_POST[vlan]\" WHERE id=\"$_SESSION[id]\"",$db)or die(mysql_error() . mysql_errno());
I get this error:
Duplicate entry '128.110.22.139' for key 41062
I have tried using these types of checks with no success:
$update = mysql_query("UPDATE hosts SET hostname=\"$_POST[hostname]\", mac=\"$_POST[mac]\", ip=\"$_POST[ip]\", vlan=\"$_POST[vlan]\" WHERE id=\"$_SESSION[id]\"",$db)or die(mysql_error() . mysql_errno());
$rows = mysql_affected_rows();
while($match = mysql_fetch_assoc($update)) {
echo $match["hostname"];
echo $match["mac"];
echo $match["ip"]; }
if($rows == 0) {
echo "update worked";
} else {
echo "update didn't work"; }
And...
while($match = mysql_fetch_object($update)) {
And..
while($match = mysql_fetch_array($update)) {
So far everything I have tried will not allow me to find the exact field and contents of a record that matches an existing record in the database. See below for details on database structure etc.
Any help is appreciated,
jas
Jas wrote:
Jas wrote: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... 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| 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