CODEWITHSUNDEEP
phpmysql
masterofdestiny
masterofdestiny

Reputation: 2811

Mysql: Compare is not working properly

I have a mysql query runnning in php .

$result = mysql_query("SELECT status,initiated_by,create_timestamp,extension_id,
    product_code,prospect_name,prospect_email1,prospect_phone1,prospect_title,
    prospect_company,prospect_message,track_id  
    FROM prospect_requests 
    WHERE subscriber_id = '$subscriberid' 
      AND create_timestamp >='$start_date' 
      AND create_timestamp <= '$end_date'");

There is two attributes names $start_date and $end_date

My question is why above query is not returning the information of $end_date

Here is scenario .

I have selected $start_date as 01-02-2013 and $end_date as 09-2-2013 .

But the above query is not returning the details of 9 Feburary .

Please help me out .

Update

When i am placing date as 10 feb it is returning me the details of 9 feb

Table structure 

CREATE TABLE `prospect_requests` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `customer_id` varchar(255) NOT NULL,
  `subscriber_id` varchar(255) NOT NULL,
  `extension_id` varchar(255) DEFAULT NULL,
  `vendor_number` varchar(255) DEFAULT NULL,
  `product_code` varchar(255) DEFAULT NULL,
  `prospect_email1` varchar(255) DEFAULT NULL,
  `prospect_phone1` varchar(255) DEFAULT NULL,
  `prospect_email2` varchar(255) DEFAULT NULL,
  `prospect_phone2` varchar(255) DEFAULT NULL,
  `prospect_title` varchar(255) DEFAULT NULL,
  `prospect_company` varchar(255) DEFAULT NULL,
  `prospect_name` varchar(255) DEFAULT NULL,
  `prospect_message` text,
  `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_timestamp` datetime DEFAULT NULL,
  `status` int(11) DEFAULT '0',
  `track_id` varchar(255) NOT NULL,
  `initiated_by` varchar(255) DEFAULT NULL,
  `subscriber_email` varchar(255) DEFAULT NULL,
  `vendor_email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=142 DEFAULT CHARSET=latin1;

Input : $start_time = 2013-02-01

     $end_time  = 2013-02-09

Upvotes: 2

Views: 284

Answers (3)

Ja͢ck
Ja͢ck

Reputation: 173522

The condition <= '$end_date' is basically the same as <= '$end_date 00:00:00'.

If you need the day itself, you could query like this:

... create_timestamp <= '$end_date 23:59:59'

create_timestamp BETWEEN '$start_date' AND '$end_date 23:59:59' would work too.

Alternative

Add one day programmatically and use <:

$end_date = date('Y-m-d', strtotime("$end_date +1 day"));

... create_timestamp < '$end_date'

Update

You can also cast the timestamp to a date first:

DATE(create_timestamp) BETWEEN '$start_date' AND '$end_date'

Upvotes: 1

user1645055
user1645055

Reputation:

for compare dates you should your BETWEEN Operator in mysql Query like Following

$result = mysql_query("SELECT 
status,initiated_by,create_timestamp,extension_id,product_code,prospect_name,
prospect_email1,prospect_phone1,prospect_title,prospect_company,prospect_message,
track_id  from prospect_requests WHERE subscriber_id = '$subscriberid' AND  
create_timestamp BETWEEN '$start_date' AND '$end_date'");

Please full description of between operator at w2school

if still not working it then please update your question with table structure

i see your update question you are using timestamp field type that cause problem please see following link that might be helpfull Comparing timestamp with date variable (MySQL and PHP)

Upvotes: 0

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324610

Dates should be in the format YYYY-MM-DD.

Also, you can use WHERE create_timestamp BETWEEN '...' AND '...' as a more efficient condition for bounding a value.

Upvotes: 0

Related Questions