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;