[PHP/MYSQL] ophalen van selectie uit stringlist

[Verwijderd]

Ik zit nu al 2 dagen naar een oplossing waar ik niet uit kom.

Situatie:
Ik heb categorieen opgeslagen in een tabel (id, categorie). Dan kan je ergens anders kiezen welke categorieen bij het object horen wat je toevoegt. LET OP: er kunnen 1 of meer categorieeen geselecteerd worden door checkboxes. Deze artikelen worden in de tabel opgeslagen als een stringlist (2,4,6,7).

Probleem:
Ik wil gaan zoeken in deze objecten op onder andere de categorieen. Dus als ik categorie 4 en 6 selecteer wil ik heb object zien die oa deze categorieen heeft. Maar daarnaast wil ik ook nog op andere velden in de tabel van het object zoeken. Deze combinatie lukt maar niet.

Wat heb ik gevonden?
Als eerste een PHP functie om een SQL query aan te maken:

PHP:

$sqlSoort = " AND soort LIKE '%" . implode("%' OR soort LIKE '%", $arrSoorten) . "%'";

Maar dit werkt niet als je ook een andere selectie wil maken. Daarnaast heb ik de MYSQL command FIND_IN_SET gevonden maar dat werkt ook niet. Ik heb ook al gedacht aan een andere tabel met (id, object_id, cat_id) maar dat lijkt me zo onhandig en het moet ook op deze manier kunnen denk ik.

Wie weet er raad?

[Pyrus]

Mja met jouw datamodel zou het toch echt wel:

SQL:

SELECT * FROM tabel WHERE x=y AND (soort LIKE '%getal%' OR soort LIKE '%getal%'......);

zijn. (let op de haakjes )

Dat dit niet lekker werkt klopt, aangezien je niet goed genormaliseerd hebt. Je kunt beter een koppeltabel maken tussen de categorien en de objecten tabel.

[ Voor 3% gewijzigd door Pyrus op 29-04-2007 17:09 ]

[ACM]

Of natuurlijk als het een or voor die twee categorieen mag zijn:

SQL:

soort REGEXP '[(,](4|6)[,)]'

[SchizoDuckie]

Waarom niet zo?

SQL:

select * from tabel where soort in (2,4,5)

Verder is een stringlist in een sql tabel gewoon een gaar iets, een koppeltabel is dan de juiste oplossing

[ Voor 41% gewijzigd door SchizoDuckie op 29-04-2007 18:20 ]

[Verwijderd]

ACM schreef op zondag 29 april 2007 @ 17:32:
Of natuurlijk als het een or voor die twee categorieen mag zijn:

SQL:

soort REGEXP '[(,](4|6)[,)]'

*EEKS!!!*
Niet leesbaar, niet onderhoudbaar, en waarschijnlijk alleen ondersteund door MySQL.

[Verwijderd]

ik snap dat mijn manier de juiste is, maar het lijkt me dat je te veel extra dingen gaat doen met een koppeltabel. Je moet dan per soort een INSERT maken en dan ook weer een veel lastigere query maken voor het ophalen hiervan. Dus ik zie de voordelen nog niet van een koppeltabel, but be my guest om me te overtuigen.

[SchizoDuckie]

Verwijderd schreef op zondag 29 april 2007 @ 21:00:
ik snap dat mijn manier de juiste is, maar het lijkt me dat je te veel extra dingen gaat doen met een koppeltabel. Je moet dan per soort een INSERT maken en dan ook weer een veel lastigere query maken voor het ophalen hiervan. Dus ik zie de voordelen nog niet van een koppeltabel, but be my guest om me te overtuigen.

Het áller grootste voordeel is dat je gewoon veel flexibeler bent in hoe je data ophaalt. Een simpele join is echt geen complexe query imo, en die extra inserts zijn ook een loos tegen argument waar je je later echt door voor je kop gaat slaan omdat bijvoorbeeld in 1x je datamodel verandert.

[Verwijderd]

ik heb even wat ge-googled naar koppeltabels ed en ben toch maar overstag gegaan. het selecteren is ook nog simpeler en het INSERTEN stelt idd niet zoveel voor.

sql query voor ophalen:

PHP:

$sqlCats = "SELECT DISTINCT(kt.object_id) AS object_id FROM koppeltabel AS kt WHERE kt.cat_id. = '" . implode("' OR kt.cat_id = '", $arrCategorieen) . "'";
mysql_query("SELECT * FROM object WHERE id IN (" . $sqlCats . ")");

[Creepy]

Plus je kan nu gebruik maken van indexen, iets wat niet kon met je komma oplossing

[Verwijderd]

Creepy schreef op zondag 29 april 2007 @ 23:26:
Plus je kan nu gebruik maken van indexen, iets wat niet kon met je komma oplossing

wat bedoel je met indexen precies?

[ACM]

