Creating a form to search the database

August 2nd, 2014 Leave a comment Go to comments

What to create a form to that can be used to search your form entries? Follow this example:

 

1
2
3
4
5
<form action="" method="POST">
First Name: <input type="text" name="fname" /> 
Last Name: <input type="text" name="lname" /> 
<input type="submit" />
</form>
1
[cfdb-datatable form="Contact Form" filter="fname~~/.*$_POST(fname).*/i&&lname~~/.*$_POST(lname).*/i"]

In the form, put whatever fields you wish to search on. You can name these form fields by any name.

Use a short code with the filter attribute. Here we use filter variable substitution. $_POST(fname) and $_POST(lname) will be substituted with the values of the form post.

For the filter in the short code, string together regular expressions of the form database_form_field_name~~/.*$_POST(posted_field_name).*/i. The “.*” at the front and end of the regex allow the user to just type in part of a word and find a match. The “i” makes it case-insensitive.

Chain all the regex’s together with logical AND (&&). If a user leaves an entry blank in the search form like fname in the example above then fname~~/.*$_POST(fname).*/i will become fname~~/.*.*/i after substituting an empty string. This regex will match all entries. The effect is as if this contraint did not exist. So this means that a user can fill in only those elements on the search form that he wants to constrain. Those that are not filled out are effectively unconstrained.

When you first load a page with the above form and short code, it displays all the values. If you want the short code to show no results when the page is loaded, added a hidden form field to the form and short code. In this case there is a hidden INPUT named “x” in the form and we add to the short code filter “&&submit_time>$_POST(x)”

