asked on

PHP Return Data within 60 days of todays date.

Hi experts, I have a data field, saved as varchar I know this is not practical but i would like to keep it like this please.

My question is how would i only return data from the database from todays date. and 60 days into the future? for example todays date is the 05/04/2011 and 60 days from now would be 05/06/2011 i would need all data between them two dates getting from the database.

Many thanks for your help.

The query i have so far is the following.

$sql = "SELECT customer_name, account_number, mobile_number, end_contract
FROM customer_details
ORDER BY end_contract ASC";

How would i change this to get my desired outcome?

Many thanks for your time.

Kalpan

please modify the query as below

$sql = "SELECT customer_name, account_number, mobile_number, end_contract
FROM customer_details where end_contract BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 60 DAY)
ORDER BY end_contract ASC";

NeoAshura

ASKER

I thought that CURDATE only worked if the field was set to "DATE" mine is Varchar

Kalpan

which format you are having for the end_contract as string

i suppose if you have 2011-04-05 to STR_TO_DATE('2011-04-05', 'Y-m-d')

than try this

$sql = "SELECT customer_name, account_number, mobile_number, end_contract
FROM customer_details where STR_TO_DATE(end_contract, 'Y-m-d') BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 60 DAY)
ORDER BY end_contract ASC";

NeoAshura

ASKER

I tried your query, but my date format is d-m-y in the database. so i change the query to the following.

$sql = "SELECT customer_name, account_number, mobile_number, end_contract
FROM customer_details where STR_TO_DATE(end_contract, 'd-m-Y') BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 60 DAY)
ORDER BY end_contract ASC";

Is that right?? Its just when i ran the query it returned nothing in the table.

when i ran it in mysql it reutrned 0 rows of data.

NeoAshura

ASKER

any other ideas or where i might be going wrong?

NeoAshura

ASKER

sorry my format is varchar and is dd-mm-yyyy

NeoAshura

ASKER

can anyone assist?

Kalpan

did u check the records that should fetch with the current query conditions, i suppose there wouldn't be any records.

Please post you sql via exporting the data for the records that might fetch the data. least 5-10 records.

Should be able to replicate the issue and help you out with the query.

Thanks

Kalpan

NeoAshura

ASKER

Hi Kalmax is this what you need?

$sql = "SELECT customer_name, account_number, mobile_number, end_contract
FROM customer_details where STR_TO_DATE(end_contract, 'd-m-Y') BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 60 DAY)
ORDER BY end_contract ASC";
$result4=mysql_query($sql);
if($result4) {
$count=mysql_num_rows($result4);
$my_table="<html><table><tr><td>Customer Name</td><td>Account Number</td><td>Mobile Number</td><td>End contract</td></tr>";

Kalpan

no i would need the table records atleast 5-10 which should be fetched as per your requirement..

I would be only able to check if the query works or not...since you said the query returns 0 rows when using with mysql query browser

Thanks

NeoAshura

ASKER

Is this what you wanted?

-- phpMyAdmin SQL Dump
-- version 3.3.9
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Apr 07, 2011 at 02:56 PM
-- Server version: 5.1.54
-- PHP Version: 5.3.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `newdatabase`
--

-- --------------------------------------------------------

--
-- Table structure for table `customer_details`
--

CREATE TABLE IF NOT EXISTS `customer_details` (
  `customer_name` varchar(50) NOT NULL,
  `mobile_number` varchar(11) NOT NULL,
  `month_conn` varchar(11) NOT NULL,
  `start_contract` varchar(11) NOT NULL,
  `end_contract` varchar(11) NOT NULL,
  `IMEI` varchar(14) NOT NULL,
  `type_acc` varchar(3) NOT NULL,
  `account_number` varchar(15) NOT NULL,
  `term` varchar(15) NOT NULL,
  `tariff` varchar(30) NOT NULL,
  `model` varchar(50) NOT NULL,
  `handset_supplied` varchar(15) NOT NULL,
  `staff_member` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `customer_details`
--

INSERT INTO `customer_details` (`customer_name`, `mobile_number`, `month_conn`, `start_contract`, `end_contract`, `IMEI`, `type_acc`, `account_number`, `term`, `tariff`, `model`, `handset_supplied`, `staff_member`) VALUES
('MRS ANNA NORRIS', '07730528189', '01/01/2009', '01/01/2009', '01/01/2011', '123456789', 'CON', '2745031', '', '', 'Bold 9700', '', 'Mark Nichols'),
('MRS ANNA NORRIS', '07770770969', '01/06/2009', '01/06/2009', '01/06/2011', '987654321', 'UPG', '2745031', '', '', 'IPhone 4 ', '', 'Mark Nichols'),
('MRS ANNA NORRIS', '07793386377', '01/06/2009', '01/06/2009', '01/06/2011', '123456798', 'UPG', '2745031', '', '', 'Blackberry 8750', '', 'Mark Nichols'),
('MRS ANNA NORRIS', '01254785478', '01/07/2009', '01/07/2009', '01/07/2011', '111111111', 'CON', '2745031', '', '', 'Nokia C300', '', 'Mark Nichols'),
('A COOKE & SON', '07412589658', '01/06/2009', '01/06/2009', '01/06/2011', '14785236974', 'CON', '4654793', '', '', '', '', 'Phil Hutchinson'),
('A COOKE & SON', '07814793921', '01/06/2009', '01/06/2009', '01/06/2011', '123456789', 'UPG', '4654793', '', '', '', '', 'Phil Hutchinson'),
('A COOKE & SON', '077777777777', '01/06/2009', '01/06/2009', '01/06/2011', '1234546789', 'UPG', '4654793', '', '', '', '', 'Phil Hutchinson'),
('Mark Nichols', '0787', 'dsf', 'dsfsd', 'dsf', 'jiji', 'iji', 'jij', 'ij', 'iji', '', '', 'Kate Snowdon');

Open in new window

Kalpan

here is the successful query that returns the 5 rows.

SELECT customer_name, account_number, mobile_number, end_contract,DATE_ADD(CURDATE(), INTERVAL 60 DAY),CURDATE(),STR_TO_DATE('01/05/2011','y')
FROM customer_details WHERE STR_TO_DATE(end_contract, '%d/%m/%Y') BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 60 DAY)
ORDER BY end_contract ASC;

please modify

$sql = "SELECT customer_name, account_number, mobile_number, end_contract
FROM customer_details where STR_TO_DATE(end_contract, '%d/%m/%Y') BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 60 DAY)
ORDER BY end_contract ASC";

Hope that helps now...

Thanks

kalpan

ASKER CERTIFIED SOLUTION

Kalpan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NeoAshura

ASKER

cheers buddy worked like a charm