WHERE date = curdate

bonneocc
Invité n'ayant pas de compte PHPfrance

11 juil. 2019, 18:11

Bonjour,

Je suis entrain de filtrer un tableau pour faire sortir un résultat par jour, par exemple aujourd'hui:
$sqlNums = "SELECT * FROM `orders` "
	. " JOIN items_list ON orders.order_name = items_list.iddc"
	. " JOIN members_list ON orders.taken_member = members_list.idm"
		. " WHERE order_type = :order_type"
		. " AND FROM_UNIXTIME(order_time) = :order_date"
		. " ORDER BY order_type ASC";

  $stmtNums = $connect->prepare($sqlNums);

		$order_type		= 0; // en cours

		$dateFormat     = date('Y-m-d');

		echo 'date '.$dateFormat;

		$stmtNums->bindParam(':order_type', $order_type, PDO::PARAM_INT);
		$stmtNums->bindParam(':order_date', $dateFormat, PDO::PARAM_STR);
Quand je fais >= :order_date, je reçois les 2 résultas, mais quand je fais = :order_date, je reçois rien

table MySQL:

--
-- Structure de la table `orders`
--

CREATE TABLE `orders` (
  `ido` int(3) UNSIGNED NOT NULL,
  `order_name` int(3) NOT NULL,
  `order_cat` int(5) NOT NULL COMMENT 'Main category: pizza, chawarma, burger, tacos..',
  `order_price` bigint(4) NOT NULL DEFAULT '0',
  `order_cost` int(3) DEFAULT NULL,
  `order_table` int(2) NOT NULL DEFAULT '0',
  `order_quantity` int(2) NOT NULL DEFAULT '1',
  `order_drink` int(2) NOT NULL DEFAULT '0' COMMENT '0:no drink,1:coca,.. comp drink that goes with the order',
  `order_guest` int(4) NOT NULL COMMENT 'guest id (guest_list)',
  `order_type` int(1) NOT NULL DEFAULT '0' COMMENT '0: en cours, 1: payé cash, 2: payé crédit, 3: annulé, 4: gratuit',
  `order_comment` longtext COMMENT 'if any order is credit, place the name and phone num',
  `order_time` int(20) DEFAULT NULL,
  `taken_member` int(3) DEFAULT NULL COMMENT 'who placed the order',
  `pay_time` int(20) NOT NULL DEFAULT '0',
  `book_time` varchar(20) DEFAULT NULL COMMENT 'if any order is booked for next day and so on',
  `paid_member` int(3) DEFAULT NULL COMMENT 'who sold the order'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Contenu de la table `orders`
--

INSERT INTO `orders` (`ido`, `order_name`, `order_cat`, `order_price`, `order_cost`, `order_table`, `order_quantity`, `order_drink`, `order_guest`, `order_type`, `order_comment`, `order_time`, `taken_member`, `pay_time`, `book_time`, `paid_member`, `iddc`, `item_catid`, `item_name`, `item_tran`, `with_drink`, `item_price`, `item_cost`, `tech_sheet`, `item_status`, `idm`, `profile_key`, `id_group`, `id_level`, `username`, `cashier_status`, `cashier_open_date`, `cashier_close_date`, `password`, `password_update`, `tel`, `tel_update`, `profile_status`, `join_date`, `mem_language`, `profile_modif_date`, `last_login`, `last_logout`, `total_time_spent`, `ip_real`, `ip_proxy`, `toban`, `mem_views`, `member_activation`, `mem_status`) VALUES
(132, 39, 0, 10, NULL, 0, 1, 0, 0, 0, NULL, 1562858775, 1, 0, NULL, NULL, 39, 5, 'Crêpes - Caramel', 'Crepes-Caramel', 0, '10', '0', 1, 1, 1, '4a2c2188', 1, 0, 'Issam', 0, '1562619082', '1562619096', '2fe37bfcb4b52d6b5b770347e85d56438443d1be01172078e8574ced1cdcfc32', '1547119767', '0661337138', '1547119176', 1, '1533399765', 'fr', '', 1562858757, 1562862357, 820800, '41.214.156.122', '', 0, 0, '4c6c5decd66efc4c11d137f99343', 1),
(133, 90, 0, 23, NULL, 0, 1, 0, 0, 0, NULL, 1563020428, 1, 0, NULL, NULL, 90, 10, 'Chawarma Plat', 'Chawarma-Plat', 1, '23', '0', 1, 1, 1, '4a2c2188', 1, 0, 'Issam', 0, '1562619082', '1562619096', '2fe37bfcb4b52d6b5b770347e85d56438443d1be01172078e8574ced1cdcfc32', '1547119767', '0661337138', '1547119176', 1, '1533399765', 'fr', '', 1562858757, 1562862357, 820800, '41.214.156.122', '', 0, 0, '4c6c5decd66efc4c11d137f99343', 1);

Merci à vous

Mammouth du PHP | 1100 Messages

11 juil. 2019, 23:03

https://dev.mysql.com/doc/refman/5.5/en ... m-unixtime
mysql> SELECT FROM_UNIXTIME(1447430881);
-> '2015-11-13 10:08:01'
$dateFormat = date('Y-m-d');
c'est donc normal que le = ne fonctionne pas.

si le champ date est de type datetime, alors il est possible de faire quelque chose comme :
DATEDIFF(now(), order_time) = 0 // aujourd'hui
DATEDIFF(now(), order_time) = 1 // hier

bonneocc
Invité n'ayant pas de compte PHPfrance

12 juil. 2019, 12:14

Le champs date est de type integer