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.
LVL 6
NeoAshuraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KalpanCommented:
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";
NeoAshuraAuthor Commented:
I thought that CURDATE only worked if the field was set to "DATE" mine is Varchar
KalpanCommented:
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";
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

NeoAshuraAuthor Commented:
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.
NeoAshuraAuthor Commented:
any other ideas or where i might be going wrong?
NeoAshuraAuthor Commented:
sorry my format is varchar and is dd-mm-yyyy
NeoAshuraAuthor Commented:
can anyone assist?
KalpanCommented:
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
NeoAshuraAuthor Commented:
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>";
KalpanCommented:
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

NeoAshuraAuthor Commented:
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

KalpanCommented:
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
KalpanCommented:
use the following query with group by customer_name

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)
GROUP BY customer_name ORDER BY end_contract ASC

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NeoAshuraAuthor Commented:
cheers buddy worked like a charm
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.