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

2004-02-04 Thread Jas
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]

2004-02-04 Thread 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


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

2004-02-04 Thread John W. Holmes
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]

2004-02-04 Thread Jas
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]

2004-02-04 Thread John W. Holmes
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]

2004-02-04 Thread Jas
[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