De PHPhulp kroeg
Data ophalen van vandaag met AND OR
Ik wil in de opencart database records ophalen van de bestellingen die vandaag geplaatst zijn.
In de tabel oc_order staan de volgende velden:
order_status_id / 1 en 5 in mijn geval zijn in behandeling of verzonden welke die moet hebben.
date_added
Ik heb de volgende query:
[code/]
Code (php)
1
2
3
4
2
3
4
<?php
$q = 'SELECT * FROM oc_order WHERE order_status_id = 1 || order_status_id = 15 AND YEAR(date_added) = YEAR(NOW()) AND MONTH(date_added) = MONTH(NOW()) AND DAY(date_added) = DAY(NOW()) ';
?>
$q = 'SELECT * FROM oc_order WHERE order_status_id = 1 || order_status_id = 15 AND YEAR(date_added) = YEAR(NOW()) AND MONTH(date_added) = MONTH(NOW()) AND DAY(date_added) = DAY(NOW()) ';
?>
Zonder de order_status_id vergelijking werkt de code goed dus krijg ik enkel de order van vandaag maar met de vergelijking of de status van de order op verzonden of in behandeling staat helaas niet meer.
Code (php)
1
2
3
4
2
3
4
<?php
$q = 'SELECT * FROM oc_order WHERE (order_status_id = 1 OR order_status_id = 15) AND (YEAR(date_added) = YEAR(NOW()) AND MONTH(date_added) = MONTH(NOW()) AND DAY(date_added) = DAY(NOW())) ';
?>
$q = 'SELECT * FROM oc_order WHERE (order_status_id = 1 OR order_status_id = 15) AND (YEAR(date_added) = YEAR(NOW()) AND MONTH(date_added) = MONTH(NOW()) AND DAY(date_added) = DAY(NOW())) ';
?>
Code (php)
1
2
3
2
3
<?php
$q = 'SELECT * FROM oc_order WHERE order_status_id IN (1,15) AND YEAR(date_added) = YEAR(NOW()) AND MONTH(date_added) = MONTH(NOW()) AND DAY(date_added) = DAY(NOW()) ';
?>
$q = 'SELECT * FROM oc_order WHERE order_status_id IN (1,15) AND YEAR(date_added) = YEAR(NOW()) AND MONTH(date_added) = MONTH(NOW()) AND DAY(date_added) = DAY(NOW()) ';
?>
Code (php)
1
AND YEAR(date_added) = YEAR(NOW()) AND MONTH(date_added) = MONTH(NOW()) AND DAY(date_added) = DAY(NOW())
Kan dat niet korter? Je bent de query in PHP aan het behandelen / zet MySQL nodeloos aan (extra) rekenwerk.
Waarom niet
Code (php)
1
2
3
2
3
<?php
$q = '... WHERE date_added = CURDATE()';
?>
$q = '... WHERE date_added = CURDATE()';
?>
of
?
In het laatste geval kun je er nog iets generiekers van maken waarbij je de bestellingen van dag X (X variabel) opvraagt.
Het liefst deze omgebouwd naar week, maand, jaar.
Code (php)
1
2
3
4
2
3
4
<?php
$q = 'SELECT * FROM oc_order WHERE (order_status_id = 1 OR order_status_id = 15) AND YEAR(date_added) = YEAR(NOW()) AND MONTH(date_added) = MONTH(NOW()) AND DAY(date_added) = DAY(NOW()) ';
$result = $database->query($q);
?>
$q = 'SELECT * FROM oc_order WHERE (order_status_id = 1 OR order_status_id = 15) AND YEAR(date_added) = YEAR(NOW()) AND MONTH(date_added) = MONTH(NOW()) AND DAY(date_added) = DAY(NOW()) ';
$result = $database->query($q);
?>
Alvast bedankt!