Gossamer Forum
Home : General : Databases and SQL :

mySQL Query

Quote Reply
mySQL Query
Here is the situation:

1. User enters 'Română' ($cat) to search for this subcat in DMOZ database.
2. However, 'Română' is encoded as 'Română' in database (as it is by default in DMOZ dump).
3. Tried the following code, but fails:

my $str = "SELECT `catid` FROM `dstructure` WHERE UCASE(name) = UCASE(CONVERT( _utf8 '$cat' USING latin1 ))";
my $sth = $dbh->prepare($str);
$sth->execute();
$catid = $sth->fetchrow_array();

Ideas? Tried various encoding of input, but no luck. Try another encoding method of dstructure.name?

----
Cheers,

Dan
Founder and CEO

LionsGate Creative
hoodPALS
I Can't Believe It's Not Google Project
Quote Reply
Re: [dan] mySQL Query In reply to
Hi Dan,

Did you submit your text as utf-8? I think with that, the text will be the same as the one in mysql?

Cheers,

Dat

Programming and creating plugins and templates
Blog
Quote Reply
Re: [tandat] mySQL Query In reply to
1. DMOZ cat structure file saved locally as UTF-8 using ASCII editor.
2. File uploaded (in ASCII) to server.
3. Call CGI-Perl script to import file to mySQL. Script also outputs each category. Română appears as Română.
4. After import, Română appears as Română in mySQL database.
* database and table use utf8_general_ci collation

----
Cheers,

Dan
Founder and CEO

LionsGate Creative
hoodPALS
I Can't Believe It's Not Google Project
Quote Reply
Re: [dan] mySQL Query In reply to
But was the connection setup to be in utf8 as well?

Adrian
Quote Reply
Re: [brewt] mySQL Query In reply to
Hmmm, that sounds like it. Like on of these?

$dbh = UTF8DBI->connect("DBI:mysql:$dbname:$dbhost", $dbuser, $dbpass);

or

$dbh->do('SET dstructure utf8');

----
Cheers,

Dan
Founder and CEO

LionsGate Creative
hoodPALS
I Can't Believe It's Not Google Project
Quote Reply
Re: [dan] mySQL Query In reply to
SET CHARACTER SET charset_name

Adrian
Quote Reply
Re: [brewt] mySQL Query In reply to
Brilliant, thanks! Database is as should be. Hate to go back to the well one more time, but when script queries the database for Top/World/Română, not found:

Code:
$sth = $dbh->prepare("SELECT `catid` FROM `dstructure` WHERE UCASE(name) = UCASE('Top/World/Română')");
$sth->execute();
$catid = $sth->fetchrow_array();

$catid undefined. But, if I enter that same SQL query via phpMyAdmin, it is found. Works for other cats (e.g., Top/World), so appears to be encoding related. Connection is setup as utf8.

----
Cheers,

Dan
Founder and CEO

LionsGate Creative
hoodPALS
I Can't Believe It's Not Google Project