Re: multiple fields all unique? [almost solved]

[Jas <jason.gerfen@xxxxxxxxxxxx>]

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