[MYSQL] Order BY getal uit kommagescheiden reeks

[flo_]

Hallo,

Ik heb een vraag: hoe kan ik kijken of een bepaalde string/integer in een kommagescheiden value zit?

Met andere woorden: ik heb in mijn database bij alle records een extra veld met daarin een aantal getallen (kommagescheiden). Deze zijn erin gekomen via implode(). Nu wil ik sorteren op records die een bepaald nummer bevatten in dat extra veld.

Wie weet de oplossing?

Groet,
flo_

[Pyrus]

Een goed database model gebruiken, oftewel normaliseren.
Als het aantal waarden dat in die string zit vast is betekent dit een kolom per waarde toevoegen. Is het aantal waarden variabel zul je de unieke waardes in een tabel moeten opslaan en gaan werken met een koppeltabel tussen de beide tabellen.

Je zou ook kunnen doen:

SQL:

SELECT * FROM tabel WHERE kolommetcsv LIKE '%gezochtewaarde%';

Maar dan kom je in de problemen als je 12 zoekt en er ook 112 of 120 ofzo staat.

[ Voor 24% gewijzigd door Pyrus op 29-03-2007 22:40 ]

[RobIII]

Ik zou als ik jou was eerder eens gaan kijken naar mijn datamodel dan hoe je dit voor elkaar kunt krijgen; zo te horen rammelt je datamodel nogal

Er zijn vast creatieve manieren om dit op te lossen, maar ik zou het daar dus zoeken.

[flo_]

Pyrus schreef op donderdag 29 maart 2007 @ 22:39:
Een goed database model gebruiken, oftewel normaliseren.
Als het aantal waarden dat in die string zit vast is betekent dit een kolom per waarde toevoegen. Is het aantal waarden variabel zul je de unieke waardes in een tabel moeten opslaan en gaan werken met een koppeltabel tussen de beide tabellen.

Kent MySQL dan echt geen functie die een tijdelijke array aanmaakt o.i.d. voor het verwerken van statements?

RobIII: waar vind ik dat model

[ Voor 3% gewijzigd door flo_ op 29-03-2007 22:43 ]

[Pyrus]

je datamodel is de structuur van je database. Dus de tabellen met hun kolommen + types en onderlinge relaties.

[flo_]

@Pyrus: je query is goed. Bij de output nogmaals controleren met explode en in_array of het daadwerkelijk dat getal is wat ik zoek Het gaat niet om al te veel records, dus volgensmij is mijn probleem opgelost. Of zie ik wat over het hoofd ?

[The Eagle]

flo_ schreef op donderdag 29 maart 2007 @ 22:40:
[...]

Kent MySQL dan echt geen functie die een tijdelijke array aanmaakt o.i.d. voor het verwerken van statements?

RobIII: waar vind ik dat model

Nofi, maar als je al niet weet wat een datamodel is dan vraag ik me af wat je uberhaupt met MySQL wilt doen.
En verder: idd, gewoon het LIKE statement gebruiken icm met wildcards

[Pyrus]

Ja, dat je datamodel ontiegelijk brak is en dit waarschijnlijk inefficienter is dan een fatsoenlijk datamodel

Daarnaast: het zijn NU niet al te veel records, maar blijven het er zo weinig of nemen ze toe?

[RobIII]

flo_ schreef op donderdag 29 maart 2007 @ 22:46:
Of zie ik wat over het hoofd ?

Dat het een WHERE clause is en geen ORDER BY misschien? (Zoals je topictitel aangeeft)

[flo_]

The Eagle schreef op donderdag 29 maart 2007 @ 22:47:
[...]

Nofi, maar als je al niet weet wat een datamodel is dan vraag ik me af wat je uberhaupt met MySQL wilt doen.
En verder: idd, gewoon het LIKE statement gebruiken icm met wildcards

Nou, misschien weet ik wat je bedoelt als jet met andere woorden omschrijft. Maar die term ken ik niet. Ik kan heus met MySQL overweg hoor alleen dit was even een lastig ding.

RobIII: ja, dat zag ik ook meteen al na het posten

[ Voor 5% gewijzigd door flo_ op 29-03-2007 22:50 ]

[LauPro]

Ik heb me hier ook al rot naar gezocht in het verleden, maar het is niet mogelijk met ORDER BY voor zover ik weet. Of je moet een tweede nummering genereren die je dan sorteert, alles behalve snel.

Het is niet altijd zo dat data logisch (A..Z, Z..A, 0..1, 1..0) moet worden gesorteerd imo. Kan best zijn dat je wil sorteren op:

code:

 1 3 5 7 2 4 6 8

Datamodel kan dan misschien rammelen maar als je met veel legacy zit en een status moet worden verplaatst qua positie zit je mooi.

[flo_]

Kan ook nog iemand uitleggen wat datamodel is Gewoon table structure, net zoals in PHPMyAdmin?

Edit: thnx Pyrus, stiekem berichten editen

[ Voor 20% gewijzigd door flo_ op 29-03-2007 22:58 ]

[RobIII]

flo_ schreef op donderdag 29 maart 2007 @ 22:57:
Kan ook nog iemand uitleggen wat datamodel is Gewoon table structure, net zoals in PHPMyAdmin?

