[mysql] WHERE LIKE `waarde van db na bepaalde conversie`

[Verwijderd]

De situatie
Ik heb een aantal rijen in de tabel `onderdelen`, de kolommen zijn;
ond_id (unique) en omschrijving (varchar, 50).
Het id wordt tevens getoond aan de gebruiker, echter wordt deze geconverteerd naar een zogeheten onderdeelnummer.
De conversie is niets anders als de som van het id met een honderdduizend-tal; in mijn situatie is dit id+500000- en tevens wordt er een number_format() overheen gehaald die een punt plaatst voor de duizendtallen.
Ofwel; 5 wordt bijvoorbeeld getoond als 500.005.

Het probleem..
Dit tonen is vanzelfsprekend geen probleem, maar het zoeken wel- de bezoeker heeft de mogelijkheid te zoeken in de database mb.v. een POST-formuliertje. Met de ontvangen zoek-string stel ik een query samen die zoekt binnen alle velden (ter informatie, mijn voorbeeld tabelstructuur is uiterst versimpeld daar tevens gebruik gemaakt wordt van joins e.d.).

Een voorbeeld record;
ond_id = 1,
omschrijving = 'grote stekker'

Als de bezoeker gaat zoeken op `stekker` dan wordt dit record gevonden, dit levert geen enkel probleem op. Echter, als de bezoeker zoekt op `500.001` - iets wat veel gaat gebeuren - dan vindt hij de record vanzelfsprekend níet.
Ik ben totaal niet thuis in de MySQL string functies maar mijn vraag is eigenlijk hoe ik dit het beste ga aanpakken.

Eventuele oplossing
Een oplossing kán zijn om alvorens de query op te bouwen met de ontvangen waarde, te kijken of deze waarde voldoet aan een reguliere expressie, bijv.

code:

^([0-9]{0,3})?\.[0-9]{1,}$

... maar ik ben van mening dat dit ook wel direct in de query te converteren is.
Daarnaast gaat dat toch problemen opleveren, omdat er bijvoorbeeld ook gezocht kan worden op onderdeelnummers (`fabrikantnummers`) die er bijvoorbeeld zo uitzien:
`1234.45.678`.

[NMe]

Kun je het getal niet beter vanuit je applicatie transformeren? Sowieso omdat je dan misschien wat overzichtelijker met integers kan werken in plaats van met strings.

[Verwijderd]

-NMe- schreef op dinsdag 07 juni 2005 @ 16:45:
Kun je het getal niet beter vanuit je applicatie transformeren? Sowieso omdat je dan misschien wat overzichtelijker met integers kan werken in plaats van met strings.

NOFI, u bedoeld?

_{Ivy snapt de suggestie niet helemaal.}

[ Voor 4% gewijzigd door Verwijderd op 07-06-2005 16:48 ]

[bigbeng]

Je ontkomt er niet aan om dit programmatisch op te lossen. Zelfs al zou het direct in de query kunnen, je hebt toch minimaal een IF statement nodig om 500.001 te onderscheiden van 'grote'. De enige mogelijkheid om dat te voorkomen is door je optelling en numberformat uit te voeren in de WHERE van je zoekquery (pseudo):
WHERE number_format(id+500000, "maskje") LIKE '%zoekterm%'

Ik weet alleen niet of MYSQL zo'n format functie heeft.

[NMe]

Verwijderd schreef op dinsdag 07 juni 2005 @ 16:48:
[...]


NOFI, u bedoeld?

_{Ivy snapt de suggestie niet helemaal.}

Je bouwt een applicatie neem ik aan, zij het in PHP, zij het in een andere taal, en daar krijg je die zoekterm binnen. Als je 500.005 binnen krijgt, en het bijbehorende ID is 5, dan trek je toch gewoon 500.000 van dat getal af, en dan ga je met het resterende getal je query opbouwen? Of denk ik nu te gemakkelijk?

[ Voor 23% gewijzigd door NMe op 07-06-2005 16:54 ]

[glashio]

code:

SELECT
  *
FROM
  onderdelen
WHERE
  ond_id = IF(REPLACE('500.028','.','')<600000 AND REPLACE('500.028','.','')>=500000,REPLACE('500.028','.','')-500000,-1)

Er vanuit gaande dat in de tabel `onderdelen` alleen de onderdelen zitten van '500.000' tot en met '599.999'.
Mocht het dit niet het geval zijn komt ie met ond_id = -1 terug

* glashio is ook van mening dat je het beter door de POST-processor (ala PHP) kan converteren

[ Voor 18% gewijzigd door glashio op 07-06-2005 17:06 . Reden: Kleine wijziging :P ]

[Verwijderd]

-NMe- schreef op dinsdag 07 juni 2005 @ 16:53:
[...]

