mysql query returns two results instead of one

Question

I noticed this because for some words it returned the wrong etymology. So I noticed that the query returns many results instead of one. the table has about 60000 entries.

so here is my mysql query:

conMySQL.ConnectionString = "Provider=MSDASQL; DRIVER={MySQL ODBC 3.51 Driver};SERVER=" & MYSQLserverIP & ";Port=" & MYSQLserverPort & ";DATABASE=" & MYSQLserverDB & ";UID=" & MYSQLserverUser & ";PASSWORD=" & MYSQLserverPass & ";OPTION=3;Connect Timeout=1;"
conMySQL.Open
If conMySQL.State = 1 Then
    recMySQL.Open "SELECT * FROM tEtymology WHERE sLemma='" & sLemma & "'", conMySQL, adOpenStatic, adLockReadOnly
    If Not (recMySQL.EOF And recMySQL.BOF) Then
        recMySQL.MoveFirst
        strReturn = sLemma & "<br>" & recMySQL.Fields("id").Value & "#" & recMySQL.Fields("sLemma").Value & "#" & recMySQL.Fields("sEtymology").Value
        recMySQL.MoveNext
        strReturn = strReturn & "<br>" & recMySQL.Fields("id").Value & "#" & recMySQL.Fields("sLemma").Value & "#" & recMySQL.Fields("sEtymology").Value
    End If
End If

for example, for the Lemma "μπότα" the result is:

μπότα
34288#μπότα#[<μσν. μπότα < γαλλ. botte]
38505#οπότε#[<αρχ. ὁπόταν, με επίδραση του ὅτε]

this happens for many words, not only for the one you see here. I tried removing the index in the sLemma field ( thought that the indexed entry is a hashed value and due to collision it becomes the same ), but no change. I also tried changing the collation of the sLemma column but nothing changed.

so what i understand from this is that for mysql μπότα=μπότα and μπότα=οπότε which doesn't make any sense. There is only one entry "μπότα" in the sLemma column.

Any ideas ?

edit: here is how the entries look in phpmyadmin

enter image description here

you may think it's the same entry ( the red ones with the IDs mentioned here ) but if you zoom the image you will see they are different letters ( first and last letter ).

try to select distinct FROM tEtymology WHERE sLemma='"...... — Just code
– Just code, Commented Nov 11, 2013 at 12:28
sLemma doesn't have a unique constraint. using DISTINCT doesn't change anything, still returns two results. — MirrorMirror
– MirrorMirror, Commented Nov 11, 2013 at 12:34
Try doing just 'select distinct sLemma FROM ...' rather than 'distinct *' - I am wondering whether this is a character encoding issue. Presumably you are using a database installation that supports these characters? Try creating a new table with just a single column and a unique constraint and inserting those two values? — Matt
– Matt, Commented Nov 11, 2013 at 12:50
Please note that you appear to be wide-open to SQL Injection - please use parameterized queries. — Clockwork-Muse
– Clockwork-Muse, Commented Nov 11, 2013 at 13:35

Matt · Accepted Answer · 2013-11-11 14:11:27Z

1

From the comments and testing, almost certainly a character encoding issue. Not sure which character set you are currently using but you could try something like 'greek' (ISO 8859-7 Greek) - other possibilities here: http://dev.mysql.com/doc/refman/5.1/en/charset-charsets.html

Some character charts for each character set here: http://collation-charts.org/mysql60/

There is also some stuff to note about setting the character set being used by your connection (not just the actual server) if changing the character set on the db doesn't solve it. Hope that helps!

EDIT

Apparently you can use the command

show variables like 'char%';

to see all your current character set encodings. I came across this blog on the topic which is not exactly the same, but quite interesting: https://www.bluebox.net/insight/blog-article/getting-out-of-mysql-character-set-hell

edited Nov 11, 2013 at 14:11

answered Nov 11, 2013 at 13:57

Matt

3,3736 gold badges34 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql query returns two results instead of one

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related