[PHP/MySql]Checken of kolom een woord bevat

[Donool]

Ik ben druk bezig met een systeempje om items op trefwoorden op te zoeken in een MySql database, werkt tot nu toe allemaal goed op 1 ding na, het zoeken op trefwoorden.

Ik heb een kolum in mijn database genaamd trefwoorden, bevat in dit voorbeeld 'woord1, test, woorden'


Mijn query is als volgt:

PHP:

$sql = DB::query("SELECT * FROM papieren WHERE naam LIKE %s OR trefwoorden LIKE %s ",$zoek, $zoek]);

Waarbij $zoek staat voor het trefwoord waar je naar zoekt, als ik zoek naar woord vind ik niets maar zoek ik naar de complete rij trefwoorden krijg ik wel een resultaat. Ik heb al geprobeerd om % tekens om mijn zoek variable te zetten (in de query) maar dan krijg ik alsnog geen resultaat.

Ik heb ook een FULL TEXT INDEX kolum gemaakt in een andere tabel om de functie WHERE MATCH AGAINST te proberen alleen daar krijg ik ook weer 0 resultaat. Die query is alsvolgt

SQL:

Select * from documenten where match(trefwoorden) against ('trefwoord')

Mijn vraag is nu of jullie mij kunnen helpen met de zoek functie, ik wil gewoon kijken of een kolum een (deel) van een woord bevat. Ik krijg het zelf niet voor elkaar dus hulp zou fijn zijn



Alvast bedankt!

[pedorus]

Het probleem is dat niet elke waarde zijn eigen vakje in de database heeft. In een goed genormaliseerd ontwerp had je 3 tabellen gehad. Deze tabel zonder de laatste kolom, 1 tabel met keywords, en een tabel om die met elkaar te koppelen. Even inlezen is dus het beste

[Matis]

Kijk ook even naar de String Matching documentatie van MySQL. https://dev.mysql.com/doc...comparison-functions.html
Let vooral op de wildcards en de aanhalingstekens.

[NMe]

Matis schreef op woensdag 30 mei 2012 @ 00:46:
Kijk ook even naar de String Matching documentatie van MySQL. https://dev.mysql.com/doc...comparison-functions.html
Let vooral op de wildcards en de aanhalingstekens.

Doe dat vooral niet en kijk naar de link die pedorus aanhaalt. Dit soort dingen met stringmatching doen is nooit echt performant en altijd vervelend om te programmeren. Gewoon normaliseren die hap, nu je de kans nog hebt om het zaakje om te gooien.

[RobIII]

True; eens met NMe en pedorus. Neemt echter niet weg dat een simpele like '%foo%' natuurlijk gewoon moet werken; albeit non-performant en non-optimaal. Ik ben geen PHP kenner, dus help me even: is dit PDO ofzo? Zit 't probleem niet in 't verkeerd/niet escapen van % of 't ontbreken van ' om de %s ofzo?

[ Voor 30% gewijzigd door RobIII op 30-05-2012 02:16 ]

[krvabo]

NMe schreef op woensdag 30 mei 2012 @ 01:23:
[...]

Doe dat vooral niet en kijk naar de link die pedorus aanhaalt. Dit soort dingen met stringmatching doen is nooit echt performant en altijd vervelend om te programmeren. Gewoon normaliseren die hap, nu je de kans nog hebt om het zaakje om te gooien.

Hoewel ik het in theorie met je eens ben, ligt dit ook deels aan je praktijk.
Ja, het mooiste en beste is normaliseren.

Laten we aannemen dat dit komt te draaien op een shared hosting-omgeving die flink oversold is (goedkoper) en dat er 2.000.000 items zijn, 1000 tags en elk item meerdere koppelingen heeft. Nou, voor een normaal ('dedicated') systeem is dat uiteraard geen probleem, ook niet met flink wat traffic, maar ik denk dat zo'n oversold hostingdatabaseje het best wel eens zwaar kan krijgen.

Granted, met zulke dingen kun je het beter achteraf denormaliseren naar een 'string' dan vooraf, maar ik durf niet een 'one size fits all' te hangen over dat het zoeken in een string in alle rijen nou gegarandeerd trager is dan vele koppelingen in een shared mysql-database.

(Overigens doe ik het zelf uiteraard wel netjes )

[NMe]

