PHP: Re: [phpXperts] Re: Querying between 2 dates

sir ANJAN thank you thank you so much for your help... I will try to follow your instruction... Thank thank you so much.... Thanks for sharing your talents.. I will inform once I try your instruction...

mety

MATIAS 'Mety' SORIANO

MIS/IT Director/Consultant/Professor

URL: http://www.shc.edu.ph

E-mail: metysoriano@yahoo.com / metysoriano@shc.edu.ph

Contacts: Tel: (042)3736167 Cel: 09282860545

From: Anjan Bhowmik <anjan011@gmail.com>
To: phpexperts@yahoogroups.com
Sent: Monday, July 18, 2011 22:52:53
Subject: Re: [phpXperts] Re: Querying between 2 dates

Here to demonstrate i will use this table -

-- Table structure for table `dates`

CREATE TABLE IF NOT EXISTS `dates` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`date_normal` datetime NOT NULL,

`date_string` varchar(50) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Now here are 2 column containing date info, one is datetime, and other is a varchar or string containg date in a specific format.

I m also using these data for testing -

-- Dumping data for table `dates`

INSERT INTO `dates` (`id`, `date_normal`, `date_string`) VALUES

(1, '2011-07-19 11:23:49', '07/19/2011'),

(2, '2011-07-02 11:24:18', '07/02/2011'),

(3, '2006-07-18 11:27:11', '18/06/2011');

From the data you can see that the date in string column has the format of mm/dd/yyyy, except the 3rd one, which has dd/mm/yyyy format. Since you are using string, it can hold anything you want, so whoever is adding data in that column muct be very very careful.

Now, lets try converting the date, with this query -

SELECT

id,

date_normal,

date_string,

str_to_date(date_string,'%m/%d/%y') as date_converted

FROM

dates

order by

date_normal desc

This gives you this - http://screencast.com/t/rNmbj32rBT3

As, you can see, the converted date value in 3rd column is null because 18/06/2011 can not be matched by format '%m/%d/%y'. So, you can see that if a date value is null you can't be sure whether your date string column has any value at all or it has value in a different format (again to ensure the format consistancy the program inserting/updating this column value must be very very carefull and validate date date well).

Points to note: (I guess you probably know them anyway)

1) str_to_date is a function which calculates the date value of date_string column content. This is called for every every row of data your query returns. So if you query fetches 100 rows, this function is called 100 times, adding some execution time delays.

2) you can't use the column alias in where clause like this -

SELECT

id,

date_normal,

date_string,

str_to_date(date_string,'%m/%d/%y') as date_converted

FROM

dates

where

date_converted >= '2011-01-01'

order by

date_normal desc

Thats because the where condition is executed to filter the data, which is before the str_to_date function is even called, thus making date_converted column alias unavailable. So to make it work you willl have to call the function in the where clause -

SELECT

id,

date_normal,

date_string,

str_to_date(date_string,'%m/%d/%y') as date_converted

FROM

dates

where

str_to_date(date_string,'%m/%d/%y') >= '2011-01-01'

order by

date_normal desc

or use a temp table -

SELECT

FROM

(

SELECT

id,

date_normal,

date_string,

str_to_date(date_string,'%m/%d/%y') as date_converted

FROM

dates

) as temp

where

date_converted >= '2011-01-01'

order by

date_converted desc

Either way it slows down the query by some percentage :) Hope this helps

On Mon, Jul 18, 2011 at 3:54 PM, Mety <metysoriano@yahoo.com> wrote:

Anjan good day, is there any example in converting date input with varchar datatype to date so that I could query my date column, during report? please help...

