Not showing duplicate rows

netfrugal

How do I query the count and also disregard duplicate rows - based on one particular column?

"SELECT COUNT(*) FROM Table1 WHERE date_created = '$today'";

Is this where I use DISTINCT?

Also, is there a way to avoid accidental duplicate entries?

dougal85

yes, this is where you use DISTINCT.

SELECT DISTINCT COUNT ....

do you mean duplicate entries when you insert? or when you are taking data out.

netfrugal

I mean both.

Is there a way to stop accidental duplicates? Like tracking a visitor on a site. If a visitor refreshes the page, then the script inserts another entry. which is essentially the same entry.

And if there are duplicates, then I want to filter them out. The one thing I see as maybe an issue is that I have a timestamp. So the record isn't really a pure duplicate.

See my dilemma?

bpat1434

INSERT INTO .... ON DUPLICATE UPDATE ...

That's to prevent duplicate entries. Instead of duplicating it, you'd use the Update syntax to update whatever has changed.

If you're tracking users, you should not necessarily track by timestamp, rather Session ID or even DB ID for that session ID. This way, you can select a UNIQUE ID / Session identifier. If it's in the table, update it, if not, insert it.

NogDog

bpat1434 wrote:
INSERT INTO .... ON DUPLICATE UPDATE ...

That's to prevent duplicate entries. Instead of duplicating it, you'd use the Update syntax to update whatever has changed.

If you're tracking users, you should not necessarily track by timestamp, rather Session ID or even DB ID for that session ID. This way, you can select a UNIQUE ID / Session identifier. If it's in the table, update it, if not, insert it.

Just a note: it's "ON DUPLICATE KEY UPDATE", and it requires that you have a primary key and/or other unique index on a column (or group of columns) that would generate the duplicate key condition.