Gossamer Forum: Products: Gossamer Links: Development, Plugins and Globals: GT::SQL::Condition, LIKE, and foreign charachters?

Jun 16, 2004, 4:08 AM

Andy

Veteran / Moderator (18441 posts)

Jun 16, 2004, 4:08 AM

Post #1 of 12

Shortcut

GT::SQL::Condition, LIKE, and foreign charachters?

Hi guys and gals. I'm just trying to figure out if LSQL's GT::SQL::Condition routine supports different languages?

For example, if I search for "öéëïîåíßá" (greek), the category adverts stuff should find a match.

Code:
     my $cond = GT::SQL::Condition->new("Keywords", "LIKE", "%$Keyword%"); 
     $sth2 = $table2->select( $cond ) || return $GT::SQL::error;

.. which should equate to;

Code:
     my $cond = GT::SQL::Condition->new("Keywords", "LIKE", "%öéëïîåíßá%"); 
     $sth2 = $table2->select( $cond ) || return $GT::SQL::error;

...however, it doesn't seem to find the results? Am I missing something really simple here?

TIA for any replies.

Cheers

Andy (mod)
andy@ultranerds.co.uk

Jun 16, 2004, 11:46 PM

zaaron

Novice (41 posts)

Jun 16, 2004, 11:46 PM

Post #2 of 12

Shortcut

Re: [Andy] GT::SQL::Condition, LIKE, and foreign charachters? In reply to

Just a quick tip.

Maybe you should try playng with charset encoding of the mysql database. Crazy

zaaron

---------
GetLokal Reviews

Jun 17, 2004, 1:07 AM

Andy

Veteran / Moderator (18441 posts)

Jun 17, 2004, 1:07 AM

Post #3 of 12

Shortcut

Re: [zaaron] GT::SQL::Condition, LIKE, and foreign charachters? In reply to

Thanks for the tip. I've aready done that (set it to a greek charset). The problem is, that its not passing the query along correctly to GT::SQL::Condition :(

Cheers

Andy (mod)
andy@ultranerds.co.uk

Jun 21, 2004, 8:50 AM

Andy

Veteran / Moderator (18441 posts)

Jun 21, 2004, 8:50 AM

Post #4 of 12

Shortcut

Re: [Andy] GT::SQL::Condition, LIKE, and foreign charachters? In reply to

*bump*

Cheers

Andy (mod)
andy@ultranerds.co.uk

Jun 21, 2004, 11:50 AM

Jagerman

Staff (2198 posts)

Jun 21, 2004, 11:50 AM

Post #5 of 12

Shortcut

Re: [Andy] GT::SQL::Condition, LIKE, and foreign charachters? In reply to

Are you sure there is something actually containing that string? I just tested GT::SQL::Condition, and it did a proper select.

Jason Rhinelander
Gossamer Threads
jason@gossamer-threads.com

Jun 22, 2004, 7:50 AM

Andy

Veteran / Moderator (18441 posts)

Jun 22, 2004, 7:50 AM

Post #6 of 12

Shortcut

Re: [Jagerman] GT::SQL::Condition, LIKE, and foreign charachters? In reply to

Thanks for the reply Jason :)

I just tried this.. and it looks like its down to Unicode::MapUTF8. Below are the main sections I'm using (I've cut out the middle sections, which don't have any reason to be checked, as they don't interact with the $Keyword or condition);

Code:
    use Unicode::MapUTF8 qw(to_utf8 from_utf8 utf8_supported_charset); 
...1....
    $Keyword =~ s/([\200-\377]+)/from_utf8({ -string => $1, -charset => 'ISO-8859-1'})/eg; 
...2...
     $table2 = $DB->table('CatAdverts'); 
     $table2->select_options('ORDER BY RAND()',"LIMIT $NumberOfAdverts") || return $GT::SQL::error; 
     my $cond = GT::SQL::Condition->new("Keywords", "LIKE", "%$Keyword%") || return $GT::SQL::error; 
     $sth2 = $table2->select( $cond ) || return $GT::SQL::error;

Between "...1..." (red) putting print $Keyword; between this section shows it up fine; but when you put it where the "...2..." part is, it just shows up blank :(

I've also tried 'ISO-8859-7' to no avail :(

TIA for your help.

Cheers

Andy (mod)
andy@ultranerds.co.uk

Jun 22, 2004, 1:28 PM

Jagerman

Staff (2198 posts)

Jun 22, 2004, 1:28 PM

Post #7 of 12

Shortcut

Re: [Andy] GT::SQL::Condition, LIKE, and foreign charachters? In reply to

The module isn't doing what you think:

perl -e 'use Unicode::MapUTF8 qw/to_utf8 from_utf8 utf8_supported_charset/; $foo = from_utf8({ -string => "öéëïî", -charset => "ISO-8859-1" });'

$foo ends up blank.

Jason Rhinelander
Gossamer Threads
jason@gossamer-threads.com

Jun 23, 2004, 12:37 AM

Andy

Veteran / Moderator (18441 posts)

Jun 23, 2004, 12:37 AM

Post #8 of 12

Shortcut

Re: [Jagerman] GT::SQL::Condition, LIKE, and foreign charachters? In reply to

Quote:

Hi Jason. Thanks for the reply.

Any idea how I can correctly parse it, or shouldn't it need to?

Cheers

Andy (mod)
andy@ultranerds.co.uk

Jun 23, 2004, 5:00 PM

Jagerman

Staff (2198 posts)

Jun 23, 2004, 5:00 PM

Post #9 of 12

Shortcut

Re: [Andy] GT::SQL::Condition, LIKE, and foreign charachters? In reply to

My guess would be that it doesn't need it.

Jason Rhinelander
Gossamer Threads
jason@gossamer-threads.com

Jun 24, 2004, 12:22 AM

Andy

Veteran / Moderator (18441 posts)

Jun 24, 2004, 12:22 AM

Post #10 of 12

Shortcut

Re: [Jagerman] GT::SQL::Condition, LIKE, and foreign charachters? In reply to

Could I send over the admin panel/FTP so you can have a look? I've tried it without, and although it shows ok in the browser (when just printing the word out), it still gets no results :(

Cheers

Andy (mod)
andy@ultranerds.co.uk

Jul 1, 2004, 4:02 AM

Andy

Veteran / Moderator (18441 posts)

Jul 1, 2004, 4:02 AM

Post #11 of 12

Shortcut

Re: [Jagerman] GT::SQL::Condition, LIKE, and foreign charachters? In reply to

Thanks for the feedback Jason. I finally managed to get it working with foreign charachters :)

One problem I am still having though, is case sensitivity in the query. Is there a built in function for GT::SQL::Condition, that will let me check both cases? i.e;

Education
and
education
and
EduCATION

...etc.

TIA

Andy (mod)
andy@ultranerds.co.uk

Jul 1, 2004, 1:51 PM

Jagerman

Staff (2198 posts)

Jul 1, 2004, 1:51 PM

Post #12 of 12

Shortcut

Re: [Andy] GT::SQL::Condition, LIKE, and foreign charachters? In reply to

Not easily - but as long as the columns are non-binary CHAR/VARCHAR's, or TEXT fields, the query should always be case-insensitive.

Jason Rhinelander
Gossamer Threads
jason@gossamer-threads.com