php zoek functie database

[Verwijderd]

Hallo iedereen,

Ik ben bezig met een website te ontwikkelen met een sql database. Ik wil graag een zoekfunctie implementeren voor de administrator. Die de gegevens van een bepaalde gebruiker(s) weergeeft. Nu zou ik graag op naam, adres en username willen zoeken. Dit is allemaal geen probleem. Het enigste wat ik me afvraag is hoe ik de lege velden moet implementeren in mijn query. Gebruik ik hiervoor een LIKE of bestaan hier andere technieken voor?

[MueR]

Je kan toch gewoon kijken of een veld leeg is en die dan niet meenemen in je query?

[Verwijderd]

MueR schreef op vrijdag 18 mei 2012 @ 13:03:
Je kan toch gewoon kijken of een veld leeg is en die dan niet meenemen in je query?

Dat was ook mijn eerste gedacht. Kan je 'if' statements meegeven in een query?

[mithras]

Maakt het iets uit dat je die expliciet wil vermelden? Als je op "dorpstraat" zoekt en ik geef mijn straat niet op (dus: veld is leeg), dan wil je toch niet dat ik in de resultaten voorkom?

[Creepy]

Verwijderd schreef op vrijdag 18 mei 2012 @ 13:06:
[...]

Dat was ook mijn eerste gedacht. Kan je 'if' statements meegeven in een query?

Doe eens gek en pak de documentatie erbij. Los daarvan wil je dit gewoon in je code doen en de query gewoon dynamisch opbouwen

code:

if zoekvelde is gevuld then
  voeg zoekveld aan query toe

[NMe]

Waarom zou je een if willen toevoegen aan een query als dat in PHP kan om een makkelijker te cachen query te maken?

[Verwijderd]

Oke bedankt, ik ga de documentatie maar eens goed bekijken.

[kwaakvaak_v2]

Hoe wilde je anders zonder code tussenlaag van Formulier -> Database -> Zoekresultaat gaan?

[Arjen42]

Je zou in je query constructies zoals hieronder kunnen toepassen:

SQL:

WHERE (naam = @naam OR @naam IS NULL)
AND (adres = @adres OR @adres IS NULL)
AND (username = @username OR @username IS NULL)

Op deze manier kan je op 1,2, or 3 van de velden zoeken. Deze velden die je niet gebruikt zet je dan op NULL. Groot voordeel van deze manier is dat je maar één query hoeft te maken en deze niet dynamisch hoeft te generen in je code.

[ Voor 51% gewijzigd door Arjen42 op 18-05-2012 21:39 . Reden: Velden aangepast zoals in voorbeeld. Uitleg toegevoegd. ]

[Verwijderd]

Arjen42 schreef op vrijdag 18 mei 2012 @ 21:34:
Je zou in je query constructies zoals hieronder kunnen toepassen:

SQL:

WHERE (straat = @straat OR @straat IS NULL)
AND (woonplaats = @woonplaats OR @woonplaats IS NULL)

Ik kan me vergissen maar geeft dit dan niet een andere output. Als ik bij adres enkel mijn straatnaam ingeef en niet mijn huisnummer wil ik toch enkel de resultaten zien waarvan de straatnaam "straatnaam" is en de huisnummers maken niet uit. Op jouw manier worden ook records getoond waarin het huisnummer effectief NULL is.

Het kan zijn dat ik me volledig vergis hoor.

[Arjen42]

Verwijderd schreef op vrijdag 18 mei 2012 @ 21:39:
[...]


Ik kan me vergissen maar geeft dit dan niet een andere output. Als ik bij adres enkel mijn straatnaam ingeef en niet mijn huisnummer wil ik toch enkel de resultaten zien waarvan de straatnaam "straatnaam" is en de huisnummers maken niet uit. Op jouw manier worden ook records getoond waarin het huisnummer effectief NULL is.

Het kan zijn dat ik me volledig vergis hoor.

Volgens mij is je opmerking offtopic aangezien de topic starter het niet over de semantiek van database zelf heeft. Waar het (bij mij) omgaat is hoe je optioneel op meerdere kolommen kunt zoeken. Let op dat de IS NULL check op de variable c.q. parameter is en niet op de kolom.

