Hi,
Since I posted this I have tried the strtotime approach and still can’t get it to work. I’ve also tried without the datepicker on the Contact Form 7 end, specifying to the user how to enter the dates. This works in that the dates are now displayed as dd/mm yyyy. No matter what I do I can’t get the filter to work on this date search.
I realise I must be doing something wrong, I just can’t work out what…
I’m guessing that the date search doesn’t work in a filer like to~~/.*$_GET(To).*/i because it has forward slashes in it. Try it like this to~~#.*$_GET(To).*#i
Thanks Michael
Unfortunately that didn’t work either. I’m now trying a different approach due to the importance of date searching on my website. I’ve created more fields in Contact Form 7: Day, Month, Year. I’ve managed to get the fields on the search form (as drop downs).
I’m now struggling with the filter syntax – can you help please?
I need the filter to be as before except for the dates (Day, Month, Year). As it stands if someone searches for 21 Jul 2015 they could get results for all 21’s, all July’s and all Years. They may also want to search on just Month and Year. To make things more complicated there will be two sets of dates From and To, each with Day, Month and Year. This allows users to find listings within dates.
I did find a post on the forum that used this structure:
filter="City~~/$_POST(city)/i&&Blood-Group~~/$_POST(bloodgroup)/i"
but I couldn’t get it to work.
If the filter syntax for dates is not compatible with the straight text searching would a separate form work? I would still need some help with the syntax though…
Many thanks in advance
I suggest you look at this: http://cfdbplugin.com/?page_id=1195
use strtotime to convert your date fields to actual numeric dates, then you an filter them with “>” and “<”
Hi,
Thanks for the above but I’m still struggling. I have now got the dates displaying as dd/mm/yy, but I still can’t search on them using strtotime. Here’s my code:
`[cfdb-html form=”Sign Up” show=”Country,City,AccommType,From,To,Event,Interests,MainImage,submit_time” role=”Anyone” filelinks=”url” filter=”strtotime(from)$_GET(from)&&Country~~/.*$_GET(country).*/i&&City~~/.*$_GET(city).*/i&&AccommType~~/.*$_GET(accommtype).*/i&&to~~/.*$_GET(To).*/i&&Event~~/.*$_GET(event).*/i&&Interests~~/.*$_GET(interests).*/i”]’
Please can you tell me where I’m going wrong!
This doesn’t make sense:
strtotime(from)$_GET(from)
Maybe
strtotime(from)>=strtotime($_GET(from))
Although I’m not sure the plugin will understand the nested syntax.
Hi Michael,
Thanks for your continuing support with this. I have now stripped the filtering down so that the only fields the user can search on are the ‘from’ and ‘to’ dates.
The dates are formatted as dd-mm-yy in both contact form 7 and cfdb. Dates are input in cfdb with the jquery datepicker.
I found a previous support question (http//:cfdbplugin/?page_id=116) which describes exactly what I am trying to achieve:
July 9th, 2014 at 13:21 | #12 Reply | Quote
I created a form with a Date field and a Time field that get populated on submit.
Is there a way to filter between two dates based on user input?
Thanks
Here’s your answer:
July 10th, 2014 at 00:38 | #13 Reply | Quote
@seanw
In a short code you can call the PHP strtotime function to covert the string date to a number and compare it. User input can be passed in as GET or POST parameters. The short code would look something like:
[cfdb-table form="myform" filter="strtotime(date_field)>=$_GET(min_date)&&strtotime(date_field)< =$_GET(max_date)]
Using your example my code is as follows, but it still doesn’t work:
<form action="" method="GET">
<label for="from">Available From:</label> <input id="from" class="datepicker" name="from" type="text" />
<label for="to">Available To:</label> <input id="to" class="datepicker" name="to" type="text" />
<input alt="Submit" src="http://freestayexchange.com/wp-content/uploads/2014/12/search_button.png" type="image" /></form>
[cfdb-html form="Sign Up" show="Country,City,AccommType,From,To,Event,Interests,MainImage,submit_time" role="Anyone" filelinks="url" filter="strtotime(from)>=$_GET(min_date)&&strtotime(to)<=$_GET(max_date)"]
Can you tell me what I am doing wrong? Where do max_date and min_date come from? I’m at my wits end with trying to work this out and have tried every example I can find but still can’t get it right.
Thanks for your patience!
I’m getting lost in the details and I think the example is confusing you. Plus you haven’t taking into account my last post.
So let’s boil this down to a simple example.
I made a form that collect start times as a date field. I made some submissions. In the database I have a “start” field with values that look like this.
start
------------
2015-01-01
2015-02-01
2015-03-01
2014-04-01
Then I put this on a post to query for dates in the DB start field >= start date provided on a search form:
<form action="" method="POST">
Start: <input type="text" name="start" />
<input type="submit" />
</form>
[cfdb-table form="start_end" filter="strtotime(start)>=strtotime($_POST(start))"]
I’m using POST, you could switch to GET if you like.
I think other date formats will work like 03/01/2015 instead of 2015-03-01
Thanks Michael – it worked a dream and I was able to use the UK date format, but there is still a problem. I added a new field “end” and applied the same syntax except for ‘>=’ which I replaced with ‘<=’.
`[cfdb-html form=”start_end” filter=”strtotime(end)<=strtotime($_POST(end))”]’
After lots of testing (it works with ‘>=’) it seems to me that the less than operator is not working. It does not work for Start either, so it’s not a field issue.
So I can filter by Start but not by End…Any ideas?
I also got the following error on the post screen:
Warning: strtotime() expects parameter 1 to be string, array given in /home2/freestay/public_html/wp-content/plugins/contact-form-7-to-database-extension/CFDBFunctionEvaluator.php on line 65
Once I entered a date in the search form the message did not return, it only seems to appear the first time the post/page opens.
Any help appreciated, it’s so close to working now!
OK. I realize that we will have to do this in a bit more complicated way, because it won’t work right when no $_POST parameters are available for the filter.
What you need to do is to create a wrapper short code that detects if the user submitted a start or end values on the search form and set the short code filter dynamically based on that.
To do this, create your own short code call it start_end_wrapper and put in this code:
$tmp = array();
if (isset($_REQUEST['start']) && $_REQUEST['start']) {
$tmp[] = "strtotime(start)>=strtotime({$_REQUEST['start']})";
}
if (isset($_REQUEST['end']) && $_REQUEST['end']) {
$tmp[] = "strtotime(end)<=strtotime({$_REQUEST['end']})";
}
$startEndFilters = implode('&&', $tmp);
if (isset($atts['filter'])) {
$atts['filter'] = $atts['filter'] . '&&' . $startEndFilters;
} else {
$atts['filter'] = $startEndFilters;
}
$sc = $atts['shortcode'];
$shortCodeString = '[' . $sc;
unset($atts['shortcode']);
foreach ($atts as $name => $value) {
$shortCodeString .= " $name=\"$value\"";
}
$shortCodeString .= ']';
if ($content) {
$shortCodeString .= $content . '[/' . $sc . ']';
}
echo $shortCodeString;
In the first few lines, I’m assuming the search form has a “start” and “end” and that your form data has a “start” and “end” field. Change as appropriate.
Back in your page, change the short code you are using:
1. Remove the part of the filter dealing with start and end times. Leave other constraints in
2. Change the short code name form cfdb-table to start_end_wrapper
3. Add shortcode=”cfdb-table” to the short code
Thank you for spending your time on this to help, it is very much appreciated. I have spent hours trying to get this to work and have followed your instructions to the letter (even going so far as to get someone to check it with me!), but to no avail.
I stripped the page down to the bare minimum with the following code (removing all reference to ‘filter’):
<form action="" method="POST">
Start: <input type="text" name="start" />
End: <input type="text" name="end" />
<input type="submit" />
</form>
[cfdb-table form="start_end" show="start,end"]
[/cfdb-table]
You can see the output here:
I then created my own shortcode, start_end_wrapper, as directed. This is the generated code containing the code you supplied:
require_once(ABSPATH . 'wp-content/plugins/contact-form-7-to-database-extension/CFDBFormIterator.php');
$exp = new CFDBFormIterator();
$exp->export($atts['form'], $atts);
while ($row = $exp->nextRow()) {
$tmp = array();
if (isset($_REQUEST['start']) && $_REQUEST['start']) {
$tmp[] = "strtotime(start)>=strtotime({$_REQUEST['start']})";
}
if (isset($_REQUEST['end']) && $_REQUEST['end']) {
$tmp[] = "strtotime(end)<=strtotime({$_REQUEST['end']})";
}
$startEndFilters = implode('&&', $tmp);
if (isset($atts['filter'])) {
$atts['filter'] = $atts['filter'] . '&&' . $startEndFilters;
} else {
$atts['filter'] = $startEndFilters;
}
$sc = $atts['shortcode'];
$shortCodeString = '[' . $sc;
unset($atts['shortcode']);
foreach ($atts as $name => $value) {
$shortCodeString .= " $name=\"$value\"";
}
$shortCodeString .= ']';
if ($content) {
$shortCodeString .= $content . '[/' . $sc . ']';
}
echo $shortCodeString;
}
I then changed cfdb-table to start_end_wrapper and inserted shortcode=”cfdb-table” in the code as you suggested.
The code on my page is:
<form action="" method="POST">
Start: <input type="text" name="start" />
End: <input type="text" name="end" />
<input type="submit" />
</form>
[start_end_wrapper form="start_end" shortcode="cfdb-table" show="start,end"]
[/cfdb-table]
This was the result:
Please can you tell me what I am doing wrong? Am I missing something? I have checked for line breaks etc.
Many thanks
Julie
Hi Michael, Since my last post I have tried changing the order of constraints in the shortcode and leaving constraints out and still can’t get it to work. Please can you tell me if searching between dates is possible, if not I will have to try something else.
Regards
Julie
I don’t know what’s going wrong here. For comparison I set up my own example with your dates that seems to work. I wonder if strtotime doesn’t like the dd-mm-yyy format.
Hi Michael,
I changed the date format to yy-mm-dd but still can’t get it work. Would you be able to post your php code and your shortcode so that I can compare? Yours works a charm!
I’m also beginning to wonder if the shortcode I generated start_end_wrapper is accessing the .php? I did follow all the instructions given in your documentation about using the Shortcode Exec php plugin and creating my own shortcode.
Thanks once again for your time.