Een site met 2 miljoen records die aan duizend verschillende tags gekoppeld moeten worden op een oversold shared hosting-bak draaien is al een fout op zich. En dan nog: dat zijn aantallen van niks, met de juiste indices is normaliseren op zo'n trage bak die jij omschrijft waarschijnlijk zelfs sneller.

RobIII schreef op woensdag 30 mei 2012 @ 02:02:
True; eens met NMe en pedorus. Neemt echter niet weg dat een simpele like '%foo%' natuurlijk gewoon moet werken; albeit non-performant en non-optimaal. Ik ben geen PHP kenner, dus help me even: is dit PDO ofzo? Zit 't probleem niet in 't verkeerd/niet escapen van % of 't ontbreken van ' om de %s ofzo?

%s is iets dat aangeeft dat je op die plek een string wil hebben en daar mist inderdaad een wildcard volgens mij.

[RobIII]

NMe schreef op woensdag 30 mei 2012 @ 02:52:
%s is iets dat aangeeft dat je op die plek een string wil hebben

Ja, d'uh. Ik ben wel bekend met 't principe (sprintf en consorten ) Waar zie je me voor aan?

NMe schreef op woensdag 30 mei 2012 @ 02:52:
en daar mist inderdaad een wildcard volgens mij.

Maar TS zegt:

Donool schreef op woensdag 30 mei 2012 @ 00:02:
Ik heb al geprobeerd om % tekens om mijn zoek variable te zetten (in de query) maar dan krijg ik alsnog geen resultaat.

Ik neem aan c.q lees dat als TS iets heeft gehad a-la ...WHERE naam LIKE %%s% OR ... en dat gaat dan niet werken De % (wildcards, niet de %s "placeholder") moeten dan namelijk escaped worden (althans, dat is bij sprintf e.d. zo):
echo sprintf('Foo %%%s%% bar', 'ende');

Foo %ende% bar

Wat je natuurlijk ook had kunnen doen / proberen: echo sprintf('Foo %s bar', '%ende%');

En dan mis je (denk ik) nog de ' om de value heen maar ik ben daar niet héél erg zeker van; ik weet niet wat DB precies voor object is (PDO??) en hoe de query method omgaat met dit soort querystrings; het lijkt me (een poging tot?) iets als prepared statements ofzo, maar dan zie ik niet helemaal in hoe dit bijv. een SQL injection zou vangen (maar again; ik weet niet wat DB is noch wat query() precies doet). Wat ik zo snel in de docs zie lijkt m'n PDO-aanname verkeerd; misschien een Zend-iets ofzo?

[ Voor 10% gewijzigd door RobIII op 30-05-2012 03:19 ]

[Cartman!]

RobIII schreef op woensdag 30 mei 2012 @ 03:01:

Wat je natuurlijk ook had kunnen doen / proberen: echo sprintf('Foo %s bar', '%ende%');

Gewoon dit, niet meer en niet minder

[Donool]