--- In phpexperts@yahoogroups.com, Anjan Bhowmik <anjan011@...> wrote:
>
> only by converting it to datetime or timestamp :)
>
> On Thu, Jul 14, 2011 at 10:18 AM, Mety <metysoriano@...> wrote:
>
> > **
> >
> >
> > trans_date = varchar datatype... not date nor datetime. how to query date
> > if it has varchar datatype
> >
> > --- In phpexperts@yahoogroups.com, "ymnoor21" <ymnoor21@> wrote:
> > >
> > >
> > >
> > >
> > >
> > >
> > > Mety,
> > > Can you please let me know what exactly your date column (trans_date)
> > look like? I mean what datatype you are using to store date information? Is
> > it 'date' / 'datetime' or 'text'? If you are not sure what i mean, then
> > please run this query in your database:
> > >
> > > SHOW CREATE TABLE snhf_expense_finance
> > >
> > > It should give you how the table was created. I am quite sure that if you
> > are using 'date' or 'datetime' datatype for your date column (trans_date),
> > then it shouldn't let you store them as 'mm/dd/yyyy' because mysql's default
> > date storing format is 'YYYY-MM-DD'.
> > >
> > > And please stop calling me sir, i am not an old fellow. :P
> > > Also dont put your username and password in your mysql_connect function.
> > You dont need to share it with the group.
> > >
> > > Thanks
> > > Yamin
> > >
> > > --- In phpexperts@yahoogroups.com, "Mety" <metysoriano@> wrote:
> > > >
> > > > sir below is my codes
> > > >
> > > >
> > > > **************************
> > > > <?php
> > > > /* VARIABLE IN MISCELLANEOUS */
> > > > $recno = $_POST['recno'];
> > > > $total_month_expenses = $_POST['total_month_expenses'];
> > > > $trans_date = $_POST['trans_date'];
> > > > $farrow_date = $_POST['farrow_date'];
> > > > $item = $_POST['item'];
> > > > $sub_total_misc = $_POST['sub_total_misc'];
> > > > $description = $_POST['description'];
> > > >
> > > > /* VARIABLE IN FEEDS FINANCING */
> > > > $recno = $_POST['recno'];
> > > > #$trans_date = (date('m/d/y'));;
> > > > $trans_date = $_GET['trans_date'];
> > > >
> > > > $datefrom = (date("Y-m-d", strtotime($_REQUEST['datefrom'])));
> > > > $dateto = (date("Y-m-d", strtotime($_REQUEST['dateto'])));
> > > >
> > > > $cat = $_POST['cat'];
> > > > $batch_no = $_POST['batch_no'];
> > > > $noheads = $_POST['noheads'];
> > > > $feed_type = $_POST['feed_type'];
> > > > $no_bag = $_POST['no_bag'];
> > > > $price = $_POST['price'];
> > > > $sub_total_finance = $_POST['sub_total_finance'];
> > > > $due_date = $_POST['due_date'];
> > > > $sub_total = $no_bag * $price;
> > > >
> > > >
> > > >
> > > > $dbaseconnect = mysql_connect("localhost","metysoriano","mety1124") or
> > die ("Can not connect Database");
> > > > mysql_select_db(snhf,$dbaseconnect) or die ("No database Found");
> > > >
> > > >
> > > > $open_record = "SELECT * from snhf_expense_finance WHERE trans_date
> > BETWEEN '$datefrom' AND '$dateto' AND cat = 'Sow / Breeders' ORDER BY recno
> > DESC";
> > > >
> > > > $open_query = mysql_query($open_record,$dbaseconnect);
> > > > ?>
> > > >
> > > > (displaying result)
> > > >
> > > >
> > > > $total_month_finance_expense = 0;
> > > > while ($open = mysql_fetch_array($open_query))
> > > > {
> > > >
> > > > (displaying record)
> > > > }
> > > > ?>
> > > > *************************
> > > >
> > > > IS THAT CORRECT PROCESS SIR?
> > > >
> > > >
> > > >
> > > > --- In phpexperts@yahoogroups.com, "ymnoor21" <ymnoor21@> wrote:
> > > > >
> > > > >
> > > > >
> > > > > Mety,
> > > > > I didn't get your question this time. What do you mean by "not
> > advisable to utilize date format for query problems"? Do you mean sanity
> > check? If so, you can always do that on your own before passing it to your
> > query. Example:
> > > > >
> > > > > $dateFrom = $_REQUEST['datefrom'];
> > > > > $dateTo = $_REQUEST['dateto'];
> > > > >
> > > > > if($dateFrom != "") {
> > > > > //format the input date to match your database datefrom column;
> > > > > $_dateFrom = date("Y-m-d", strtotime($dateFrom);
> > > > > }
> > > > > else {
> > > > > //use default date / first date record in your data_trans table
> > > > > $_dateFrom = "1950-01-01"; //assuming that your data_trans table
> > //starts from 1950 or you can do a simple query to get the first date.
> > > > > }
> > > > >
> > > > > if($dateTo != "") {
> > > > > //format the input date to match your database dateto column;
> > > > > $_dateTo = date("Y-m-d", strtotime($dateTo);
> > > > > }
> > > > > else {
> > > > > //you can use current date
> > > > > $_dateTo = date("Y-m-d");
> > > > > }
> > > > >
> > > > > $SQL = "SELECT * FROM table WHERE date_trans BETWEEN '$_dateFrom' AND
> > '$_dateTo'";
> > > > >
> > > > > $result = mysql_query($SQL);
> > > > >
> > > > > Now if you say, is it better to use operator (>= , <=) in date
> > comparison rather than using BETWEEN? Then to be simplistic, i'd recommend
> > you do a performance benchmark testing to answer. Sometimes no of records
> > may lead your query optimization to a complete failure. So be careful when
> > you choose one over other.
> > > > >
> > > > > Thanks
> > > > > Yamin
> > > > >
> > > > > --- In phpexperts@yahoogroups.com, "Mety" <metysoriano@> wrote:
> > > > > >
> > > > > > that mean sir date format (d/m/y) is not advisable to utilize to
> > avoid query problems?
> > > > > >
> > > > > >
> > > > > > --- In phpexperts@yahoogroups.com, "ymnoor21" <ymnoor21@> wrote:
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Mety,
> > > > > > > Check your date format. You are taking the date as input in US
> > format, but your database date column / field is in different format
> > (yyyy-mm-dd). So i'd recommend you to take a good look at this:
> > > > > > >
> > > > > > > http://us.php.net/manual/en/function.date.php
> > > > > > >
> > > > > > > However, you can also do this to your date inputs before passing
> > it to your SQL:
> > > > > > >
> > > > > > > $datefrom = date("Y-m-d", strtotime($_REQUEST['datefrom']);
> > > > > > > $dateto = date("Y-m-d", strtotime($_REQUEST['dateto']);
> > > > > > >
> > > > > > > $SQL = "SELECT * FROM table WHERE date_trans BETWEEN '$datefrom'
> > AND '$dateto'";
> > > > > > >
> > > > > > > $result = mysql_query($SQL);
> > > > > > >
> > > > > > > Thanks
> > > > > > > Yamin
> > > > > > >
> > > > > > >
> > > > > > > --- In phpexperts@yahoogroups.com, "M A Hossain Tonu" <tonu71@>
> > wrote:
> > > > > > > >
> > > > > > > > hi mety,
> > > > > > > >
> > > > > > > > 1. before querying mysql use mysql default date format i.e.
> > yyyy-mm-dd
> > > > > > > >
> > > > > > > > 2. $dateArray = explode("/","06/22/2011");
> > > > > > > > $dateFrom = $dateArray[2].'-'.$dateArray[0].'-'.$dateArray[1];
> > > > > > > > //$dateFrom will have 2011-06-22
> > > > > > > > do same for the other date.
> > > > > > > >
> > > > > > > > 3. your query works now.
> > > > > > > >
> > > > > > > > Cheers! :)
> > > > > > > >
> > > > > > > > M A Hossain Tonu
> > > > > > > > http://mahtonu.wordpress.com
> > > > > > > >
> > > > > > > > --- In phpexperts@yahoogroups.com, "Mety" <metysoriano@>
> > wrote:
> > > > > > > > >
> > > > > > > > > I have 2 text boxes DATE FROM and DATE TO. These 2 is a space
> > wherein users can input the date from and date to. This text box will query
> > the date between 2 dates. Below is my example:
> > > > > > > > >
> > > > > > > > > Date From: 06/01/11
> > > > > > > > > Date To: 06/30/11
> > > > > > > > >
> > > > > > > > > When I query my database, the records dated month of June
> > 2010 and 2009 are also displayed. Suppose to be this record should not be
> > displayed since 2011 of June that I wanted to display. Below is my query
> > statement
> > > > > > > > >
> > > > > > > > > select * from table WHERE date_trans BETWEEN $datefrom AND
> > $dateto
> > > > > > > > >
> > > > > > > > > is my process correct?
> > > > > > > > >
> > > > > > > > > please help me\
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > mety
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> >
> >
>
>
>
> --
> Anjan Bhowmik
> Freelance Software & Web Developer
> M : +880 - 1670 - 556419
> E : anjan011@..., info@...
> Blog: http://www.ultrasoftbd.com/
> Site: http://www.onlyfreelancer.com/ - Only for Freelancer's!
>