MySQL查询在phpmyadmin中有效，但在php中无效(由于日期) [英] Mysql query works in phpmyadmin but not in php (due to date)

问题描述

我有一个简单的查询，该查询可在phpmyadmin中工作，但不适用于php脚本.这是生成的查询:

I have a simple query that works in phpmyadmin but not in php script. Here is the query that gets generated:

SELECT id AS couponID, percentage_off, usage_count, used_count FROM coupons WHERE `code` = 'DANGER' AND active_status = '1' AND deleted = '0' AND  DATE(start_date) <= '2017-08-28 11:24:31' AND  DATE(end_date) >= '2017-08-28 11:24:31' AND category LIKE '%cLevel%'

当我使用mysqli_num_rows时，它给了我0，而mysqli_fetch_assoc给了我null

When I user mysqli_num_rows, it gives me 0 and mysqli_fetch_assoc gives me null

但是当我在phpmyadmin中运行此确切查询时，它给了我一行结果.

But when I run this exact query in phpmyadmin, it gives me a result, one row.

我将其范围缩小到了DATE(end_date)部分.如果删除它，我将得到结果.我不明白这是怎么了.

I've narrowed it down to the DATE(end_date) part. If I remove that, I get the result. I don't get it what's wrong here.

这是一个重新创建我的表的SQL:

Here's an SQL to recreate my table:

CREATE TABLE `coupons` (
  `id` int(10) NOT NULL,
  `name` varchar(200) NOT NULL,
  `code` varchar(20) NOT NULL,
  `percentage_off` int(5) NOT NULL,
  `category` varchar(200) DEFAULT NULL,
  `service_id` varchar(200) NOT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `added_on` datetime NOT NULL,
  `added_by` int(10) NOT NULL,
  `updated_on` datetime DEFAULT NULL,
  `usage_count` int(10) NOT NULL,
  `used_count` int(10) NOT NULL,
  `active_status` int(2) NOT NULL,
  `deleted` int(2) NOT NULL,
  `deleted_on` date DEFAULT NULL,
  `deleted_by` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `coupons`
--

INSERT INTO `coupons` (`id`, `name`, `code`, `percentage_off`, `category`, `service_id`, `start_date`, `end_date`, `added_on`, `added_by`, `updated_on`, `usage_count`, `used_count`, `active_status`, `deleted`, `deleted_on`, `deleted_by`) VALUES
(1, 'Funny 23 Years', 'FUN23', 50, 'Tutors', '', '2017-02-16 00:00:00', '2017-02-28 00:00:00', '0000-00-00 00:00:00', 2, '2017-02-15 15:04:00', 50, 0, 0, 1, '2017-05-29', '46'),
(2, 'TEST', 'FUN40', 40, 'service', '1,6', '2017-07-23 15:25:00', '2017-07-24 15:25:00', '2017-02-15 13:46:00', 2, '2017-07-23 15:26:00', 40, 0, 0, 0, NULL, ''),
(3, 'TEST', 'FUN10', 10, 'workplace,expert,cLevel,course,tutors,service', '', '2017-02-16 00:00:00', '2017-04-30 00:00:00', '2017-02-15 14:45:00', 2, '2017-04-19 15:41:00', 100, 5, 0, 0, NULL, ''),
(4, 'TEST', 'fun40', 40, 'Service', '6', '2017-02-16 10:08:00', '2017-02-28 11:57:00', '2017-02-15 13:49:00', 2, NULL, 40, 0, 0, 1, '2017-02-15', '2'),
(5, 'TEST', 'TEST99', 99, 'workplace,expert,cLevel,course,tutors', '', '2017-07-06 16:55:00', '2017-08-31 00:00:00', '2017-02-16 12:14:00', 2, '2017-08-14 19:34:55', 100, 55, 1, 0, NULL, ''),
(6, 'TEST', 'DANGER', 90, 'workplace,expert,cLevel,course,tutors', '', '2017-03-31 18:23:00', '2017-10-30 18:23:00', '2017-02-16 12:58:00', 2, '2017-08-28 10:48:40', 100, 12, 1, 0, NULL, ''),
(7, 'Matrix March', 'MATMAR', 10, 'service', '2', '2017-03-20 23:00:00', '2017-03-31 00:00:00', '2017-03-20 18:48:00', 46, '2017-03-20 18:52:00', 10, 0, 0, 0, NULL, '');

ALTER TABLE `coupons`
  ADD PRIMARY KEY (`id`);

PHP代码

$promoCodeQ = "SELECT id AS couponID, percentage_off, usage_count, used_count FROM coupons WHERE `code` = '$discountCode' AND active_status = '1' AND deleted = '0' AND DATE(start_date) <= '$now' AND DATE(end_date) >= '$now' AND category LIKE '%$category%'";
$output['query'] = $promoCodeQ;
$promoCode = mysqli_query($connect, $promoCodeQ);
if($promoCode){
    $output['num_rows'] = mysqli_num_rows($promoCode);
    $temp = [];
    while ($row = mysqli_fetch_assoc($promoCode)) {
        $temp[] = $row;
    }
    $output['output'] = $temp;
}

推荐答案

您应该比较带日期的日期而不是带日期时间的日期

you should compare date with date not date with datetime

AND  DATE(start_date) <= '2017-08-28' 
AND  DATE(end_date) >= '2017-08-28' 

如此

SELECT id AS couponID, percentage_off, usage_count, used_count 
FROM coupons 
WHERE `code` = 'DANGER' 
AND active_status = '1' 
AND deleted = '0' 
AND  DATE(start_date) <= '2017-08-28' 
AND  DATE(end_date) >= '2017-08-28' 
AND category LIKE '%cLevel%'

如果您在php中使用var，则应使用str_to_date正确转换，例如:

in you case you are using var in php and you should convert properly using str_to_date eg:

 str_to_date( '$now'),'%Y-%m-%d');

SELECT id AS couponID, percentage_off, usage_count, used_count 
FROM coupons 
WHERE `code` = 'DANGER' 
AND active_status = '1' 
AND deleted = '0' 
AND  DATE(start_date) <= str_to_date( '$now'),'%Y-%m-%d')
AND  DATE(end_date) >= str_to_date( '$now'),'%Y-%m-%d')
AND category LIKE '%cLevel%'

或OP所使用

   str_to_date( '$now'),'%Y-%m-%d %H:%i:%s');

  SELECT id AS couponID, percentage_off, usage_count, used_count 
  FROM coupons 
  WHERE `code` = 'DANGER' 
  AND active_status = '1' 
  AND deleted = '0' 
  AND  start_date <= DATE_FORMAT('$now','%Y-%m-%d %H:%i:%s')
  AND  end_date >= DATE_FORMAT('$now','%Y-%m-%d %H:%i:%s')
  AND category LIKE '%cLevel%'

这篇关于MySQL查询在phpmyadmin中有效，但在php中无效(由于日期)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！