Solved

Inserting only unique into MySQL

Posted on 2004-09-11
4
1,462 Views
Last Modified: 2012-08-13
I want to build a table in mysql of all of the unique url's that are accessing my script.  I don't want to do an INSERT for every record, as that will cause double records to be formed.   Is setting the table field which will hold the url as a primary key the way to do this?  I think mysql generates an error if you try to insert a duplicate primary key, so this doesn't feel like the most elegant way to build a table that contains only one of each value.  I'd like to avoid having to do a table lookup and then an insert, if possible.
0
Comment
Question by:siteup
4 Comments
 
LVL 1

Expert Comment

by:theandrew
ID: 12035487
I have a script that does this.

Here are two ways of doing it:
1) First, do a DELETE in the database, to delete all of the records from the database that match that IP and that URL. Then do your insert. If you have a date field you will be able to see the last date/time that they were at that page.

2) Insert it anyways, leave the duplicates there (nice later for detailed reporting). Adjust your script for reporting the number of hits or whatever to: SELECT DISTINCT IP,URL FROM........   this will only pull the unique ip and url page combinations and wont show the duplicates.

Additionally:
If you did a SELECT URL,IP,COUNT(IP) AS COUNT from table GROUP BY IP,URL......

This would show you a count of how many times that person has viewed that page.

If you post your actual table names and fields i can spell out the exact code for ya.
0
 
LVL 3

Expert Comment

by:pat5star
ID: 12035555
You can add a unique index to your URL column which will then only allow unique values to be inserted. Then you have 2 options: 1) you can either query the database first to see if it contains the URL you will be attempting to insert or 2) you can just insert each time and catch the error that MySQL will produce if it already contains that URL.

Myself, I would choose option number 2. This way you aren't wasting time or effort querying the database first and your guaranteed that you won't have duplicate values.

-Pat
0
 
LVL 15

Expert Comment

by:JakobA
ID: 12036074
You can use the IGNORE keyword in your INSERT statement, then data rows containing an existing primary key value or unique index value are just ignored and not inserted.
see: http://dev.mysql.com/doc/mysql/en/INSERT.html
0
 
LVL 2

Accepted Solution

by:
afano earned 500 total points
ID: 12040201
What you need is REPLACE instead of INSERT.

REPLACE, if it finds a pre-existing primary key will replace the row.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question