Trying to autopopulate form using mysql connection

  • bltrecruiting
    Asked on December 18, 2014 at 6:27 AM

    Hello

    First of all many thanks for all the tutorials on here, they greatly helped me in writing my code. 

    I have data from my form connected to MYSQL, that all works fine, but what I am trying to do is pull it back the other way.......

     

    I have inserted into my form webpage http://www.recruitingblt.comli.com/add-new-sales-record.html a box which searches for client name, if found then it should auto-populate the fields.

     

    It looks as if it is finding the record for example if you try James Dean, as he is in my mysql database as a client, then it brings you back to the form page but just does not autofill.

     

    I therefore think this could be something simple, I have missed something somewhere, can anyone help?

     

    Thanks 

     

    Here are both codes I am using for this query

     

    http://pastie.org/9787954

     

     

  • bltrecruiting
    Replied on December 18, 2014 at 9:29 AM

    Can anyone please help me? Please? 

  • Ben
    Replied on December 18, 2014 at 10:54 AM

    Hi,

    I would suggest taking a look at the following 3 guides since they will show you how to pre-populate the fields:

    How to use lookup query via database and pre-populate the form fields?

    Prepopulating fields to your JotForm via URL parameters

    How to find Field IDs & Names

    By looking at your code, I see that you did not match the field names on your jotform which is why they do not get filled out.

    Do let us know however if you have any further questions and we would be happy to assist you with them.

    Best Regards,
    Ben

  • bltrecruiting
    Replied on December 18, 2014 at 12:26 PM

    They do match the field names from the array (not the field text, but the actual field identifiers in jotform), I have the code working now however the array just prints to the page with the form below it, i.e. the query has been processed, the data fetched from MYSQL but it would seem jotform does not know what to do with the array. 

    Is this definitely possible? Has anyone at jotform actually tried it? I can see the examples with coding etc but there is no proof of it actually working. I have adapted my code to find the first row that contains the client_name as there will be many rows for that client, thus I just want the first one in order to pull through address details etc. Simple query that is possible with an HTML form. 

    The search box finds a match and returns the array, but it would seem the Java part with jotform link does not know what to do with the array. I am just trying to pass one MYSQL field value Attn_Email1 to test. I will add the other fields when I get this working (if it is possible). 

    Here is my database.php code;

    http://pastie.org/9788518

    Here is the result;

     NB: Attn_Email1 is a field below the address lines and is empty 

    Trying to autopopulate form using mysql connection Image 1 Screenshot 20

  • bltrecruiting
    Replied on December 18, 2014 at 1:08 PM

    Okay I got this working!!! 

     

    But now it only populates the first box Client Name

     

    Is there anything wrong with the following;

     

      // Get the fields from the database

            $name= $row['Attn_Name1'];

            //add your additional fields here

            echo '<script type="text/javascript" src="//form.jotform.com/jsform/43438603456356?name7='.$regnum.'&name='.$name.'"></script>';

        }

     

    name 7 is the URL name of my Name field 

    name is the URL name of my Attn_Name1 field taken from form source 

     

    Is it because they are names similar? 

  • Ben
    Replied on December 18, 2014 at 2:23 PM

    Hi,

    It is great to hear that it is coming to place, now lets tackle the name and email fields.

    echo '<script type="text/javascript" src="//form.jotform.com/jsform/43438603456356?name7='.$regnum.'&name='.$name.'"></script>';

    OK, so first my suggestion would be to name the fields in a way that would be easy to follow so that you can make future edits to it much faster.

    The "name7" is the correct field for the client name, but the invoice attention of name has the "name" as its value and this seems to not work nicely with the first name, which I will make further tests of and report to our developers.

    Usually the last number should not cause the issue if there is a field without a number, but as it is currently, i would recommend changing the fields to clientName and invoiceName1 and invoiceName2.

    Since there also seem to be issues with the javascript conflict on your pages, I would also suggest using iframe embed method, which would require you to change the code just slightly to:

    <iframe id="43438603456356" frameborder="0" scrolling="no" style="width: 100%; border: medium none; height: 1118px;" name="43438603456356" allowtransparency="true" src="//www.jotformeu.com/form/43438603456356?<?php echo $parameters; >?"></iframe>

    You can prepare the $parameters variable upfront like so:

    $parameters = 'Firstfieldname=' . $firsfieldNameValue;
    $parameters .= '&secondfield=' . $secondFieldValue;
    $parameters .= '&thirdfield=' . $thirdFieldValue;

    and so on for all the fields that you want to pre-fill.

    In case you need it, this guide will show you how to find the names of each fields: How to find Field IDs & Names. Clicking on the field will also allow you to edit it.

    Best Regards,
    Ben

     

  • bltrecruiting
    Replied on December 18, 2014 at 3:44 PM

    **it sends the address 5 fields as a string rather into one column in MYSQL

  • bltrecruiting
    Replied on December 18, 2014 at 4:03 PM

    I take it back, it was still only populating with the top row of info

     

    So i moved to your second point and embedded an I frame, but I get nothing, the qeury runs but no data pulled through, if my field name on form is [invoiceName1] and in MYSQL the column is Attn_Name1 is the following $attnname parameter correct? it doesn't make sense to me looks wrong 

    $attnname= 'invoiceName1='.$Attn_Name1;

            

            //add your additional fields here

            echo '<iframe id="43438603456356" frameborder="0" scrolling="no" style="width: 100%; border: medium none; height: 1118px;" name="43438603456356" allowtransparency="true" src="//www.jotformeu.com/form/43438603456356?<?php echo $attnname; >?"></iframe>';

  • bltrecruiting
    Replied on December 18, 2014 at 4:05 PM

    I also tried this; 

     

    $attnname= 'Attn_Name1='.$invoiceName1;

     

  • Ben
    Replied on December 18, 2014 at 4:19 PM

    Hi,

    Hello, thank you so much, the first fix worked, so changing the name fields, I did as you said and it worked. If I could hug you I would!!!

    You are welcome :) I am glad that it is working for you and going the way you have planned it :)

    My address however is causing the last issue, it is a string of 5 variables, but in My SQL I have coded the form submission thankyou.php to send it as an array, how can I tell the search field to pull the field back through as a string but then separate into each address field accordingly so address[0] addresss [1] etc

    Just to be sure, do you have the data saved as array? If I understood you correctly you are saving it as string, but would need to show it up as a string one at a time for each field that is populated.

    Now if I was to save the data coming in as array of fields, I would save it as json encoded array. Such array is then easy to output by json decode function and simply added to the fields.

    Another good alternative could be to serialize the input and output.

    I have given the links to all 4 needed functions and as you will see they are easy to add to your existing code, while also help you preserve the data and the data type.

    Now in regards to your new responses:

    $attnname= 'invoiceName1='.$Attn_Name1;

    echo '<iframe id="43438603456356" frameborder="0" scrolling="no" style="width: 100%; border: medium none; height: 1118px;" name="43438603456356" allowtransparency="true" src="//www.jotformeu.com/form/43438603456356?<?php echo $attnname; >?"></iframe>';

    This should work as long as the $Attn_Name1 has a value.

    This is what I would suggest.

    Add all fields that you would like to have and add them +1 value, for example like so:

    $params = 'clientName=1&address[addr_line1]=2&address[addr_line2]=3&address[city]=4&address[state]=5&address[postal]=6&invoiceName1=7&email15=8&name216=9&email2=10';

    echo '<iframe id="43438603456356" frameborder="0" scrolling="no" style="width: 100%; border: medium none; height: 1118px;" name="43438603456356" allowtransparency="true" src="//www.jotformeu.com/form/43438603456356?<?php echo $params; >?"></iframe>';

    If you test it over an URL http://www.jotformeu.com/form/43438603456356?clientName=1&address[addr_line1]=2&address[addr_line2]=3&address[city]=4&address[state]=5&address[postal]=6&invoiceName1=7&email15=8&name216=9&email2=10

    You can see these parameters work. Now just replace the number with a field that you get from DB and if it is not working, you will know that the issue is with the field retrieval and we can focus more on that :)

    Do let us know how it goes.

    Best Regards,
    Ben

  • Ben
    Replied on December 18, 2014 at 4:23 PM

    Oh and one more thing:

    Again thank you so much and can I just say due to the support I have received on this I am going to be upgrading my account, I was unsure but you guys have been so helpful and quick to respond too!!

    Thank you for the very nice words it is much appreciated. :)

    We are looking forward to hear from you how it goes and we are here to assist if needed.

    Best Regards,
    Ben

  • Kate
    Replied on December 18, 2014 at 9:48 PM

    My account has been disabled they think I was phishing!

     

    Anyway I would just like to say that although everything is working, there is now a conflict, if the user selects no as in they have not previously entered client details before, the two fields that I am pre-populating using database.php (Attn_Name1 & Client_Name) in MYSQL are now empty.

     

    So the PHP script is  running whether they select yes or no, so the solution doesnt work. I have changed to Java for the search box so the .php doesnt run automatically only onsubmit, but no luck, can't get it to work at all. 

     

    Can anyone help? 

  • Ashwin JotForm Support
    Replied on December 19, 2014 at 5:54 AM

    Hello Kate,

    On your issue related to account disabled:  I have already responded to you on the following thread and also activated your account:  http://www.jotform.com/answers/478779-Account-suspended-#1 

    On your issue related to PHP script:  Your message has reached my colleague @Ben and I have also send a message to him. He will get back to you as soon as he is back online.

    Thank you!

  • Ben
    Replied on December 19, 2014 at 9:30 AM

    Hi Kate,

    So the PHP script is  running whether they select yes or no, so the solution doesnt work.

    OK, so you would like to check if the PHP should search and output anything or not.

    To do that we should just take a look at the very top if the name field is set or not.

    I have modified your code from pastie.org and you can find it here: http://pastebin.com/35QWn82D

    Please note that we do the check on line 31. Since your form seems to be sending data over $_GET we are looking for it in that way, but if you ever change it to POST just replace the $_GET with $_POST and it will continue to work.

    Based on the test above, we do next. If the name is set, we go through the code you already had that checks the data in the DB and outputs it to the form - 76.

    If not set, we will just display the jotform as it is (without any parameters) - line 82.

    Please test it and let us know how it goes, but do note that I had used the code from pastie.org and I believe that the code there is now not most up to date so you might need to make some additional modifications.

    Best Regards,
    Ben