Selecten op voorraad en laagste prijs
Ik heb deze query:
Code (php)
1
2
3
4
5
2
3
4
5
SELECT *
FROM products
WHERE product_id="123"
ORDER BY voorraad DESC, prijs ASC
LIMIT 0,1
FROM products
WHERE product_id="123"
ORDER BY voorraad DESC, prijs ASC
LIMIT 0,1
Dit werkt bijna goed, maar wil het als volgt:
Van de producten die op voorraad zijn de laagste prijs. Is er geen voorraad? Laagste prijs! Er moet altijd een resultaat komen! Moet dus via "order by"?
Het is nu zo dat wanneer een product veel voorraad heeft deze voorgaat op product met weinig voorraad maar wel met een lagere prijs.
Als ik "voorraad DESC, prijs ASC" omdraai krijg ik wel de laagste prijs maar die heeft soms geen voorraad.
Hoe dit te realiseren?
Alvast bedankt!
Gewijzigd op 22/06/2012 14:51:22 door Roy -
WHERE ... AND voorraad > 0
Gewijzigd op 22/06/2012 14:55:19 door - SanThe -
Hiermee "WHERE ... AND voorraad > 0" bedoel je:
Code (php)
1
2
3
4
5
2
3
4
5
SELECT *
FROM products
WHERE product_id="123" AND voorraad > 0
ORDER BY prijs ASC
LIMIT 0,1
FROM products
WHERE product_id="123" AND voorraad > 0
ORDER BY prijs ASC
LIMIT 0,1
?
Als er niets op voorraad is wil ik toch de beste prijs hebben. Op deze manier krijg ik geen resultaat meer.
Toch bedankt voor het meedenken!
Gewijzigd op 22/06/2012 15:22:19 door Roy -
Geef eens een voorbeeldje van welke je gegevens je kan hebben en wat je er uit geselecteerd wilt hebben.
Producten hebben ook een uniek id maar deze wordt eigenlijk niet gebruik. Er zijn meerdere dezelfde producten. Hiervan moet de meest gunstige (zie uitleg eerder) geselecteerd worden.
Voorbeelden met gegevens en welk resultaat ik wil hebben:
product_id - voorraad - prijs
123 - 0 - 10
123 - 2 - 11
123 - 1 - 12
Hiervan dient de 2e geselecteerd te worden omdat:
Er is voorraad, daarvan de goedkoopste kiezen.
product_id - voorraad - prijs
123 - 0 - 12
123 - 0 - 11
123 - 0 - 12
Hiervan dient de 2e geselecteerd te worden omdat:
Er is geen voorraad, de goedkoopste kiezen.
product_id - voorraad - prijs
123 - 1 - 12
123 - 2 - 11
123 - 3 - 12
Hiervan dient de 2e geselecteerd te worden omdat:
Er is voorraad, de goedkoopste kiezen.
Gewijzigd op 22/06/2012 16:27:04 door Roy -
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DELIMITER $$
CREATE PROCEDURE SelectPerfectProduct (IN pid INT(11))
BEGIN
DECLARE pist INT(11);
SELECT MAX(voorraad) FROM products WHERE product_id = pid INTO pist;
IF pist = 0 THEN
SELECT * FROM products
WHERE
prijs =
(SELECT MIN(prijs) FROM products WHERE product_id = pid)
AND product_id = pid;
ELSE
SELECT * FROM products
WHERE
prijs =
(SELECT MIN(prijs) FROM products WHERE product_id = pid AND voorraad > 0)
AND product_id = pid;
END IF;
END$$
DELIMITER ;
CREATE PROCEDURE SelectPerfectProduct (IN pid INT(11))
BEGIN
DECLARE pist INT(11);
SELECT MAX(voorraad) FROM products WHERE product_id = pid INTO pist;
IF pist = 0 THEN
SELECT * FROM products
WHERE
prijs =
(SELECT MIN(prijs) FROM products WHERE product_id = pid)
AND product_id = pid;
ELSE
SELECT * FROM products
WHERE
prijs =
(SELECT MIN(prijs) FROM products WHERE product_id = pid AND voorraad > 0)
AND product_id = pid;
END IF;
END$$
DELIMITER ;
Je kan het dan opvragen via:
CALL(SelectPerfectProduct)
Heb nog nooit gebruik gemaakt van een stored procedure. Er wordt in de website waar dit moet gebeuren nog gebruik gemaakt van mysql ipv mysqli of beter pdo. Is dit ook uit te voeren via mysql?
Hoe dit uit te voeren en het resultaat eruit halen? Gewoon in mysql_query gooien? En het resultaat eruit halen met bijv. mysql_fetch_assoc?
hier een tut over stored procedures
Even geprobeerd via phpmyadmin bij SQL, gaat goed! :)
Maar.. een stored procedure wordt in de database opgeslagen (edit: jazeker, in de information_schema)?
Ik voeg dit dus één keer toe (edit: ja want het wordt opgeslagen):
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<?php
mysql_query("
DELIMITER $$
CREATE PROCEDURE SelectPerfectProduct (IN pid INT(11))
BEGIN
DECLARE pist INT(11);
SELECT MAX(voorraad) FROM producten_leverancier WHERE product_id = pid INTO pist;
IF pist = 0 THEN
SELECT * FROM producten_leverancier
WHERE
prijs =
(SELECT MIN(prijs) FROM producten_leverancier WHERE product_id = pid)
AND product_id = pid;
ELSE
SELECT * FROM producten_leverancier
WHERE
prijs =
(SELECT MIN(prijs) FROM producten_leverancier WHERE product_id = pid AND voorraad > 0)
AND product_id = pid;
END IF;
END$$
DELIMITER ;
");
?>
mysql_query("
DELIMITER $$
CREATE PROCEDURE SelectPerfectProduct (IN pid INT(11))
BEGIN
DECLARE pist INT(11);
SELECT MAX(voorraad) FROM producten_leverancier WHERE product_id = pid INTO pist;
IF pist = 0 THEN
SELECT * FROM producten_leverancier
WHERE
prijs =
(SELECT MIN(prijs) FROM producten_leverancier WHERE product_id = pid)
AND product_id = pid;
ELSE
SELECT * FROM producten_leverancier
WHERE
prijs =
(SELECT MIN(prijs) FROM producten_leverancier WHERE product_id = pid AND voorraad > 0)
AND product_id = pid;
END IF;
END$$
DELIMITER ;
");
?>
?
Daarna de code eruit halen en elke keer als ik dit wil uitvoeren dit runnen (edit: ja):
Code (php)
1
2
3
4
5
2
3
4
5
<?php
$query = mysql_query("CALL SelectPerfectProduct(17117);");
$resultaat = mysql_fetch_assoc($query);
$beste = $resultaat['prijs'];
?>
$query = mysql_query("CALL SelectPerfectProduct(17117);");
$resultaat = mysql_fetch_assoc($query);
$beste = $resultaat['prijs'];
?>
edit:
Wat ik tevens niet tegen kom is de limit, krijg ik met deze stored precedure altijd één resultaat?
Als ik het uitvoer via phpmyadmin krijg ik netjes resultaat maar via php krijg ik:
can't return a result set in the given context
Toevoeging op 26/06/2012 17:35:42:
Inmiddels weer het een en ander geprobeerd en kwam de volgende bug tegen:
https://bugs.php.net/bug.php?id=42548
Kan het zijn dat dit het probleem veroorzaakt? In phpmyadmin werkt het wel... lijkt mij niet?
Gewijzigd op 26/06/2012 14:04:27 door Roy -
Of met de gebruikte extensie? Ik dacht namelijk dat Myadmin dat zelf detecteerde?
PHPMyAdmin gebruikt ook PHP toch?
Ik duik nog een keer in de stored procedures maar voor nu zo sneller klaar.
Hartelijk dank! Ondanks het anders gedaan te hebben wel weer wat geleerd!