Search by date range by submitting serach form request

14 posts by 4 authors in: Forums > CMS Builder
Last Post: July 2, 2015   (RSS)

By nmsinc - June 20, 2015

See form below for FROM and TO dates posted to URL. I'm having problems with the POST page displaying only records within the specified FROM and TO range!

<form action="../traffic-report.php?" method="post">
Traffic Report FROM &nbsp;<input type="text" name="min_date" value="<?php echo trim(@$_REQUEST['min_date'], " 00:00:00"); ?>" id="date_from" />
&nbsp;
TO &nbsp;<input type="text" name="max_date" value="<?php echo trim(@$_REQUEST['max_date'], " 00:00:00"); ?>" id="date_to" />
<input type="submit" name="submit" value="Click Here" />
</form>

See $where statement for traffic-report.php below:

$where = "createdDate < 'max_date' and createdDate > 'min_date'";

Any help or suggestion would be very much appreciated! - Thanks - nmsinc

nmsinc

By gkornbluth - June 21, 2015 - edited: June 21, 2015

Hi nmsinc,

Have a look at these, they might offer some insights.

http://www.interactivetools.com/forum/forum-posts.php?postNum=2232818

http://www.interactivetools.com/forum/forum-posts.php?postNum=2232778

Best

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By gregThomas - June 25, 2015

Hi nmsinc,

Could you post the code you've got for your page, and let me know what the exact issue is? 

Jerry's  suggestion of using the getRecords built in search functionality could be the easiest solution. You can read the documentation on this feature here:

http://www.interactivetools.com/docs/cmsbuilder/viewer_search.html

Cheers,

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By nmsinc - June 27, 2015

Hi Dave,

I have the following form using a date picker:

<table style="width: 100%" class="auto-style6">
  <tr>
  <td class="auto-style7" style="width: 730px">
  <ul>
   <form action="../reports/traffic-stop-report.php?" method="post">
   <li>
   Traffic Stop Report FROM &nbsp;<input type="text" name="min_date" value="<?php echo trim(@$_REQUEST['min_date'], " 00:00:00"); ?>" id="date_from" />
   &nbsp;

   TO &nbsp;<input type="text" name="max_date" value="<?php echo trim(@$_REQUEST['max_date'], " 00:00:00"); ?>" id="date_to" />
   <input type="submit" name="submit" value="Click Here" />
   </li>
   </form>
  </ul>
  </td>
    </tr>
 </table>   

When the page is posted, I only desire the records where the 'createdDate' match the dates picked from 'min_date' to 'max_date' !

Thanks - nmsinc

nmsinc

By Dave - June 30, 2015

Hi nmsinc, 

Are you generating your own WHERE statement or using the built in CMSB search features? 

If you're using the built in CMSB search features you need the _min _max on the end of the fieldname: 
http://www.interactivetools.com/docs/cmsbuilder/viewer_search.html

If you're creating your own WHERE statement can you print it out and show me the code that generates it and it's value when you print it? 

Also, an advanced trick, if you want to see the MySQL that getRecords() is generating you can temporarily add this option: 

'debugSql'      => true,  

Let me know what you find, thanks!

Dave Edis - Senior Developer
interactivetools.com

By nmsinc - July 1, 2015

Hi Dave,

I want to use the date sequence at the end of the URL, however; I'm using a datepicker for 'min' and 'mix' dates and I'm not familiar with how to separate the year, month and day minimums and maximums at the end of the URL when using a datepicker. Any help would be greatly appreciated!

Thanks - nmsinc

nmsinc

By Dave - July 1, 2015

Hi nmsinc, 

Can you have it print out all the form values that are getting submitted?  Just add this on your POST page: 

<?php showme($_REQUEST); ?>

And what is the name of the date field you want to do a search on? 

Thanks!

Dave Edis - Senior Developer
interactivetools.com

By nmsinc - July 1, 2015

Here it is:

Array
(
    [min_date] => (this is the datepicker min date request)
    [max_date] => (this is the datepicker max date request)
    [submit] => Click Here
    [date_year_min] => 
    [date_month_min] => 
    [date_day_min] => 
    [date_year_max] => 
    [date_month_max] => 
    [date_day_max] => 
)

Thanks - nmsinc

nmsinc

By Dave - July 1, 2015

But what's the format of the data in min_date and max_date?  We need some data to try and piece together into a valid date to pass to getRecords() to do the date filtering.

Dave Edis - Senior Developer
interactivetools.com