Je hebt gelijk als je bedoeld dat je niet meer expliciet kan zoeken c.q. uitsluiten op huisnummer IS NULL. Ik heb echter mijn twijfels of dit in de praktijk voorkomt. Daarnaast is vaak het huisnummer een verplicht veld. Wat meestal inhoud dat deze kolom als NOT NULL is gedefinieerd. Uiteraard is dit een aantal scenario's weer niet handig als het huisnummer niet bekend is.

Ik snap overigens niet wat jou bezwaar is tegen als de huisnummers maken niet uit er ook records getoond worden waarin het huisnummer effectief NULL is. Deze records zou je toch ook zien wanneer je query gebruikt die enkel op straatnaam filtert.

[Verwijderd]

Arjen42 schreef op vrijdag 18 mei 2012 @ 21:57:
[...]


Ik snap overigens niet wat jou bezwaar is tegen als de huisnummers maken niet uit er ook records getoond worden waarin het huisnummer effectief NULL is. Deze records zou je toch ook zien wanneer je query gebruikt die enkel op straatnaam filtert.

Voila, daar had ik weer niet aan gedacht. Je hebt gelijk!

[krvabo]

Verwijderd schreef op vrijdag 18 mei 2012 @ 13:15:
Oke bedankt, ik ga de documentatie maar eens goed bekijken.

'We' bedoelen gewoon dit:

PHP:

$sql = 'SELECT straat, naam, etc FROM tabel WHERE';

if (isset($naam)) {
  $sql .= " naam = ' ".mysql_real_escape_string($naam)." ' ";
}
[..]
->query($sql);

Wil je het netter, en makkelijker, doen dan kun je het ook zo doen:

PHP:

$aWhere = array();

if (isset($naam)) {
  $aWhere[] = " naam LIKE '%".mysql_real_escape_string($naam)."%' ";
}

if (isset($adres)) {
  $aWhere[] = " adres LIKE '%".mysql_real_escape_string($adres)."%' ";
}

if (isset($postcode)) {
  $aWhere[] = " postcode LIKE '%".mysql_real_escape_string($postcode)."%' ";
}

$sql = 'SELECT straat, naam, etc FROM tabel WHERE ' . implode(' AND ', $aWhere);

->query($sql);

Iets in die richting.

[ Voor 4% gewijzigd door krvabo op 18-05-2012 22:55 ]

[Verwijderd]

Maar dan liefst zonder het aan elkaar plakken van Strings. Ik denk dat hetzelfde mogelijk zal zijn m.b.v geparameteriseerde queries.

[Arjen42]

@Rubinski, zoals mijn voorbeeld?

Als DBA'er heb ik zelf natuurlijk de voorkeur voor een stored procedure of een table valued user definied function.

Voorbeeld van zo'n een stored procedure zou zo kunnen zijn:

SQL:

CREATE PROCEDURE sp_findUser
(
   @naam varchar(100)
   , @username varchar(100)
   , @adres varchar(100)
)
AS
BEGIN
   SELECT 
      u.username
      , u.naam
      , u.adres
   FROM users u
   WHERE (u.naam = @naam OR @naam IS NULL)
   AND (u.adres = @adres OR @adres IS NULL)
   AND (u.username = @username OR @username IS NULL)
END;

In PHP kan je dan je deze stored procedure aanroepen met de @naam, @adres, en @username parameters. Voor de zoekvelden waar je dan niet op wil zoeken zet je dan de overeenkomstige parameter op NULL.

Wil je graag LIKE gebruiken kan dit ook. De joker tekens kunnen dan in PHP code of door de gebruiker zelf ingevoerd worden. In de stored procedure vervang je de = operator door de LIKE operator.

[krvabo]

Ja, prepared statements zijn een geweldige oplossing, en een stored procedure is natuurlijk prachtig, maar gezien wat de TS vraagt denk ik niet dat zoiets op dit moment binnen zijn kennisniveau past.

Als mensen tegen je vertellen hoe je de sixtijnse kapel na kunt schilderen terwijl je alleen je plafond wil witten schiet je er ook weinig mee op.

[Arjen42]

@krvabo,

Op zich heb je een punt. Aan de andere kant zal zoiets nooit in zijn kennisniveau komen als we dit soort vragen altijd met strings aan elkaar plakken zullen beantwoorden. Eigenlijk zouden de tutorials gelijk met prepared statements moet beginnen en het aan elkaar plakken van strings geheel moeten weglaten.