1
2
3
4
5
6
<form action="" method="POST">
First Name: <input type="text" name="fname" /> 
Last Name: <input type="text" name="lname" /> 
<input type="hidden" name="x" value="1"/>
<input type="submit" />
</form>
1
[cfdb-datatable form="Contact Form" filter="fname~~/.*$_POST(fname).*/i&&lname~~/.*$_POST(lname).*/i&&intval(1)=$_POST(x)"]
  1. April 6th, 2012 at 12:34 | #1

    Hi Michael

    I have four forms on my site.
    – Two in the public area for people looking for care homes: 1. An immediate vacancy 2. A future vacancy.
    – Two in the private area: 1. Care home information 2. Listing a vacancy.

    When a member of the public provides their details, I want to search the care home information database.

    When a vacancy is listed, I want to search the public databases.

    I’ve tried to set up your code above, without success. Where am I going wrong – or is there a better way to achieve this?

    Thanks.

    Brian

  2. Bill
    April 24th, 2012 at 22:20 | #2

    I’m stumped also. Help!

    • Michael Simpson
      April 25th, 2012 at 06:50 | #3

      I got this worked out with Brian off-line. His issue was a line break in the middle of the short code causing it not to work.

  3. Darrin
    July 21st, 2012 at 14:14 | #4

    The search works great!

    From the search, I would only like to show some of the columns in the table. I tried this code however it still shows all columns.

    [cfdb-datatable form="Company Contact List" filter="Company~~/.*$_POST(Company).*/i&&ContactName~~/.*$_POST(ContactName).*/i" show="Company,ContactName,Email,Phone,Address,City,State,Zip,ServicesLocation" orderby="Company"]

    Any thoughts?

    Thanks

    • Michael Simpson
      July 21st, 2012 at 17:01 | #5

      Make sure there is no line break inside the short code in your actual post. Specifically make sure there is no line break right before the “show”. Otherwise WP might not recognize “show” and the rest of the stuff after the line break as part of the short code.

  4. Jeff
    August 15th, 2012 at 16:18 | #6

    I have a field where users enter a date (not the current date). I would like to automatically show all entries whose entered date matches today’s date but I cannot get a call up of the current date to work in the filter. Is this easy to do and I’m just stupid?

    • Michael Simpson
      August 16th, 2012 at 18:35 | #7

      Other than the submit time, everything else is treated as a string. So you can’t do date comparisons on other fields.

  5. Jeff
    August 15th, 2012 at 16:19 | #8

    I have a field where users enter a date (not today’s date). I would like to automatically show all entries whose entered date matches today’s date but I cannot get a call up of the current date to work in the filter. Is this easy to do and I’m just stupid?

  6. Maulik
    August 20th, 2012 at 10:25 | #9

    Is this only works for [cfdb-datatable] ??? Can we have Search Form for [cfdb-html] too ???

    • Michael Simpson
      August 20th, 2012 at 13:02 | #10

      It will work for any short code. The important part is what is in the “filter” part and that applies to all short codes.

  7. Maulik
    August 20th, 2012 at 23:50 | #11

    @Michael Simpson

    Super Cool Man… Thanks….

    I have also posted few question points on http://cfdbplugin.com/?page_id=284 – from Maulik

  8. September 17th, 2012 at 12:06 | #12

    I am trying to get this to work on my class website. I have my students submit work through Contact Form and then would like to be able to search the database by simply putting in either the Student Name or the Assignment Name to grade all the work at once. Below is the code the modifications I made to the referenced code from this page but it doesn’t work.

    ——————————————————————————-

    Student Name:
    Assignment:

    [cfdb-datatable form="Submit: 2012-13 Per 1"
    filter="your-name~~/.*$_POST(sname).*/i&&assignment-name~~/.*$_POST(work).*/i" show="Submitted,your-name,assignment-name,assignment-attch"]
    ——————————————————————————-

    Here is the page I am refering to that doesn’t work. Please let me know how to fix this, it would REALLY HELP ME with grading my students work efficiently.
    http://ganttech.com/studentwork/search/
    What happens is the page simply refreshes without showing anything. Not sure what to do. PLEASE HELP!

  9. September 17th, 2012 at 12:07 | #13

    I am trying to get this to work on my class website. I have my students submit work through Contact Form and then would like to be able to search the database by simply putting in either the Student Name or the Assignment Name to grade all the work at once. Below is the code the modifications I made to the referenced code from this page but it doesn’t work.

    ——————————————————————————-

    Student Name:
    Assignment:

    [cfdb-datatable form="Submit: 2012-13 Per 7"
    filter="your-name~~/.*$_POST(sname).*/i&&assignment-name~~/.*$_POST(work).*/i" show="Submitted,your-name,assignment-name,assignment-attch"]
    ——————————————————————————-
    Here is the page I am referring to that doesn’t work. Please let me know how to fix this, it would REALLY HELP ME with grading my students work efficiently.
    http://ganttech.com/studentwork/search/
    What happens is the page simply refreshes without showing anything. Not sure what to do. PLEASE HELP!

    • Michael Simpson
      September 17th, 2012 at 12:36 | #14

      I don’t see any obvious problem.

      1. Try it without the “filter” part in it; verify that a result table appears
      2. Put in filter=”your-name~~/.*$_POST(sname).*/i” and verify that works
      3. Put in filter=”assignment-name~~/.*$_POST(work).*/i” and verify that works
      4. Then but in both with an && or a ||

      Look at your short code in the post in WP with the editor set to “HTML” mode (not “visual”). Replace any “& amp ;” character codes with actual &

  10. September 17th, 2012 at 12:57 | #15

    @Michael Simpson
    I didn’t even think about breaking it apart to verify each part. I did that, and for some reason it’s now working. Thanks, this will really help with my grading this year.

  11. Alipio
    October 19th, 2012 at 01:07 | #16

    How can i add search box that can search all attribute

    • Michael Simpson
      October 20th, 2012 at 12:58 | #17

      Not currently supported

  12. Alipio
    October 19th, 2012 at 01:11 | #18

    @Alipio
    its like the search box in the [cfdb-datatable]

  13. March 28th, 2013 at 10:52 | #19

    Hi – I need to pre-fill forms based on user entering their email address.
    Is there an existing template I might modify?
    Thanks in advance!
    ER

    • Michael Simpson
      March 28th, 2013 at 11:09 | #20

      Unfortunately no.

  14. Ernest
    April 12th, 2013 at 04:49 | #21

    How do I filter a submit_time in this example ?

    [cfdb-table form="contact-form" filter="submit_time>/.*$_POST(sometime).*/i"]

    This is not working.

  15. Ernest
    April 15th, 2013 at 08:41 | #22

    @Ernest
    Had to learn about regex here,because time filter will not work with it ,but this will work instead
    [cfdb-table form="contact-form" filter="submit_time>$_POST(sometime)"]
    Thanks for this usefull plugin Michael !

  16. giovane_mar
    September 12th, 2013 at 14:00 | #23

    Hi Michael.
    First, congratulations for CFDB plugin, it’s a great plugin, very usefull.

    My need is the same above, but i need show the distinct values.

    Example: Many people submit a form. I create a page for filter this submissions, using shortcode cfdb-html to show the name of those people into combo, like your post above. But, one person can submit more than one form, and I need show the name of people into combo without repeat. Is possible?

    Using mysql_query, I know it’s possible (select distinct column-name).

    And using shortcode? If not, you could add this feature for plugin, some like [cfdb-html distinct=field-name].

    All the best. Thanks again for support.

  17. Michael Simpson
    September 15th, 2013 at 18:09 | #24
  18. mariechild
    April 25th, 2014 at 21:07 | #25

    Hello,

    I need your expertise on a kind of difficult taks for me, but I’m sure its wouldnt be not at all hard for you.

    I just installed the plugin for the purpose of creating a form that will have a required field and the required field needs to query a database. If the enduser does not enter in the correct information according to what I have in the database, then the validation should fail and the page should not redirect to another page.

    Bascially, I have a form where I would for my clients to enter in their building name of where they work. I have created a database in myPHPadmin with a list of building names. If the enduser does not provide the correct name of a building, then the validation is suppose to fail. Otherwise, it should redirect them to another page.

    I actually researched and found shortcode to redirect the page using contact form 7..but i need help in querying a databse to pass validation on a required field.

    Any suggestions? Or is it possible to create a customized shortcode for this?

    Thanks in advance for any help provided.

  19. mariechild
    April 26th, 2014 at 14:06 | #27

    Hi Michael, thanks for the quick response. You are a very knowledgeable person.

    I used your recommended code to validate a required field on my form. Can you take a look at it? For some reason, it is still allowing the enduser to pass validation by entering in a bogus building name, but that field should check the database to make sure a legitimate building name ihas been entered.

    I want one field on my form to pass validation. To ensure our clients are using this particualr form only and not just the general public, we want the enduser to enter their building name that corresponds to a building name that I have setup in a database myPHPadmin. If they do not enter in a correct building name, then they should get an error message, sayiing ” please enter your building name”. Otherwise, the validation should pass to the next page if the correct building name was entered.

    I dont know if I am doing this right, but here is the information and the code that I modified just a teeny bit.

    The name of my form is “Show Me Deals Copy”
    The form text field name is “Building”
    In phpmyadmin , the Database is “Deals” and the table is “Building”

    function is_already_submitted($formName, $fieldName, $fieldValue){
    require_once(ABSPATH . ‘wp-content/plugins/contact-form-7-to-database-extension/CFDBFormIterator.php’);
    $exp = new CFDBFormIterator();
    $atts = array();
    $atts['show'] = $fieldName;
    $atts['filter'] = “$fieldName=$fieldValue”;
    $exp->export($formName, $atts);
    $found = false;
    while ($row = $exp->nextRow()) {
    $found = true;
    }
    return $found;
    }

    function my_validate_email($result, $tag) {
    $formName = ‘show_me_deals_copy'; // Name of the form containing this field
    $fieldName = ‘building'; // Set to your form’s unique field name
    $name = $tag['name'];
    if($name == $fieldName){
    $valueToValidate = $_POST[$name];
    if (is_already_submitted($formName, $fieldName, $valueToValidate)) {
    $result['invalid'] = false;
    $result['reason'][$name] = ‘Please enter your building name'; // error message
    }
    }
    return $result;
    }

    add_filter(‘wpcf7_validate_text*’, ‘my_validate_email’, 10, 2);

  20. Michael Simpson
    April 27th, 2014 at 18:42 | #28

    @mariechild This comment refers to the code on page: http://cfdbplugin.com/?page_id=904

    Possible issue: is your ‘building’ field required in your CF7 for definition? (i.e. does it have an asterisk?). If not add it. See http://www.topsemtips.com/2012/02/simple-wordpress-contact-form-7-phone-validation/

    In other words, ‘wpcf7_validate_text*’ looks for a required field in your form definition, ‘wpcf7_validate_text’ (no asterisk) looks for field that is not required.

  21. mariechild
    April 28th, 2014 at 10:45 | #29

    Yes. My building field is required on my CF7, however, it still allows an enduser to put a bogus building name in that field. Any reason why it is not searching the database to query the building name …or any other suggestions to make that field sync or query the database I created in PHPHmyadmin for correct building names before passing validation?

    here is the code that I am using:

    function is_already_submitted($formName, $fieldName, $fieldValue){
    require_once(ABSPATH . ‘wp-content/plugins/contact-form-7-to-database-extension/CFDBFormIterator.php’);
    $exp = new CFDBFormIterator();
    $atts = array();
    $atts['show'] = $fieldName;
    $atts['filter'] = “$fieldName=$fieldValue”;
    $exp->export($formName, $atts);
    $found = false;
    while ($row = $exp->nextRow()) {
    $found = true;
    }
    return $found;
    }
    function my_validate_email($result, $tag) {
    $formName = ‘show_me_deals_copy’; // Name of the form containing this field
    $fieldName = ‘building’; // Set to your form’s unique field name
    $name = $tag['name'];
    if($name == $fieldName){
    $valueToValidate = $_POST[$name];
    if (is_already_submitted($formName, $fieldName, $valueToValidate)) {
    $result['invalid'] = false;
    $result['reason'][$name] = ‘Please enter your building name’; // error message
    }
    }
    return $result;
    }
    add_filter(‘wpcf7_validate_text*’, ‘my_validate_email’, 10, 2);

  22. Michael Simpson
    April 28th, 2014 at 12:27 | #30

    @mariechild
    I suggest to you to add debug statements in the code to see what is getting executed. See the “debug” section of this page: http://cfdbplugin.com/?page_id=747

    I would try:
    – Put a error_log statement at the beginning of my_validate_email to ensure that it is getting called.
    – Do the same in is_already_submitted
    – error_log the value of “$fieldName=$fieldValue” to see if that is coming out right
    – error_log $found right before “return $found”

  1. No trackbacks yet.
You must be logged in to post a comment.