Bedankt voor alle snelle reacties! Voor de mensen wie zich afvragen wat voor iets DB::query is, het is MeekroDB (zie http://www.meekro.com/index.php). Deze vangt de SQLinjection pogingen op, heb een volledige Acunetix scan over het systeem gehaalt en alles werd netjes opgevangen een echte aanrader dus voor de beginnende gebruikers (ik).

Ik heb nu de volgende query:

SQL:

SELECT *
FROM papieren
WHERE trefwoorden LIKE '%test%'

Ik krijg ook nog het goede resultaat terug. Bedankt voor jullie tips, het is gelukt!

EDIT: Het enige probleem was dat MeekroDB een '' om de %s zet dus dan krijg je '%'test'%', ik heb dit gefixt door

SQL:

DB::query("SELECT * FROM papieren WHERE trefwoorden LIKE '%".$zoek."%'");

te gebruiken.

[ Voor 17% gewijzigd door Donool op 30-05-2012 10:47 ]

[NMe]

Ja, luister vooral niet naar de tips over normaliseren en ga symptomen bestrijden. Eens zien wat er gebeurt als er een paar duizend records later secondenlang over je query gedaan wordt omdat er geen indices gebruikt kunnen worden voor velden die je matcht met een leading wildcard.

[Donool]

NMe schreef op woensdag 30 mei 2012 @ 10:46:
Ja, luister vooral niet naar de tips over normaliseren en ga symptomen bestrijden. Eens zien wat er gebeurt als er een paar duizend records later secondenlang over je query gedaan wordt omdat er geen indices gebruikt kunnen worden voor velden die je matcht met een leading wildcard.

Ik heb het gelezen en begrijp wel dat het beter is om het zo als zij te doen, alleen voor dit systeem maakt het niet uit, er zullen op zijn hoogst 50 items invoorkomen, als ik daar 2 seconden op moet wachten (6core server met 8GB ram draaiend op een SSD)maakt het niet uit. Als ik wat groters ga maken ga ik er zeker gebruik van maken

[pedorus]

Donool schreef op woensdag 30 mei 2012 @ 10:39:
Bedankt voor alle snelle reacties! Voor de mensen wie zich afvragen wat voor iets DB::query is, het is MeekroDB (zie http://www.meekro.com/index.php). Deze vangt de SQLinjection pogingen op, heb een volledige Acunetix scan over het systeem gehaalt en alles werd netjes opgevangen een echte aanrader dus voor de beginnende gebruikers (ik).

Die gast van MeekroDB had beter eens naar example 3 van pdo kunnen kijken en zich werk kunnen besparen. Nou ja, het mooie van standaarden is dat er zo veel zijn om uit te kiezen zullen we maar zeggen. Overigens blijven de andere voordelen van prepared statements zo onbenut (oa performance-verschil).

Bedankt voor jullie tips, het is gelukt!

Pounding A Nail: Old Shoe or Glass Bottle?

EDIT: Het enige probleem was dat MeekroDB een '' om de %s zet dus dan krijg je '%'test'%', ik heb dit gefixt door

SQL:

DB::query("SELECT * FROM papieren WHERE trefwoorden LIKE '%".$zoek."%'");

te gebruiken.

Zo schakel je MeekroDB effectief uit en werkt sql-injection alsnog. De 'goede' methode lijkt me iets als

SQL:

DB::query("SELECT * FROM papieren WHERE trefwoorden LIKE %s", "%$zoek%");

of

SQL:

DB::query("SELECT * FROM papieren WHERE find_in_set(%s, zoekwoorden) > 0", $zoek);

afhankelijk van wat je wil.

[Voutloos]

Donool schreef op woensdag 30 mei 2012 @ 10:39:
EDIT: Het enige probleem was dat MeekroDB een '' om de %s zet dus dan krijg je '%'test'%', ik heb dit gefixt door

SQL:

DB::query("SELECT * FROM papieren WHERE trefwoorden LIKE '%".$zoek."%'");

te gebruiken.

Als je zelf zo een query string in elkaar plakt kan Meekro je echt niet beschermen tegen sql injeciton natuurlijk. ls je nou gewoon begrijpt dat de wildcards bij de string horen en de parameter doorgeeft incl. wildcards. Zie

RobIII schreef op woensdag 30 mei 2012 @ 03:01:
Wat je natuurlijk ook had kunnen doen / proberen: echo sprintf('Foo %s bar', '%ende%');

voor een voorbeeld. Het is leerzaam als je het verschil tussen deze en jouw oplossing uit zoekt.

[RobIII]

Donool schreef op woensdag 30 mei 2012 @ 10:39:
EDIT: Het enige probleem was dat MeekroDB een '' om de %s zet dus dan krijg je '%'test'%', ik heb dit gefixt door

SQL:

DB::query("SELECT * FROM papieren WHERE trefwoorden LIKE '%".$zoek."%'");

te gebruiken.

Ik ben niet bekend met MeekroDB (en van wat ik er vluchtig van zag had ik zoiets van... riiiiiiiight), maar heb je zo geen mogelijkheid tot SQL Injection (geïntroduceerd)?

En als je durft te komen met "het is toch alleen voor mezelf / intern" of een dergelijke kutsmoes dan krijg je gratis een ban tot 't opgelost is ( )

[ Voor 17% gewijzigd door RobIII op 30-05-2012 11:07 ]

[Voutloos]

offtopic:
Proper GMTA momentje

[RobIII]

Voutloos schreef op woensdag 30 mei 2012 @ 11:07:

offtopic:
Proper GMTA momentje

[Donool]

Voutloos schreef op woensdag 30 mei 2012 @ 11:06:
[...]
Als je zelf zo een query string in elkaar plakt kan Meekro je echt niet beschermen tegen sql injeciton natuurlijk. ls je nou gewoon begrijpt dat de wildcards bij de string horen en de parameter doorgeeft incl. wildcards. Zie
[...]
voor een voorbeeld. Het is leerzaam als je het verschil tussen deze en jouw oplossing uit zoekt.

Je hebt helemaal gelijk, wat stom van me ><
Ik zal het meteen aanpassen!

[ Voor 16% gewijzigd door Donool op 30-05-2012 11:19 ]

[NMe]

Donool schreef op woensdag 30 mei 2012 @ 10:56:
[...]

Ik heb het gelezen en begrijp wel dat het beter is om het zo als zij te doen, alleen voor dit systeem maakt het niet uit, er zullen op zijn hoogst 50 items invoorkomen, als ik daar 2 seconden op moet wachten (6core server met 8GB ram draaiend op een SSD)maakt het niet uit. Als ik wat groters ga maken ga ik er zeker gebruik van maken

Dus je programmeert alleen goed als je verwacht dat iets groot wordt?

[Donool]

Oke het is misschien niet een goed argument om het zo te doen, ik probeer alleen beter te worden in php en sql, kan je niet meteen verwachten dat ik een 100% geoptimaliseerde database maak en goed werkende code in een uurtje of 10.

[Patriot]

NMe schreef op woensdag 30 mei 2012 @ 11:24:
[...]

Dus je programmeert alleen goed als je verwacht dat iets groot wordt?

We kunnen die nou wel weer tevoorschijn halen, maar wij weten ook niet waar het precies voor is. En ieder project beschouwen als dat wat het potentieel zou kunnen zijn is ook onzinnig.

[Donool]

Topic kan wel op slot, heb de volgende werkende query:

SQL:

"SELECT * FROM papieren WHERE trefwoorden LIKE %s", '%'.$zoek.'%');

[Kajel]

Tip: Iedereen heeft het hier al gezegd, maar ik herhaal het nog eens in andere bewoordingen:
Normaliseer die DB nou gewoon! Ik weet wel dat het geen productie-werk is, en een eigen uitprobeersel/hobbyprojectje, maar geloof me nou maar, hobbyprojectjes zijn zo ongeveer de enige plek waar je tijd hebt om je te richten op de kwaliteit van je code/inrichting van de database. Mocht je ooit nog wel iets gaan doen wat valt onder noemer van productiewerk, dan heb je deadlines en dat soort zaken, en dan heb je de tijd niet meer om over dit soort dingen na te denken.
NU is juist het moment om - zoals je zelf zegt - meer te leren over PHP en SQL, en daar hoort fatsoenlijk DB design bij.

TLDR; Als je al niet het onderste uit de kan haalt qua kwaliteit bij dingen die je "voor jezelf doet", dan ga je het "voor het eggie" al helemaal niet doen!

[XyritZz]

Kajel schreef op donderdag 31 mei 2012 @ 09:18:
[...]
Mocht je ooit nog wel iets gaan doen wat valt onder noemer van productiewerk, dan heb je deadlines en dat soort zaken, en dan heb je de tijd niet meer om over dit soort dingen na te denken.
[...]

Er over nadenken moet je zeker wel als je productiewerk aan het doen bent. Als er grote tijdsdruk is kijkt management vaak alleen maar of iets af is, en niet naar de kwaliteit van het werk. Het is dan je taak als programmeur om aan te geven dat zodra de deadline gehaald is er alsnog een structureel betere oplossing toegepast moet worden.

Zie productie code als een huis, als het dak lekt en er is een 'quickfix' van 10 minuten werk of een structurele oplossing van een dag werk dan mogen ze toch eerst de quickfix toepassen zodat ik weer droog zit. Daarna wil ik echter wel alsnog de structurele oplossing om te voorkomen dat het dak binnen een paar weken weer gaat lekken.

Met code precies hetzelfde, als een site op z'n gat ligt breng je dat ding zo snel mogelijk weer in de lucht maar daarna moet je het wel duidelijk aangeven als je een quickfix hebt gebruikt zodat de klant kan beslissen of ze een structurele oplossing willen hebben.

[Knutselsmurf]

Dat is natuurlijk precies verkeerd om. Je moet juist met dit soort zaken in je hobbyprojecten ervaring opdoen, zodat je 'in het echt' daar niet meer over na hoeft te denken, of later nog moet gaan optimaliseren, omdat je direct vanaf het begin met een goede structuur werkt.

Als er iets is wat er 'in het echt' niet gebeurt, is dat je na een quickfix uitgebreid de tijd krijgt voor een nette oplossing. Het werkt nu toch? De tijd die vervolgens in de nette oplossing gestoken zou worden, kan niet gefactureerd worden en is dus niet interessant.
Beter zorg je er dus voor dat je basis gewoon goed is.

[Kajel]

XyritZz schreef op donderdag 31 mei 2012 @ 09:57:
[...]


Er over nadenken moet je zeker wel als je productiewerk aan het doen bent. Als er grote tijdsdruk is kijkt management vaak alleen maar of iets af is, en niet naar de kwaliteit van het werk. Het is dan je taak als programmeur om aan te geven dat zodra de deadline gehaald is er alsnog een structureel betere oplossing toegepast moet worden.

Leuk verhaal, maar als je om te beginnen al zorgt dat je goede code practices hanteert en goede oplossingen bedenkt, dan zijn quickfixes een stuk minder vaak nodig. Het management heeft geen boodschap aan structurele oplossingen, zodra het werkt.

Knutselsmurf schreef op donderdag 31 mei 2012 @ 10:04:
Dat is natuurlijk precies verkeerd om. Je moet juist met dit soort zaken in je hobbyprojecten ervaring opdoen, zodat je 'in het echt' daar niet meer over na hoeft te denken, of later nog moet gaan optimaliseren, omdat je direct vanaf het begin met een goede structuur werkt.

Als er iets is wat er 'in het echt' niet gebeurt, is dat je na een quickfix uitgebreid de tijd krijgt voor een nette oplossing. Het werkt nu toch? De tijd die vervolgens in de nette oplossing gestoken zou worden, kan niet gefactureerd worden en is dus niet interessant.
Beter zorg je er dus voor dat je basis gewoon goed is.

Dit is precies wat ik bedoel!

Met andere woorden: leer je trade juist bij de "hobbyprojecten" en niet op de werkvloer. Dan moet je het al gewoon kunnen.

[Morax]

Donool schreef op woensdag 30 mei 2012 @ 11:48:
Topic kan wel op slot, heb de volgende werkende query:

SQL:

"SELECT * FROM papieren WHERE trefwoorden LIKE %s", '%'.$zoek.'%');

