Asked  7 Months ago    Answers:  5   Viewed   70 times

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

 Answers

34

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%'
Friday, May 28, 2021
 
osondoar
answered 7 Months ago
50

my friend. There can be few reasons of this error.

  1. php.ini has wrong value for mysql.allow_local_infile. Must be - mysql.allow_local_infile = On

  2. If you are working locally you can try to remove the LOCAL from your SQL query. Maybe MySQL server settings just don't allow you to use the LOCAL keyword

  3. Only if you use cPanel: The problem is the phpMyAdmin that is built into the cPanel interface doesn't use the php built with apache. So you can rebuild apache all day long and it will not change how php is compiled [from cPanel forum]

Hope this will help you.

Wednesday, March 31, 2021
 
OMGKurtNilsen
answered 9 Months ago
95

I'm guessing that meetingid has no value.

Trapping on an error as suggested will not always work. If the query syntax is valid then no error is triggered, but you get no results.

Add an echo to your code:

$q1d = "SELECT max(meetingid) as newmeeting FROM meetings LIMIT 1)";
$r1d = mysqli_query ($dbc, $q1d);
echo mysqli_error() . "<br/>$q1d";

Then if necessary copy and paste the echoed query into the SQL query box in phpMyAdmin.

Wednesday, March 31, 2021
 
Shreejibawa
answered 9 Months ago
39

yo need create the user "pma" in mysql or change this lines(user and password for mysql):

/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = 'pma'; 
$cfg['Servers'][$i]['controlpass'] = '';

Linux: /etc/phpmyadmin/config.inc.php

Tuesday, July 13, 2021
 
ShadowZzz
answered 5 Months ago
65

You have cyrillic characters in your query, so it may be necessary to set mySQL connection encoding. If you are using utf-8, insert following line after mysqli_connect:

mysqli_query($conn, "SET NAMES 'utf8'");

Or if your script is saved in windows-1251, use the following: mysqli_query($conn, "SET NAMES 'cp1251'");

For more information about connection character sets and encodings please see the manual

And why does the query work in phpMyAdmin? Because it probably sets encoding for you in the background.

Wednesday, August 25, 2021
 
Byron Whitlock
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share