http://en.wikipedia.org/wiki/Data_model
http://en.wikipedia.org/wiki/Data_modeling#Data_model

[ Voor 9% gewijzigd door RobIII op 29-03-2007 23:00 ]

[Verwijderd]

flo_ schreef op donderdag 29 maart 2007 @ 22:46:
@Pyrus: je query is goed. Bij de output nogmaals controleren met explode en in_array of het daadwerkelijk dat getal is wat ik zoek Het gaat niet om al te veel records, dus volgensmij is mijn probleem opgelost. Of zie ik wat over het hoofd ?

in_array en explode zijn PHP functies, en hebben niks met (My)SQL te maken. Er zijn hier al 'dure' termen als normaliseren en datamodel gevallen, maar 't komt er in dit geval op neer dat je niet meerdere (comma delimited) waarden in 1 veld in je database moet opslaan. 't Kan wel, en wanneer er niet op gesorteerd of gefilterd moet worden is 't soms niet eens zo slecht, maar normaal sla je die verschillende waarden op in een 2e tabel die je middels een foreign key koppelt aan je hoofdtabel.
Dan weet je database ook dat 't bv. gaat om de 3e entry in die 2e tabel i.p.v. het stukje tekst tussen de 2e en 3e komma in dat veld in de 1e tabel. Dan is je database een stuk blijer, en kun je veel efficientere indexen aanleggen en queries formuleren.

[SchizoDuckie]

Verwijderd schreef op donderdag 29 maart 2007 @ 23:51:
[...]
in_array en explode zijn PHP functies, en hebben niks met (My)SQL te maken. Er zijn hier al 'dure' termen als normaliseren en datamodel gevallen, maar 't komt er in dit geval op neer dat je niet meerdere (comma delimited) waarden in 1 veld in je database moet opslaan. 't Kan wel, en wanneer er niet op gesorteerd of gefilterd moet worden is 't soms niet eens zo slecht, maar normaal sla je die verschillende waarden op in een 2e tabel die je middels een foreign key koppelt aan je hoofdtabel.
Dan weet je database ook dat 't bv. gaat om de 3e entry in die 2e tabel i.p.v. het stukje tekst tussen de 2e en 3e komma in dat veld in de 1e tabel. Dan is je database een stuk blijer, en kun je veel efficientere indexen aanleggen en queries formuleren.

Volgens mij volgen jullie z'n probleem niet helemaal in dit topic

Jullie denken allemaal over een where bladibla in (yada,yada,yada) maar wat hij volgens mij bedoelt is dit:

SQL:

select * from blaat where meuk order by blaat_id custom(1,5,4,6,2,8,100,212,101) 

of zoiets, waar dan custom een functie zou zijn die die waarden selecteert uit blaat.sortOrder ofzo

Dit kan natuurlijk nooit zo werken, want wat moet er gebeuren met ID's die buiten die range liggen?

[NMe]

SchizoDuckie schreef op vrijdag 30 maart 2007 @ 03:05:
[...]

Volgens mij volgen jullie z'n probleem niet helemaal in dit topic

Volgens mij bedoelt ie juist dat ie een string opgeslagen heeft in zijn database met daarin allerlei kommagescheiden waarden, waar hij nu dingen uit wil lezen en erachter komt dat dat vrij lastig gaat omdat databases daar niet voor gemaakt zijn.

[SchizoDuckie]

oke TS, clarify yourself

[P.O. Box]

flo_ schreef op donderdag 29 maart 2007 @ 22:36:
Ik heb een vraag: hoe kan ik kijken of een bepaalde string/integer in een kommagescheiden value zit?

Dit is de letterlijke vraag...

vele wegen leiden naar Rome:
1. in php kun je eerst exploden en dan in_array gebruiken
2. je kunt zoiets doen:

PHP:

function blaat($zoekstring) {
$str = "5,6,7,8,9,10,12,113,14,51,36";
return strpos("," . $str . ",", "," . $zoekstring . ",")
}

3. of in MySQL:

SQL:

SELECT
    selectievelden
FROM
   tabel
WHERE
   CONCAT(",", zoekkolom, ",") LIKE CONCAT("%,", zoekwaarde, ",%")

en zo zijn er nog duizenden anderen mogelijkheden te bedenken...

[LauPro]

Natuurlijk kan je ook een heel resultset in een array mikken en dan in PHP sorteren, maarja daar gaat juist performance verloren die je normaal je DB zou kunnen laten doen.

[ACM]

LauPro schreef op donderdag 29 maart 2007 @ 22:50:
Ik heb me hier ook al rot naar gezocht in het verleden, maar het is niet mogelijk met ORDER BY voor zover ik weet.

Tuurlijk wel. Je kan gewoon op een 'expression' sorteren in mysql, dus zoiets:

SQL:

SELECT ..
ORDER BY
  veld REGEXP '(^|,)123(,|$)',
  etc..

Of als je extern de sortering wilt bepalen:

SQL:

SELECT ..
ORDER BY
  CASE veld 
   WHEN 1 THEN 125
   WHEN 2 THEN 24
   WHEN 3 THEN ...
   END

