[PHP/MYSQL] vreemd LIKE probleem

[Helmet]

Ik loop een beetje tegen een vreemd probleem aan en vroeg me af of iemand al hetzelfde probleem heeft gehad:

Ik heb een tabel waarin informatie staat over een inschrijving van een deelnemer aan een festival. Deze tabel heeft ook een veld `tags` waar gebruikers comma-gescheiden tags over zichzelf kunnen invoeren. Deze tags staan gewoon als tekst in de database (in hetzelfde veld en nee dat was niet mijn keuze):

Omdat er relatief weinig records zijn en ik de 50% treshold van FULLTEXT search om die reden niet wil gebruiken heb ik besloten om met een eenvoudige like te zoeken in het veld `tags`:

De database heeft trouwens een encoding van UTF-8 en is ingesteld op de CI (case insensitive) mode:

Nu voer ik de twee volgende queries uit:

SQL:

SELECT subscription_id, naam, confirmed, websites, tags, picture FROM `subscriptions` WHERE tags like '%test%' LIMIT 0, 10

SQL:

SELECT subscription_id, naam, confirmed, websites, tags, picture FROM `subscriptions` WHERE tags like '%Test%' LIMIT 0, 10

Waarbij ik slechts bij de 2e query resultaat krijg, bij de eerste query return er helemaal niets, tenzij ik deze in PHPMYADMIN uit voer. Dan geven beiden queries hetzelfde en ook gewenste resultaat.

Een stukje relevante code:

PHP:

$tag = addslashes(urldecode($_GET['tag']));
$total = db_query(sprintf("SELECT COUNT(subscription_id) AS `total` FROM `subscriptions` WHERE tags LIKE '%%%s%%'", $tag));
$total = db_fetch_array($total);
$total = $total['total'];
if ($start > $total) { $start = 0; }
$sql = sprintf("SELECT subscription_id, naam, confirmed, websites, tags, picture FROM `subscriptions` WHERE tags LIKE '%%%s%%' LIMIT %d, %d", $tag, $start, $perpage);

Zie ik iets heel eenvoudigs over het hoofd?

[ Voor 4% gewijzigd door Helmet op 13-04-2009 23:15 ]

[Verwijderd]

Ik zag het verschil in de queries bijna over het hoofd. Blijkbaar vindt MySQL het verschil tussen %test% en %Test% te groot en besluit ze anders te behandelen. Vergeet ook niet dat case-sensitive met UTF-8 een hele nieuwe betekenis krijgt dus mogelijk dat daar het probleem inzit.

Ook vraag ik me af waarom het in PHPMyAdmin wel werkt. Mischien iets in hoe de connectie naar de database wordt opgezet. Daar kun je volgens mij zowel de charset, UTF-8 dus, als de case-senstive optie instellen en het zou kunnen dat die de default settings van de server overschrijven.

De beste oplossing is mischien wel om gewoon te zorgen dat je tags altijd lowercase zijn. Dit maakt je cache niet alleen een stuk efficienter maar ook dit soort grapen van de unicode tijd waarin we leven een stuk simpeler alhoewel ik niet weet of en hoe strtolower() omgaat met input van je aziatische bezoekers.

[Voutloos]

Helmet schreef op maandag 13 april 2009 @ 23:00:
De database heeft trouwens een encoding van UTF-8 en is ingesteld op de CI (case insensitive) mode

Dan staat de connectie of tabel definitie op iets anders.

Verwijderd schreef op dinsdag 14 april 2009 @ 04:54:
IDe beste oplossing is mischien wel om gewoon te zorgen dat je tags altijd lowercase zijn.

Dat is niet dé oplossing voor dit probleem, want dit kan best op een case insensitive manier. Dan nog kan het gezien de totaal vrije input wel een beetje helpen om de tags in ieder geval iets consistenter te houden.

dit soort grapen van de unicode tijd waarin we leven

Met unicode mag je aan genoeg zaken gaan denken, maar hoe met hoofdlettergebruik om gegaan moet worden is echt het probleem niet.

Overigens is het gebruik van addslashes() fout. Escapen is context afhankelijk. Hier is de context mysql en het gaat om een string, ergo gebruik mysql_real_escape_string() .

[HuHu]

Zet je de verbinding wel op UTF8 als je in je eigen script verbinding maakt?

PHP:

db_query('SET NAMES utf8;');

[Fugitive1977]

Ik heb in een dergelijk geval in het verleden ook wel eens de functie upper() of lower() van mysql gebruikt.
In jou geval dus zoiets:

PHP:

$qry = "SELECT subscription_id, naam, confirmed, websites, tags, picture FROM `subscriptions` WHERE upper(tags) like upper('%Test%') LIMIT 0, 10;;

Alle mogelijk combinaties van hoofdletters en kleine letters zouden dan gematched moeten worden.

[Voutloos]

Daar heb je de case insensitive collation dus voor...

[Skidmarks]

Kijk eens wat je huidige connectie collation is met deze query:

SQL:

SELECT @@collation_connection

Je zal waarschijnlijk na het maken van een connectie steeds de juiste collation en charset willen zetten met deze query:

SQL:

SET NAMES 'utf8' COLLATE 'utf8_general_ci'

Zie voor meer info
http://dev.mysql.com/doc/...n/charset-connection.html

[Helmet]

Voor de volledigheid:

Het probleem lag hem niet in de verschillende queries, maar in de database abstraction layer van in dit geval Drupal (die automatisch gegevens insert in een '%s' veld). Dit is waarschijnlijk de reden waarom %b en %B (in het geval van mijn probleem)) enerzijds wel en niet replaced worden.

Bedankt voor de feedback kerels :-)

[Voutloos]

Dat is dan de belangrijkste te leren les: bekijk altijd de letterlijk uitgevoerde query.
In je topicstart lijk je de letterlijke queries te noemen, en dat is waarom hier niet eerder op gewezen werd.