Re: [PHP-DB] multiple fields all unique? [almost solved]
Jas wrote: For this table to create 3 unique keys I did the following, in case it helps someone else out. +--+--+--+-+-++ | 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 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] multiple fields all unique? [almost solved]
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. +--+--+--+-+-++ | 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 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] multiple fields all unique? [almost solved]
From: Jas [EMAIL PROTECTED] 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? Your update will actually fail in the case, so you need to catch the error with mysql_error() (and maybe mysql_errno()) and examine it to see which key (or index) was matched (a unique column is an index). When you update the table with an existing mac value, the error will be similar to Duplicate value for Key XX where XX is what key was duplicated. I can't remember if the keys start at zero or one, but your ID column will be the first key, then mac, hostname, and finally ip (in the order they were created). So, if you examine the result of mysql_error() and it say duplicate for key 2, then the mac column was duplicated. Although this sounds a little harder than doing a SELECT prior to and just comparing values, it lets you do this with just a single query and only have extra processing upon errors instead of every single update. If you need a working example, let me know. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] multiple fields all unique? [almost solved]
John W. Holmes wrote: [snip] When you update the table with an existing mac value, the error will be similar to Duplicate value for Key XX where XX is what key was duplicated. I can't remember if the keys start at zero or one, but your ID column will be the first key, then mac, hostname, and finally ip (in the order they were created). So, if you examine the result of mysql_error() and it say duplicate for key 2, then the mac column was duplicated. Although this sounds a little harder than doing a SELECT prior to and just comparing values, it lets you do this with just a single query and only have extra processing upon errors instead of every single update. [/snip] Your right, I am able to see which record matches (not the record I am updating) I think you just answered my question by stating that Although this sounds a little harder than doing a SELECT prior to and just comparing values, it lets you do this with just a single query and only have extra processing upon errors instead of every single update. I suppose that is what I am going to have to do before doing the update. If you know of a different method please elaborate. thanks again, Jas -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] multiple fields all unique? [almost solved]
From: Jas [EMAIL PROTECTED] 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, You're not going to be able to fetch anything from the result set because you're excuting an UPDATE query, not a SELECT. You also do not want to die() when the query fails, otherwise you won't be able to react to the error. Execute the query, then check mysql_error() for a value. If it contains a value, then the query failed more than likely because of a duplicate key. The format of the error message is always the same, Duplicate entry '' for key , where is the value that was duplicated and is the key number. It looks like key 41062 is the IP column. So you can look for the 41062 and display a message about duplicate IP. If it were me, I'd just match the value between single quotes, and echo a message such as Duplicate value . The difference between a mac, ip, and hostname are pretty obvious, so you don't really _have_ to tell the user which one it is. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] multiple fields all unique? [almost solved]
[snip] You're not going to be able to fetch anything from the result set because you're excuting an UPDATE query, not a SELECT. You also do not want to die() when the query fails, otherwise you won't be able to react to the error. Execute the query, then check mysql_error() for a value. If it contains a value, then the query failed more than likely because of a duplicate key. [snip] Well looks like I need to brush up on my php - mysql functions. =) For anyone that has been following this thread here is a simple way to catch the duplicate and show the user the contents: ?php $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(); if([EMAIL PROTECTED]($update)) { echo No match found; } else { echo Match found; $error = preg_match(/^[']$/,mysql_errno($update)); $sql = mysql_query(select * from hosts where id = $error) while($x = mysql_fetch_array($sql)) { list($id,$host,$mac,$ip,$vlan) = $x; } } ? Might want to check the preg_match string to look for everything between ' and ' but other than that you should be able to list the record that your update statement is having a problem with. HTH Jas -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php