Keskustelu: Nettisivujen teko: WHERE-ehdot syötettyjen tietojen mukaan

p99o [15.10.2013 12:08:26]

Tuli sellainen ongelma tässä vastaan, että oikein keksi miten mysql:ssä saisi where-ehdot toimimaan siten että 'tyhjiä where-ehtoja' ei huomioitaisi kyselyssä.

Hyvänä esimerkkinä verkkokauppa, jossa tuotteita voisi jaotella kategorian, valmistajan ja vaikkapa hinnan mukaan.

Kutsuessa sivua, voisi kutsu olla kutakuinkin näin

tuotesivu.php?kategoria=2&maxhinta=300&valmistaja=2

Entä jos kyselystä jätetään (tai siihen lisätään ehtoja) jotain pois -
esim:

tuotesivu.php?kategoria=2&maxhinta=100

Saako hommaa toimimaan yhteen kyselyyn? Tietääkseni AND ja OR eivät tässä kohtaa hirveästi auta?

The Alchemist [15.10.2013 12:10:16]

Jos nyt yrität kysyä, että miten saat WHERE-osasta pois kohdan "valmistaja=foo", kun valmistajaa ei ole annettu get-parametrina, niin vastaus on tietysti yksinkertaisesti se, että jätät kyseisen kentän pois sql-kyselystäsi. Asiaan ei liity mitään ihmeellistä magiaa.

p99o [15.10.2013 12:31:58]

miten "yksinkertaisesti jätän kentän pois"?

WHERE .kategoria={asetettu} AND .valmistaja={ei asetettu, kaikki käy} AND .jokutieto={asetettu}

Lebe80 [15.10.2013 12:54:17]

Rakennat sitä where -lauseesta ihan merkkijono-muuttujan ehtojen perusteella.

Vaikka teet ihan aluksi array-apumuuttujalla if-lauseita hyväksi käyttäen ja vaikka implodella yhdistät " AND " -sanalla liimaten.

Tietenkin voit tehdä myös monimutkaisemmallakin tavalla, jos pelkkiä and-ehtoja et voi käyttää.

if ($valmistaja){
 $whereArr[] = "valmistaja=$valmistaja";
}
if ($kategoria){
 $whereArr[] = "kategoria=$kategoria";
}
if ($kikkare){
 $whereArr[] = "kikkare=$kikkare";
}
...
"select ....... where ".implode(" AND ",$whereArr);

Metabolix [15.10.2013 13:18:08]

PDO:lle sopivammin:

$parametrit = array();
$where = array();
if (isset($_GET["valmistaja"])) {
	$where[] = "valmistaja = :valmistaja";
	$parametrit[":valmistaja"] = $_GET["valmistaja"];
}
if (isset($_GET["maxhinta"])) {
	$where[] = "hinta <= :maxhinta";
	$parametrit[":maxhinta"] = $_GET["maxhinta"];
}
if (empty($where)) {
	$where = "";
} else {
	$where = "WHERE ". implode(" AND ", $where);
}
$lause = $pdo->prepare("SELECT * FROM taulu {$where}");
$lause->execute($parametrit);

p99o [15.10.2013 13:27:34]

Aivan ihanaa, sain toimimaan. Erityisesti tuo implode pelasti, sitä en olekaan ennen käyttänyt pahemmin.

Kiitos myös metabolixille, vaikkakin ehdinkin jo saada toimimaan. Oma versioni oikeastaan on ihan saman näköinen, joten en omaa tuotostani laita tähän

The Alchemist [15.10.2013 14:00:44]

Itse tykkään näistä abstraktiokerroksista:

<?php

db_search(
    'cars',
    ['brand' => 'ford', 'model' => 'sierra', 'price' => ['min' => 500, 'max' => 2000], 'junk' => 'foobar'],
    ['size' => 50, 'skip' => 100]
);

function db_search($type, $search, $limit = []) {
    $fields = db_get_specs($type);
    $params = db_filter_params($fields, $search);
    $options = ['where' => $params] + $limit;
    $result = db_query_select($type, $fields, $options);
}

function db_get_specs($type) {
    $specs = [
        'cars' => ['brand', 'model', 'price', 'year'],
    ];

    if (!isset($specs[$type])) {
        throw new Exception('Invalid type');
    }

    return $specs[$type];
}

function db_filter_params($fields, $params) {
    $fields = array_flip($fields);
    return array_intersect_key($params, $fields);
}

function db_query_select($table, $fields, $options) {
    $fields = implode(', ', $fields);
    $sql = sprintf('SELECT %s FROM %s', $fields, $table);

    if (!empty($options['where'])) {
        $where = db_compile_where($options['where']);
        $sql .= sprintf(' WHERE %s', $where);
    }

    if (!empty($options['size'])) {
        $sql .= sprintf(' LIMIT %d', $options['size']);

        if (!empty($options['skip'])) {
            $sql .= sprintf(' OFFSET %d', $options['skip']);
        }
    }

    printf("query:\n%s\n\n", $sql);
    printf("params:\n%s\n", print_r(db_map_params($options['where']), true));

    // $result = db_handle()->execute($sql, db_map_params($options['where']))
    // return $result;
}

function db_compile_where($params, $join = 'and') {
    $fields = [];
    foreach ($params as $key => $value) {
        if (is_array($value)) {
            if (isset($value['min'])) {
                $fields[] = sprintf('%s > :%s_min', $key, $key);
            }
            if (isset($value['max'])) {
                $fields[] = sprintf('%s < :%s_max', $key, $key);
            }
        } else {
            $fields[] = sprintf('%s = :%s', $key, $key);
        }
    }
    $sep = sprintf(' %s ', strtoupper($join));
    return implode($sep, $fields);
}

function db_flatten_params($params) {
    $flat = [];
    foreach ($params as $key => $value) {
        if (is_array($value)) {
            if (isset($value['min'])) {
                $flat[$key . '_min'] = $value['min'];
            }
            if (isset($value['max'])) {
                $flat[$key . '_max'] = $value['max'];
            }
        } else {
            $flat[$key] = $value;
        }
    }
    return $flat;
}

function db_map_params($params, $type = 'assoc') {
    $params = db_flatten_params($params);

    switch ($type) {
        case 'num':
            return array_values($params);

        case 'assoc':
            $named = [];
            foreach ($params as $key => $value) {
                $named[':' . $key] = $value;
            }
            return $named;

        default:
            throw new Exception('invalid');
    }
}

query:
SELECT brand, model, price, year FROM cars WHERE brand = :brand AND model = :model AND price > :price_min AND price < :price_max LIMIT 50 OFFSET 100

params:
Array
(
    [:brand] => ford
    [:model] => sierra
    [:price_min] => 500
    [:price_max] => 2000
)

p99o [21.10.2013 18:11:09]

Miten tuohon saisi vielä LIMIT min, max.

PDO luonnollisesti sekoaa jos ne laittaa muuttujiin. täytysi käyttää Bind* -funktiota, mutta array sekoittaa homman.

Metabolix [21.10.2013 18:13:59]

LIMIT-osassa ei voi käyttää parametreja. Ei auta kuin laittaa muuttujat suoraan kyselyyn – mieluiten kokonaisluvuiksi pakotettuina (esim. intval-funktiolla).

$sql .= "LIMIT ". intval($min). ", ". intval($max);

p99o [21.10.2013 18:48:49]

miten voin ajatella niin vaikeasti, no noin tietenkin.
Eih...

Voi jummijammi

Sivun alkuun

Vastaus

Aihe on jo aika vanha, joten et voi enää vastata siihen.

Kirjautuminen

Haku

Tehtävät