Mysql query works in phpmyadmin but not in php (due to date)

Question

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%'

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

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

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.

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 Code

$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;
}

could you please dump out the query string that's constructed in your php script to compare with the query that's working in phpmyadmin? — flauntster
– flauntster, Commented Aug 28, 2017 at 6:26
Update your post with relevant php code what you tried so far . — JYoThI
– JYoThI, Commented Aug 28, 2017 at 6:28
I've added PHP code. BTW the script gets called with ajax if that's relevant — Whip
– Whip, Commented Aug 28, 2017 at 6:36

ScaisEdge · Accepted Answer · 2017-08-29 08:37:17Z

1

you should compare date with date not date with datetime

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

so

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%'

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%'

or as used by 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%'

edited Aug 29, 2017 at 8:37

answered Aug 28, 2017 at 6:33

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Whip Over a year ago

The table columns also contain time and it should also get considered

ScaisEdge Over a year ago

You use DATE(.....) this return only the date part or the date time .. and a date should be compared with a date

Jens Over a year ago

You should suggest to use prepared Statements to prepvent SQL injection

Whip Over a year ago

Thanks . It worked with end_date >= DATE_FORMAT('$now','%Y-%m-%d %H:%i:%s') and I learned something new. Does anyone mind explaining why previous code worked in phpmyadmin?

ScaisEdge Over a year ago

simply because you assign literal value that are correctly managed by sql engine . instead fo var that need a proper conversion for adatp the string to query requrements .. well if my answer is right , or lead you to the right solution you should mark it as accepted ..

|

Collectives™ on Stack Overflow

Mysql query works in phpmyadmin but not in php (due to date)

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related