CODEWITHSUNDEEP
mysqlsql
MirrorMirror
MirrorMirror

Reputation: 188

mysql query returns two results instead of one

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 ).

Upvotes: 0

Views: 128

Answers (1)

Matt
Matt

Reputation: 3363

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

Upvotes: 1

Related Questions