1

I try to find a solution about this request. The result is always wrong. The only data inserted is for the coupon :'test'.

Tk

Inside Database

coupons_date_start = NULL
coupons_date_end = NULL

If I remove these elements, the request works.

or coupons_date_start <= CURDATE() or coupons_date_start = :coupons_date_start
coupons_date_end >= CURDATE or coupons_date_end = :coupons_date_start

{ 'coupons_id' => string(4) "test" 'coupons_description' => string(4) "test" 'coupons_discount_amount' => string(14) "5.000000000000" 'coupons_discount_type' => string(5) "fixed" 'coupons_date_start' => NULL 'coupons_date_end' => NULL 'coupons_max_use' => string(1) "0" 'coupons_min_order' => string(6) "0.0000" 'coupons_min_order_type' => string(5) "price" 'coupons_number_available' => string(1) "0" 'coupons_create_account_b2c' => string(1) "0" 'coupons_create_account_b2b' => string(1) "0" 'coupons_twitter' => string(1) "0" }

the request

  $QcheckCode = $OSCOM_Db->prepare('select *
                                    from :table_discount_coupons
                                    where coupons_id = :coupons_id
                                    and (coupons_date_start is null
                                         or coupons_date_start <= CURDATE()
                                         or coupons_date_start = :coupons_date_start
                                         )
                                    and (coupons_date_end >= CURDATE()
                                         or coupons_date_end = :coupons_date_start
                                        )
                                ');

  $QcheckCode->bindValue(':coupons_id', $this->couponCode);
  $QcheckCode->bindValue(':coupons_date_start', '000-00-00 00:00:00');

  $QcheckCode->execute();

  $check_code = $QcheckCode->fetch();

  var_dump($check_code); ==> false

my database

CREATE TABLE `discount_coupons` (
  `coupons_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `coupons_description` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `coupons_discount_amount` decimal(15,12) NOT NULL DEFAULT '0.000000000000',
  `coupons_discount_type` enum('fixed','percent','shipping') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'percent',
  `coupons_date_start` datetime DEFAULT NULL,
  `coupons_date_end` datetime DEFAULT NULL,
  `coupons_max_use` int(3) NOT NULL DEFAULT '0',
  `coupons_min_order` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `coupons_min_order_type` enum('price','quantity') COLLATE utf8_unicode_ci DEFAULT 'price',
  `coupons_number_available` int(3) NOT NULL DEFAULT '0',
  `coupons_create_account_b2c` tinyint(1) NOT NULL DEFAULT '0',
  `coupons_create_account_b2b` tinyint(1) NOT NULL DEFAULT '0',
  `coupons_twitter` int(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
kurama
  • 677
  • 3
  • 8
  • 16
  • Placeholders cant be duplicated. – chris85 Sep 12 '16 at 03:04
  • 1
    Possible duplicate of [PHP's PDO prepared statement: am I able to use one placeholder multiple times?](http://stackoverflow.com/questions/27461763/phps-pdo-prepared-statement-am-i-able-to-use-one-placeholder-multiple-times) – chris85 Sep 12 '16 at 03:05
  • I tried to change with that coupons_date_end = :coupons_date_start1 and $QcheckCode->bindValue(':coupons_date_start1', '000-00-00 00:00:00'); same result – kurama Sep 12 '16 at 03:11
  • What are the data types for your coupons_date_start and coupons_date_end fields - null suggests a string not a datetime data type. –  Sep 12 '16 at 03:15
  • Can you update the question with your current code? – chris85 Sep 12 '16 at 03:18
  • Yes, I update with the database – kurama Sep 12 '16 at 03:18

0 Answers0