Identical Rows Problem

Anon

We have a database with the following fields;

Name-----Type
ID-----int(11)
title-----text
interest-----text

I have done a DISTINCT SELECT command on 'interest' and it pulls up 14 entries, two of which appear to be exactly the same ('Rubber Stamping') these link to one record.

HOW can there be two DISTINCT 'interest' when they are both entered as the same?

SOLUTION I tried deleting the record that these 'interest' link to. The duplication just moved on to the next row number. Do we have a corrupted table? Is there a solution?

Cheers Oli

[deleted]

SELECT DISTINST(field)

will always return only unique results.
If you see one result twice, there must be something different about one of them. Perhaps one has a trailing space?

You can try to cehck/fix your tables, check the manual of your database.

Anon

Hi Vincent,

I did a select on 'Rubber Stamping ' as apposed to 'Rubber Stamping' and it pulled up 1 record, tried to edit but received an error as follows;

Duplicate entry '9' for key 1

Any ideas? Its MySQL database.

oli

[deleted]

Looks like you defined that column to be 'UNIQUE', and removing the trailing space makes it the same as the other entry, which the UNIQUE index will not allow.

Anon

I dont believe i have the text column 'interest' setup as an index or UNIQUE, or else i dont know how to find out?

SHOW INDEX FROM tbl_name

Indexes
Keyname-Unique-Field-Action
PRIMARY-Yes-ID-Drop

ID-No-ID-Drop

DESCRIBE tbl_name
Field-Type-Null-Key-Default-Extra

ID-int(11)-PRI-auto_increment

title-text

intro-text

html-text

interest-text

occasion-text

new-int(11)---0

pdf-int(11)---0

[deleted]

Then how did you try to remove the trailing space?

Anon

How did i remove the trailing space? Via phpMyAdmin, editing the field manually.

[deleted]

Odd that you should get a duplicate key value when the value you changes is not a key.

Oh well, you did use phpmyadmin after all...

essexboyracer

hmmm...my coding sucks.... sorry for the wild goose chase vincent

oli