Re: [PHP-DB] multiple fields all unique? [almost solved]

[Jas]

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