Verwijderd schreef op zondag 29 april 2007 @ 20:05:
*EEKS!!!*
Niet leesbaar, niet onderhoudbaar, en waarschijnlijk alleen ondersteund door MySQL.

En de enige die ik gezien heb die correct werkt als je vraagt om 4 en o.a. deze voorkomen: 4, 14, 40, 140.

Maar inderdaad, regexpen zijn niet in alle databases en dan ook niet op dezelfde manier geimplementeerd. Althans, sql99 geeft de heeft SIMILAR TO geintroduceerd en die kan je met bijna hetzelfde pattern gebruiken, '%[(,](4|6)[,)]%'. Maar niet elke database ondersteund dat, ook mysql niet, die heeft weer een net-een-beetje-anders aanpak.

Afgezien daarvan zou ik zelf natuurlijk wel een koppeltabel nemen, of misschien gebruik maken van de array-functionaliteit van de database, maar dan alleen als het performance-wise/opslag-wise nodig is.

Verwijderd schreef op zondag 29 april 2007 @ 22:47:
sql query voor ophalen:

Zo is ie nog ietsje leesbaarder (m.n. die IN, de newlines zijn ook wel nuttig).

PHP:

$sqlCats = "SELECT DISTINCT(kt.object_id) AS object_id
 FROM koppeltabel AS kt
 WHERE kt.cat_id IN (" . implode(", ", $arrCategorieen) . ")";

Verwijderd schreef op maandag 30 april 2007 @ 09:51:
wat bedoel je met indexen precies?

Het concept index, je kan die gebruiken om sneller bij bepaalde data te komen door gebruik te maken van (meestal) een binary search tree. Afhankelijk van het soort index is ie bruikbaar om equality (=, IN), ranges (>, >=, <, <=, between) en andere operatoren te versnellen.

In jouw geval zal je op je koppeltabel waarschijnlijk een primary key (die automatisch ook een index maakt) op (object_id, catid) ofzo hebben, waardoor zoeken naar object_id al vrij rap gaat, maar kan je ook een losse index op die cat_id plaatsen zodat je where-clause in je subquery vrij efficient opgelost kan worden.

Daarnaast is het id-veld van je object-tabel natuurlijk al een primary key, dus daar zit ook al een index op waardoor je die niet meer apart hoeft te voorzien van een index.

Maar doordat je nu met indexeerbare velden werkt kan de database vrij efficient uit een grote set records de juiste set vinden. Met je like-query dwong je de database elk record te analyseren op passende content.

[ Voor 46% gewijzigd door ACM op 30-04-2007 10:19 ]

[Equator]

Verwijderd schreef op maandag 30 april 2007 @ 09:51:
[...]


wat bedoel je met indexen precies?

Een index is een lijst met geindexeerde kolommen uit een tabel in memory van de database server. Dit werkt sneller als een full tabelscan.

Als je een tabel hebt waarin een kolom "status" zit welke 0 of 1 kan zijn (of true of false) en je hebt een querie waarin je selecteerd op rijen waarin de kolom status 1 is, dan is het handig om een index te zetten op die specifieke kolom.
Het uit memory halen van deze resultset is sneller.

Misschien is het niet zo'n goed voorbeeld, omdat 0 of 1 nog vrij snel te doen is, maar stel het is een kolom waarin de woonplaats staat. Dan zal een index wel helpen vwb performance

http://www.tizag.com/mysqlTutorial/mysql-index.php

[ACM]

Equator schreef op maandag 30 april 2007 @ 10:16:
Misschien is het niet zo'n goed voorbeeld, omdat 0 of 1 nog vrij snel te doen is

Het is om een meer technische reden geen goed voorbeeld: met slechts twee waarden is het heel erg afhankelijk van je spreiding, maar loop je een grote kans dat je uiteindelijk toch het grootste deel van je tabel moest lezen en ook nog eens de index erbij. En in dat geval was het minder werk geweest om de records zelf maar gewoon te lezen. Een bitmap-index (die niet alle databases kennen) zou overigens dat probleem grotendeels teniet doen.

Als je echter bijvoorbeeld maar een laag percentage 1-en hebt en je af en toe wat 1-en moet weten, dan is het wel een bruikbare plek voor een index. Overigens hangt ook de bruikbaarheid hier heel sterk van de database af, sommige lezen de records niet eens in als ze alleen dat veld van de index hoeven te weten en dan is zo'n index al veel sneller zinvol natuurlijk, anderen moeten altijd de records lezen die er bij horen en dan is het dus minder gauw efficienter.

[Equator]

Het was natuurlijk slechts een voorbeeld.

Het zal ongetwijfeld sterk afhangen van het gebruikte DB, maar in weze is het interessant om indexen te zetten op kolommen waar je in je query een where op gebruikt, en waarin de waardes wezenlijk afwijken.. Dus niet 0 of 1/ true of false