Hou er wel rekening mee dat je onverwachte resultaten uit deze query terug kan krijgen!

Als je zoekt op "hout", en je hebt een veld waar onder andere ", houterig" in staat, dan ga je ook het record met "houterig" erin terugkrijgen!

[NMe]

Nog een mooie reden om te normaliseren inderdaad. En er is geen enkel excuus om dat niet te doen in een geval als dit, juist niet als je het nog moet leren. Als je eenmaal wat ervaring ermee hebt gaat die normalisatieslag vanzelf (ik zou niet eens zonder het op te zoeken meer weten wat het verschil tussen 1NV en 2NV is) en kost het geen extra tijd...maar dan moet je het wel in de eerste instantie eerst leren. Dat niet doen "omdat het zo toch ook werkt" is een verdomd slechte instelling voor een programmeur.

[XyritZz]

Knutselsmurf schreef op donderdag 31 mei 2012 @ 10:04:
Dat is natuurlijk precies verkeerd om. Je moet juist met dit soort zaken in je hobbyprojecten ervaring opdoen, zodat je 'in het echt' daar niet meer over na hoeft te denken, of later nog moet gaan optimaliseren, omdat je direct vanaf het begin met een goede structuur werkt.

Als er iets is wat er 'in het echt' niet gebeurt, is dat je na een quickfix uitgebreid de tijd krijgt voor een nette oplossing. Het werkt nu toch? De tijd die vervolgens in de nette oplossing gestoken zou worden, kan niet gefactureerd worden en is dus niet interessant.
Beter zorg je er dus voor dat je basis gewoon goed is.

Daar hebben we dan andere ervaringen in, want mijn ervaring is dat je die tijd wel degelijk krijgt .

Een goede basis is belangrijk, dat ben ik zeker met je eens maar dat is ook iets wat je niet altijd voor het kiezen hebt. Ik heb al regelmatig producten ondersteund die ik niet zelf ontwikkeld heb en dan krijg je simpelweg niet altijd een goede basis.

Als zo'n site dan down gaat omdat er bijvoorbeeld ergens een PHP memory limit wordt overschreden is mijn eerste zorg die site weer in de lucht krijgen (memory limit met een ini_set omhoog gooien). Daarna is de boodschap richting de klant erg simpel, dit probleem is binnen een paar maanden weer terug tenzij ik een structurele fix toe mag passen.

Als je het duidelijk uitlegt krijg je die paar uur voor de structurele fix meestal wel hoor, de klant is al blij dat z'n site geen halve dag uit de lucht is geweest.