RE: Preventing Duplicate Entries
Is it possible to use a primary key to avoid duplicates? The reaction to duplicate rows will depend on what type of statement you are issuing. An update/delete would update/delete all rows that are relevant, a select would return multiple rows with the same values. -Original Message- From: Axel IS Main [mailto:[EMAIL PROTECTED] Sent: Sunday, March 21, 2004 10:25 PM To: [EMAIL PROTECTED] Subject: Preventing Duplicate Entries I have a php app that updates an ever growing table with new information on a regular basis. Very often the information is duplicated. I'm currently handling this by checking the table for duplicate values every time I go to add new data. As you can imagine, as the table grows it takes longer and longer for this to happen, and the process gets slower and slower. In order to speed things up I'm wondering of it might not be a good idea to not allow duplication in a given field. The question is, if there is a duplicate, how will MySQL react? And what's the best way to manage that reaction? Also, will this actually be faster than doing it the way I'm doing it now? Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Preventing Duplicate Entries
I have a php app that updates an ever growing table with new information on a regular basis. Very often the information is duplicated. I'm currently handling this by checking the table for duplicate values every time I go to add new data. As you can imagine, as the table grows it takes longer and longer for this to happen, and the process gets slower and slower. In order to speed things up I'm wondering of it might not be a good idea to not allow duplication in a given field. The question is, if there is a duplicate, how will MySQL react? And what's the best way to manage that reaction? Also, will this actually be faster than doing it the way I'm doing it now? Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Preventing Duplicate Entries
On Sun, Mar 21, 2004 at 07:24:48PM -0800, Axel IS Main wrote: I have a php app that updates an ever growing table with new information on a regular basis. Very often the information is duplicated. I'm currently handling this by checking the table for duplicate values every time I go to add new data. As you can imagine, as the table grows it takes longer and longer for this to happen, and the process gets slower and slower. In order to speed things up I'm wondering of it might not be a good idea to not allow duplication in a given field. The question is, if there is a duplicate, how will MySQL react? And what's the best way to manage that reaction? Also, will this actually be faster than doing it the way I'm doing it now? Perhaps you could hash all the field values into a single 32bit value, then check for that value in the hash field. You might get a false positive, but they will be few and far between. -- Jim Richardson http://www.eskimo.com/~warlock The race isn't always to the swift, nor the battle to the strong, But it's the safest way to bet. signature.asc Description: Digital signature
Re: Preventing Duplicate Entries
Axel IS Main wrote: I have a php app that updates an ever growing table with new information on a regular basis. Very often the information is duplicated. I'm currently handling this by checking the table for duplicate values every time I go to add new data. As you can imagine, as the table grows it takes longer and longer for this to happen, and the process gets slower and slower. In order to speed things up I'm wondering of it might not be a good idea to not allow duplication in a given field. The question is, if there is a duplicate, how will MySQL react? And what's the best way to manage that reaction? Also, will this actually be faster than doing it the way I'm doing it now? Nick Since the process is getting noticeably slower, I would guess you don't have an index on the columns in question. You may be able to speed up your current process to an acceptable level just by adding an appropriate index. Better yet, if you add a UNIQUE INDEX to the appropriate column or group of columns, mysql will reject duplicates with ERROR 1062: Duplicate entry I'm guessing your current code is something like this: query mysql table for duplicate if no duplicate insert new data in mysql table if mysql returns error handle the error else whatever you do with duplicates Once you add the unique index, you can change it to something like this: insert new data if mysql returns error if error is duplicate row whatever you do with duplicates else handle other errors If you need help with the index, put EXPLAIN in front of the SELECT query you currently use to check for duplicates and post the result. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Beginner question - Preventing Duplicate Entries
You could make the column a unique key...that would prevent duplicates from being entered. Then if you want to be able to try inserting duplicates (like if you don't want the query to fail on duplicate attempts), you could do INSERT IGNORE INTO myTable ... Check http://www.mysql.com/doc/en/CREATE_TABLE.html and http://www.mysql.com/doc/en/ALTER_TABLE.html for information about keys. -Brian McCain - Original Message - From: Wileynet [EMAIL PROTECTED] To: 'mysql users' [EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 3:03 PM Subject: Beginner question - Preventing Duplicate Entries Is there a sql statement that would not allow the same entry twice. Something like INSERT into myTable s WHERE s != Value(?). I don't know if that makes sense but I thought I would give it a shot. Basically I want to know if it is possible and if so can you point me to a webpage or give me an example if you can? Thanks in advance - Wiley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Beginner question - Preventing Duplicate Entries
Is there a sql statement that would not allow the same entry twice. Something like INSERT into myTable s WHERE s != Value(?). I dont know if that makes sense but I thought I would give it a shot. Basically I want to know if it is possible and if so can you point me to a webpage or give me an example if you can? Thanks in advance - Wiley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Beginner question - Preventing Duplicate Entries
If you wanted to use MyISAM tables and peform an initial select to determine whether you should insert, you could lock the table you would be selecting/insert from. 'LOCK TABLES table_name WRITE' Don't forget to unlock the table when you are done 'UNLOCK TABLES', otherwise you will likely have deadlocks. ~Wynne -Original Message- From: Wileynet [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 2:04 PM To: 'mysql users' Subject: Beginner question - Preventing Duplicate Entries Is there a sql statement that would not allow the same entry twice. Something like INSERT into myTable s WHERE s != Value(?). I don't know if that makes sense but I thought I would give it a shot. Basically I want to know if it is possible and if so can you point me to a webpage or give me an example if you can? Thanks in advance - Wiley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Beginner question - Preventing Duplicate Entries
Add a UNIQUE INDEX to the table you are inserting to. You will then get an error if you try to insert a second time with the same values. Alternativly you could use perform a select and then an insert within the same transaction, determining whether something exists in the db before inserting. This of course would require transactions supported by InnoDB tables and not supported by MyISAM. ~Wynne -Original Message- From: Wileynet [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 2:04 PM To: 'mysql users' Subject: Beginner question - Preventing Duplicate Entries Is there a sql statement that would not allow the same entry twice. Something like INSERT into myTable s WHERE s != Value(?). I don't know if that makes sense but I thought I would give it a shot. Basically I want to know if it is possible and if so can you point me to a webpage or give me an example if you can? Thanks in advance - Wiley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
preventing duplicate entries
I want to prevent duplicate entries into my mysql database which holds
invertory of all network eqpt at my site. Engineers access it thru
netscape and the query insert is done via pdp [below].
I will place the script into php, any pointers on this would be
appreciated.
THX!
Pete
html
body
?php
require(fedb.inc);
mysql_connect(localhost,$user,$password);
@mysql_select_db($db) or die( Unable to select database);
/*Insert into database */
mysql_query (INSERT INTO asset (site_id, hostname, device, model,
serial, ip,
dept, mhz, ram, hd_size, os_ver, status, data_port,
pp_port, hub_port,
digi_port, csmim_port, tprmim_port, xyplex_port,
comments, fe)
VALUES ('$site_id', '$hostname', '$device',
'$model', '$serial', '$ip',
'$dept', '$mhz', '$ram', '$hd_size',
'$os_ver', '$status',
'$data_port', '$pp_port', '$hub_port',
'$digi_port',
'$csmim_port', '$tprmim_port',
'$xyplex_port',
'$comments', '$fe')
);
print (Thanks for submitting your device.);
?
a href='index.html'bHome/b/a
/body
/html
___
Pete Kuczynski
Principal Field Engineer
DHL Airways Inc.
Infrastructure Technology Services
(773)-462-9758
24/7 Helpdesk 1-800-434-5767
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: preventing duplicate entries
-- Original Message -- From: Pete Kuczynski [EMAIL PROTECTED] Date: Tue, 26 Jun 2001 13:19:02 -0500 I want to prevent duplicate entries into my mysql database which holds invertory of all network eqpt at my site. Engineers access it thru netscape and the query insert is done via pdp [below]. I will place the script into php, any pointers on this would be appreciated. Why not make your inventory identifier field a unique index: ie: alter table inventory add unique itemid (itemid); Now, whenever someone tries to create a duplicate item identifier, MySQL will issue an error, which your script can capture. I don't use PHP, but do use VB through MyODBC, and this is how I catch unwanted duplicates. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: preventing duplicate entries
My primary key field is a auto incremented index, the field I want to make unique, or to say, prevent duplicates in, is the hostname field, which has, hostnames of devices. Is the key word UNIQUE then? Not all of the devices have hostnames assigned to them, like terminals, so the hostname field is blank in many cases. If I make that field UNIQUE, will that cause a problem, with multiple blank fields? Thx! Pete Kristopher Briscoe wrote: What does your table definition look like? What fields are you looking to make unique? All you need to do is set one or multiple combined fields to UNIQUE or Primary keys. This will prevent duplicats. Another way would be to query the table for the information that is going to be inserted and see if you get a row returned. If so, then let the FE know. I would combine both methods for optimal assurance. On another note, I have been very interested in working with DHL. At one point I was speaking with someone in your HR department, but they have seen departed. I know this is odd, but if I forwared you my resume do you think you could pass it on to your internal recruiting staff? I am a Sr. SUN Solaris Engineer with more than 10years experience if that helps at all. Good luck with your work. Kris- _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- ___ Pete Kuczynski Principal Field Engineer DHL Airways Inc. Infrastructure Technology Services (773)-462-9758 24/7 Helpdesk 1-800-434-5767 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: preventing duplicate entries
-- Original Message -- From: Pete Kuczynski [EMAIL PROTECTED] Date: Tue, 26 Jun 2001 14:02:08 -0500 My primary key field is a auto incremented index, the field I want to make unique, or to say, prevent duplicates in, is the hostname field, which has, hostnames of devices. Is the key word UNIQUE then? Not all of the devices have hostnames assigned to them, like terminals, so the hostname field is blank in many cases. If I make that field UNIQUE, will that cause a problem, with multiple blank fields? I believe that a unique index field will permit Null values. Aaron (sql - these filters are really annoying!) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