Je bouwt een applicatie neem ik aan, zij het in PHP, zij het in een andere taal, en daar krijg je die zoekterm binnen. Als je 500.005 binnen krijgt, en het bijbehorende ID is 5, dan trek je toch gewoon 500.000 van dat getal af, en dan ga je met het resterende getal je query opbouwen? Of denk ik nu te gemakkelijk?

Op zich denk je niet te makkelijk, want dat is precies wat ik suggereer als mogelijke oplossing alleen loop ik dan, zoals ik op het allerlaatst probeer uit te leggen, tegen een probleem aan en dat is dat hij het nummer níet moet formateren als het niet om een zoekactie gaat met betrekking tot het gegenereerde onderdeelnummer.
Makkelijker gezegd; stel er is een record met het id `5` (wordt dus 500.005) en er is een record met een ander id maar met als fabrikantnummer `100.500.005.123`. Dan moet hij beide records gaan vinden, en niet alleen die met id `5`. Dat is het probleem van programmatisch converteren.

[bigbeng]

Nee hoor, want als het goed is weet jij wanneer je tegen welk veld je checkt:

SELECT *
FROM artikelen
WHERE id = <geconverteerd veld> OR fabrikantnr LIKE '%<ingegeven veld>%'
...
etc

[ Voor 14% gewijzigd door bigbeng op 07-06-2005 17:10 ]

[RobIII]

Knoop een extra veld aan je tabel met de naam ond_code ofzo.
Run daarna effe een query als:

code:

Update myTable Set ond_code = ond_id + 500000

Voortaan kun je gewoon zoeken op de ingevoerde productcode (en dus niet meer op het ID) en in allerlei andere velden. Zorg wel dat je (desnoods d.m.v. een Trigger) bij een insert van een nieuw record de ond_code ook meteen weer vult met die offset van 500000.

Tot slot nog even een quote van Joe Celko (DB Guru): "once a user complains that he wants to see a primary key you're in big trouble.". M.a.w., dat ond_id had zowieso nooit zichtbaar mogen worden voor de gebruiker. Sterker; ik zou ond_id lostrekken van de voorgestelde ond_code en zorgen dat je bij het inserten van een nieuw record meteen de juiste ond_code meegeeft. Op die manier koppel je dus je ond_id van je ond_code los. Dat houdt dus in dat je ond_code in zo'n geval dus 500.009 zou kunnen zijn voor product_id 3.

Voila; my € 0.02

Met dank aan curry684 voor de quote

[ Voor 16% gewijzigd door RobIII op 07-06-2005 17:17 ]

[Verwijderd]

bigbeng schreef op dinsdag 07 juni 2005 @ 17:09:
Nee hoor, want als het goed is weet jij wanneer je tegen welk veld je checkt:

SELECT *
FROM artikelen
WHERE id = <geconverteerd veld> OR fabrikantnr LIKE '%<ingegeven veld>%'
...
etc

Ik dacht er net aan en toen zag ik je post..
Maar het klopt precies. Dat is dus ook wat ik ga doen, met de zoekfunctie geconverteerd (indien voldaan wordt aan de reguliere expressie) zoeken naar het id en ongeconverteerd (is dat een woord?) zoeken binnen de overige velden. Dank allen, en @ROBIII;

offtopic:
Ik ben het niet helemaal met meneer Joe Celko eens, hoe mooi zijn quote ook is. In deze applicatie is een uniek nummer vereist en waarom zou men dan een extra kolom willen toevoegen om daar vervolgens op allerlei omslachtige en onhandige manieren tevens een uniek nummer in te stoppen? Nogmaals, ben het niet met de uitspraak eens en zeer zeker niet in mijn geval (lees: in de omgeving waar ik nu binnen werk).

[ Voor 3% gewijzigd door Verwijderd op 07-06-2005 17:30 ]

[RobIII]

Verwijderd schreef op dinsdag 07 juni 2005 @ 17:29:
[...]

offtopic:
Ik ben het niet helemaal met meneer Joe Celko eens, hoe mooi zijn quote ook is. In deze applicatie is een uniek nummer vereist en waarom zou men dan een extra kolom willen toevoegen om daar vervolgens op allerlei omslachtige en onhandige manieren tevens een uniek nummer in te stoppen? Nogmaals, ben het niet met de uitspraak eens en zeer zeker niet in mijn geval (lees: in de omgeving waar ik nu binnen werk).

Zelf weten

[Verwijderd]

Verwijderd schreef op dinsdag 07 juni 2005 @ 17:29:
[...]

offtopic:
...en waarom zou men dan een extra kolom willen toevoegen om daar vervolgens op allerlei omslachtige en onhandige manieren tevens een uniek nummer in te stoppen?

Je manier van terugvinden van de code komt anders evengoed omslachtig over.

Alleen weet je bij het vooraf generen van de code en die in een kolom zetten later misschien nog wat je gedaan hebt. Als je die code gaat generen aan de hand van je id misschien niet meer.

[ Voor 1% gewijzigd door Verwijderd op 07-06-2005 18:57 . Reden: typo ]