[ Voor 18% gewijzigd door ACM op 30-03-2007 14:06 ]

[LauPro]

Hebben we het over hetzelfde? Met versie 5.0.26 krijg ik met bijv:

SQL:

SELECT ..
ORDER BY 
  status REGEXP '(^|,)4306(,|$)'

Niet echt een sortering met deze regexp?

[ACM]

Zolang er een waarde uit de expressie komt krijg je een sortering, alleen is het natuurlijk een twee-waardige sortering want er kan alleen true of false (ok en null) uit komen...

SQL:

select version();
+-----------+
| version() |
+-----------+
| 5.0.26    |
+-----------+

create table sorter (field varchar (20));
insert into sorter values ('a123,456,789'),('b321,654,789'),('c321,456,987');
select * from sorter;
+--------------+
| field        |
+--------------+
| a123,456,789 |
| b321,654,789 |
| c321,456,987 |
+--------------+

select * from sorter order by field regexp '(^|,)456(,|$)';
+--------------+
| field        |
+--------------+
| b321,654,789 |
| a123,456,789 |
| c321,456,987 |
+--------------+

Zoals je ziet staat het b-veld nu bovenaan omdat die natuurlijk 0 krijgt uit de expressie, als je die onderaan wilt hebben kan je DESC toevoegen. Oftewel je kan zo sorteren op het hebben van een waarde in dat veld.

Als je wilt sorteren op de waarde van bijvoorbeeld het 3e veld moet je het natuurlijk anders doen, want een regexp-expressie doet niets met die matches, je zou dan zoiets kunnen doen:

SQL:

insert into sorter values ('d123,456,10'), ('e123,456,10000');
select field from sorter order by substring_index(field, ',', -1);
+----------------+---------------------------------+
| field          | substring_index(field, ',', -1) |
+----------------+---------------------------------+
| d123,456,10    | 10                              |
| e123,456,10000 | 10000                           |
| a123,456,789   | 789                             |
| b321,654,789   | 789                             |
| c321,456,987   | 987                             |
+----------------+---------------------------------+

-- of numeriek:
select field from sorter order by 1* substring_index(field, ',', -1);
+----------------+---------------------------------+
| field          | substring_index(field, ',', -1) |
+----------------+---------------------------------+
| d123,456,10    | 10                              |
| a123,456,789   | 789                             |
| b321,654,789   | 789                             |
| c321,456,987   | 987                             |
| e123,456,10000 | 10000                           |
+----------------+---------------------------------+

Dus ik weet niet waar jij het over hebt, maar ik heb het er over dat je in MySQL de sortering wel degelijk kan laten afhangen van delen van de waarden van velden. Je kan dus sorteren op 'bevat getal X', op een bepaalde substring, op een voorgedefinieerde volgorde (zie CASE-voorbeeld) en eigenlijk op alles wat je maar wilt als je het uit een (combinatie van) expressie(s) kan krijgen.

[Confusion]

RobIII schreef op donderdag 29 maart 2007 @ 22:39:
Ik zou als ik jou was eerder eens gaan kijken naar mijn datamodel dan hoe je dit voor elkaar kunt krijgen; zo te horen rammelt je datamodel nogal

Niet noodzakelijk. Er zijn gevallen waarin het gebruiken van een kolom met bijvoorbeeld comma seperated key-value pairs praktischer is dan 'de nette' oplossing, hoewel alleen comma seperated values dan waarschijnlijk weer niet de meest begrijpelijke oplossing is.

[flo_]

Bedankt allemaal voor jullie hulp Het gaat indeerdaad om een reeks zoals:

code:

10,23,9,23,545,2,3

Ik vergelijk het eerst met WHERE `veldnaam` LIKE ``. Wat hij daar vindt, controleer ik nogmaals met explode() en in_array() middels PHP zelf. Mijn databasestructuur zou inderdaad efficienter kunen, bijboorbeeld een aparate table met daarin de waardes per record (3 velden: id, main_record_id, value).

Normaliter stel ik op dit forum nooit vragen over PHP, want meestal ben ik te vinden in het fotografieforum. Op veel andere websites waar ik voorheen actief was, gebruikte men het woord "tabel structuur/table structure". Omdat hier "datamodel" wordt gebruikt, kwam ik er niet zo snel achter wat jullie hiermee bedoelden.

Het gaan niet om duizenden records, maar slechts om tientallen.
Voor de perfomance zal het niet veel uitmaken.

Bedankt voor de hulp!

[ Voor 7% gewijzigd door flo_ op 31-03-2007 12:41 ]

[Mischa_NL]

Csv horen (over het algemeen) niet in een database flo_.
of dat nou performance-wijs niet uitmaakt doordat je weinig records hebt doet er weinig toe. stel dat je een grotere applicatie wilt maken...
Als ik jou was zou ik je datamodel in orde maken, al is het maar om ervan te leren.

Laat me raden:
je maakt een foto album en slaat in een table album, in iedere record als csv de fotonummers op?

[ Voor 17% gewijzigd door Mischa_NL op 31-03-2007 13:19 ]