Is er iemand die een voorbeeld van prepared statements wil plaatsen? Zoveel kennis van PHP heb ik niet. Ik ben meer een dotnetter.

[RobIII]

Arjen42 schreef op vrijdag 18 mei 2012 @ 23:37:
Is er iemand die een voorbeeld van prepared statements wil plaatsen?

Dat is nergens voor nodig: Over het gebruik van Parametrized Queries staat in onze FAQ (hoewel, granted, wat verouderd) en met Google is er ook zat te vinden.

[ Voor 16% gewijzigd door RobIII op 18-05-2012 23:53 ]

[ValHallASW]

Arjen42 schreef op vrijdag 18 mei 2012 @ 22:52:
Als DBA'er heb ik zelf natuurlijk de voorkeur voor een stored procedure of een table valued user definied function.

Zeker als DBA'er moet je dit artikel van Use the Index, Luke dan even lezen ;-)

Samenvatting: wél de query dynamisch samenstellen, maar de data (= de zoektermen) via een parametrised query doorsturen. Dus (op basis van krvabo's code):

PHP:

$aWhere = array();
$filtervalues = array()

if (isset($naam)) {
  $aWhere[] = " naam LIKE :naam ";
  $filtervalues[":naam"] = $naam;
}

if (isset($adres)) {
  $aWhere[] = " adres LIKE :adres";
   $filtervalues[":adres"] = $adres;
}

$sql = 'SELECT straat, naam, etc FROM tabel WHERE ' . implode(' AND ', $aWhere);

$preparedStatement = $db->prepare($sql);
$preparedStatement->execute($filtervalues)
$rows = $preparedStatement->fetchAll();

en dat kan ongetwijfeld ook als stored procedure - ook zonder die gekke "OR :naam IS NULL"-zooi ;-)

[ Voor 4% gewijzigd door ValHallASW op 19-05-2012 16:29 ]

[Verwijderd]

krvabo schreef op vrijdag 18 mei 2012 @ 23:03:
Ja, prepared statements zijn een geweldige oplossing, en een stored procedure is natuurlijk prachtig, maar gezien wat de TS vraagt denk ik niet dat zoiets op dit moment binnen zijn kennisniveau past.

Als mensen tegen je vertellen hoe je de sixtijnse kapel na kunt schilderen terwijl je alleen je plafond wil witten schiet je er ook weinig mee op.

Je hebt een punt, maar als we het hem niet nu vertellen zal hij in een slechte gewoonte hervallen en zal hij het nooit leren.

Maak zoals Arjen zegt gewoon een stored procedure. Ik denk dat dit in jouw geval inderdaad het gemakkelijkste zal zijn.

Nog wat interessant leesvoer: Prepared statements and Stored Procedures

[Arjen42]

@ValHallASW, bedankt voor je aanvulling. Nu begint het een discussie m.b.t. performance optimalisatie te worden. Interessant dus.

Ik had me hier in eerste instantie niet op gefocust, hoewel het dillema met het optimale query plan wel in mijn achterhoofd zat. Wat de topic starter niet vermeldde was of er überhaupt indexen op deze kolommen aanwezig zouden zijn. Ik ging er maar even van uit dat deze er niet waren. In zo'n geval was er in beide oplossing, geïntegreerd of dynamisch, sprake geweest van een table scan. (Daarnaast als je LIKE conditie begint met % wordt er ook geen index gebruikt.)

Wil je dit optimaliseren zul je dus inderdaad aparte queries moeten maken. Aparte stored procedures e.v.t. met een wrapper stored procedure er om heen die de keuzes maakt welke uit te voeren. Daarnaast zul je per kolom minimaal één index moeten maken en overwegen om deze de andere kolommen te laten coveren. Uiteraard afhankelijk het aantal kolommen in de tabel en welke je daarvan wil laten zien.

Ook zal jezelf moeten afvragen wat het optimaliseren oplevert. Heeft het zin om een query die misschien één keer maand uitgevoerd wordt en 20 seconden duurt te optimaliseren zodat het maar 2 seconden duurt, terwijl voor elke mutatie op deze tabel 9 indexen bijgewerkt moeten worden?