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?
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";
0
NeoAshuraAuthor Commented:
I thought that CURDATE only worked if the field was set to "DATE" mine is Varchar
0
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";
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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

0
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

0
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
0
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

0

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
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.