- patricciaAsked on January 13, 2012 at 03:05 PM
I have a database with all information that my jotform will be asking the user to fill out in the form. How do I get jotform to access that database when the user type in registration number, so that jotbase will automatically prepopulate the rest of the information.
This is a re-post of a comment on Prepopulating Fields to Your JotForm via URL Parameters
- fxrAnswered on January 13, 2012 at 04:42 PM
Is the form hosted on a membership site?
It may be possible to create a URL that prepopulates those feilds with some php code, but without indepth knowledge of how your database/site is setup its hard for us to offer very specific advice.
For it to be in any way a viable option, your sites visitors would have to 'log in' at some point before getting access to the form. Does that happen?
- patricciaAnswered on January 15, 2012 at 07:45 AM
No, the form is not hosted on a membership site.
- fxrAnswered on January 15, 2012 at 03:30 PM
To acheive the functionality you are looking for , it would involve creating a script that asks for a customers registration number, then generating URL for the form that would the prepopulate the fields required.
It, technically shouldnt be that hard to do. We may be able to help you but we would need to find out more about your database, e.g what type of database is it? and what are the field names in it?
- patricciaAnswered on January 16, 2012 at 07:27 PM
It is a excel database, I also uploaded the database to MySgl.
The field names are:
REG, KENNELCLUB, DOG, BREED, VARIETY, SEX, FIRST, LAST, OWNER2, ADDRESS, CITY, STATE, ZIP, PHONE, COUNTRY, BREEDER, SIRE, DAM, BDATE, PLACEBORN, EMAIL, HEIGHT, CALLNAME, MODIFIED
- fxrAnswered on January 16, 2012 at 07:38 PM
OK, I will see if I can come up with some code for you at some point tomorrow.
The database needs to be MySql, and hosted on a php enabled web server.
- JoelAnswered on January 17, 2012 at 09:11 AM
I would like to see the solution for this matter too. :)
- fxrAnswered on January 17, 2012 at 06:45 PM
OK heres the solution:
You need to add the following code to a section on your webpage:
<FORM NAME = "reg" action="database.php">
<div style="width:150px; float:left; display:inline-block; font-family: Verdana;
font-size: 12px; padding-left:13px;"> Registration Number: </div>
<input type="text" id="textbox" name="regnum" size="20"/>
<input name="Submit" type="submit" value="Get Form"/>
This code just sets up an input field which takes a 'registration number' , upon submittal it accesses a file database.php (in the same directory)
The contents of database.php is here
You need to make a few edits to this code: adding your database name, username, password and table name, also the script embed code needs to change and any additional variables added.
To show this working, here is my simple example database:
Here is the webpage which accesses this database and prepopulates the respective fields. -> here
e.g enter 1, 2 or 3 where you are prompted for a registration number.
This shows what needs to be done to access a MySql database to prepopulate form fields. (There is still tidying up to be done in terms of the aesthetics of the functionality. )
Depending on how much coding experience you have, I realise this may a bit of leap, but it really isnt that complicated technically. I am more than happy to field any questions you may have should you decide to pursue this type of functionality.
- patricciaAnswered on January 17, 2012 at 11:51 PM
Thank you for helping me. I understand what has to be done, but I am having issues getting the webpage to display. I must be using the wrong code or not doing something correctly.
I changed database name, username, password and table name. I also used my embed code for my jotform. But I kept getting
This program cannot display the webpage Most likely causes: You are not connected to the Internet. The website is encountering problems. There might be a typing error in the address.
- fxrAnswered on January 18, 2012 at 01:40 AM
It probably going to take some patience on both our parts to work through this.
Are both the page with the input field for the registration number on it and the database.php uploaded onto your webserver?
Where do you get that error mesage exactly? is that on the webpage with the code to show the input for 'registration number' or after you press the 'get form' button?
Can you link me to a URL/webpage where you have the 'registration number' input field added ?
- patricciaAnswered on January 18, 2012 at 07:15 PM
Okay, now I am getting this error: No input file specified, I get this error after I press the get form button.
here is the link to my webpage. http://www.patriciashawsss.com/page17.html
- fxrAnswered on January 18, 2012 at 07:37 PM
OK, I see what you have done.
You need to create a seperate (empty) file on your webserver (in the same directory as your 'page17.html' file ) called database.php and put the code here into it.
You can remove the highlighted code below from your existing 'page17.html' webpage.
- patricciaAnswered on January 19, 2012 at 09:58 PM
Thank you for helping me. It solve my problem. But it create another.
1- I had a online entry fee that was "disabled" so that the user would have to pay for an fee. Now I am unable to put that code back in: checked="checked" disabled="disabled"
2 - I need to recreate a link directly to my jotform, for those user whom registration number is not in my database.
How can I accomplish this.
- fxrAnswered on January 20, 2012 at 04:58 AM
1. What was the original form you used showing that ? Do you want to continue using that form? Is it the form you want to autopopulate?
2. Ok, I will work on a tidier solution for that.
- patricciaAnswered on January 20, 2012 at 11:15 AM
I resolve the issue #1. I'm using the hide condition.
I still unable to create a link directly to my jotform, for those user whom information is not in my msyql.
- fxrAnswered on January 20, 2012 at 11:35 AM
I have been doing a little work on issue #2. Nearly there.
Are you have enough having the form open in a seperate page? Getting it to display on your original page, even though certainly doable, does add an extra layer of complication.
- patricciaAnswered on January 20, 2012 at 12:17 PM
Yes, it can open on a separate page.
- fxrAnswered on January 20, 2012 at 12:39 PM
OK, I have decided the easiest way to do this is just have your unprepopulated form show on the 'original' page. I dont know the dimensions of the form you are using so Im not sure if this approach is ideal.
NOTE: You dont have to embed your 'normal' form here, you can use a link to the form instead of the JotForm embed code.
You need to change the code I gave you for your 'page17.html' page to this:
You can see how this work on a clone I made of your webpage -> here
Hopefully it is easy enough for you to carry out those edits to your form.
I believe we are almost there.
- patricciaAnswered on January 20, 2012 at 07:58 PM
- patricciaAnswered on January 22, 2012 at 10:47 AM
Thank for the help, it was working, but now it is not. Another issue has cropped up regarding this issue.
When I click on the yes button, nothing is happeining. The field for me to put the number in, is not coming up. I have to click in the field to bring the form up and sometimes it do not come up. If it does come up and I go back, the form will not come up again.
- fxrAnswered on January 22, 2012 at 11:12 AM
It appears you have worked out what was happening here?
May I also suggest setting the width of your form (on JotForm) to 620 or 630, just so you dont have that clipping to the right.
- patricciaAnswered on January 22, 2012 at 04:19 PM
It still not working. It is not loading the form on my computer. Is it loading for you on your computer/ The page that I am working on is page is
- fxrAnswered on January 22, 2012 at 04:31 PM
Your embed code is missing a 'js'
Change it to this:
- fxrAnswered on January 22, 2012 at 04:53 PM
I think your radio buttons are better using the onchange event rather than onmouseup
- patricciaAnswered on January 22, 2012 at 05:23 PM
yeah, I already changed it back.
Everything is working great now.
I am trying to tidy up mysql now.
My other issues with jotform, is that I have to make a choice of sending my customer back to my website to make another online entry after they submit their form or have the information that is on the form updated to mysql. Right now, I have them redirected back to my website. Is there a way that I could have both.
- fxrAnswered on January 22, 2012 at 05:43 PM
I am not quite sure of exactly what mean.
do you want the prepopulated form appearing on your site instead of opening in a new browser tab/window?
- patricciaAnswered on January 23, 2012 at 10:53 AM
Sorry, what I meant is that I want the user information to also upload to my database,myysgl. But I also want the user to also be directed back to my website so they can make another purchase.
Right now, I have them directed back to my website.
- fxrAnswered on January 23, 2012 at 11:02 AM
Please review this user guide article on how to populate a mysql database with a form submission with a php 'thankyou' page.
Is that what you are looking to do? that guide is very thorough, let us know if you need any help with it.
- patricciaAnswered on February 11, 2012 at 11:07 AM
I have a new issue that I have been trying to resolve.
In mysql database I use the symbol "&". Example: Mr. & Mrs James Doe.
My jotform is not reading the "&" and will do not populate the field with the complete name. If only put in Mr.
If I change the "&" to "and" the complete name will populate in the field. "Mr and Mrs James Doe"
How can I resolved this issue
- JotForm SupportliyamAnswered on February 11, 2012 at 01:47 PM
The reason for this is because in URL parameters, the ampersand (&) actually is a separator. If you wish to add the ampersand as a string value, you will need to have it translated via URL encode (%26).
If you're accessing the data from your database and would want it to be posted as a URL parameter, you might want to use something like PHP's str_replace function to translate the ampersand characters.
$datafromdbvalue = "Harold & Harry";
// Returns: Harold %26 Harry
$convertedvalue = str_replace("&", "%26", "$datafromdbvalue");
echo "<a target="_blank" href="https://www.google.com/search?q=$convertedvalue" rel="nofollow noopener" rel="nofollow">test URL params</a>";
- patricciaAnswered on February 11, 2012 at 06:33 PM
I want the data to posted to my jotform field.
You lost me. I understand this part
$datafromdbvalue = "Harold & Harry";
// Returns: Harold %26 Harry
$convertedvalue = str_replace("&", "%26", "$datafromdbvalue");
but I don't understand this part echo "<a target="_blank" href="https://www.google.com/search?q=$convertedvalue" rel="nofollow noopener" rel="nofollow" rel="nofollow">test URL params</a>";
I already have my data field echo so how do I incoprated this.
Sorry for not getting it.
- JotForm SupportliyamAnswered on February 11, 2012 at 10:15 PM
The echo that I have in my code is just an example. If you are to use that it will just send you to google with the variable in the search box as well as the URL parameter.
To make it work on your own code, just change your variables (or insert the str_replace function codes) and then just leave the echo alone and it will work fine.
$kennel = str_replace("&", "%26", $row['KENNELCLUB']);
$dog = str_replace("&", "%26", $row['DOG']);
$breed = str_replace("&", "%26", $row['BREED']);
This way, you won't have to touch anything that calls the $kennel, $dog, and $breed variables.
Sorry for the confusion.
- patricciaAnswered on February 12, 2012 at 01:01 AM
Thank you, it works
- patricciaAnswered on March 17, 2012 at 06:43 PM
Hello, I had a issue back in January regarding upload data to my database. You instructed me to: Please review this user guide article on how to populate a mysql database with a form submission with a php 'thankyou' page.
I am just now trying it out and the data is not posting to my mysql database.
- JotForm SupportliyamAnswered on March 18, 2012 at 01:02 AM
Hello patriccia. Sorry if I'm going to ask this, can you share the form ID which is having this issue? Or is this a matter of having the query inserting to your database?
May I ask as well to post the details on a new thread so that we can make a fresh start of this case? You may set it to private as well if some details have sensitive information in it.
- jacknabAnswered on October 11, 2012 at 11:15 PM
Hello I am wanting to make my form to work like this thread, however i do not know anything about developing, could I pay jotform support to develope me this solution?
I tried, but what I dont understand is if you must create a new database table with fields that match the jotform field names, or if you could use your existing database fields.
- jeanettebmzAnswered on October 11, 2012 at 11:47 PM
Can you please open a new thread and provide us with more details . Hopefully somebody in the Support Staff will be able to assist you
- jeanettebmzAnswered on July 03, 2013 at 09:57 PM
We are very proud to announce that our team of developers have released Jotform Webhooks, this feature would help you to transfer forms data to your own database using your own script codes
Please find out more about it here
- digiosolutionsAnswered on May 15, 2014 at 02:59 PMI was wondering, is something like what was asked for here possible via a Google spreadsheet on Google drive? Jotform send that order # and then prepopulate a few fields for a payment form enabling the user not to have to manually invoice orders that require manual verification and they do not want to process payments on immediately, and instead, send the new jotform url, have the user enter their order number, and have the form prepopulate their calculation total field and unique ID dummy text field and transfer it to paypal widget then onto check out?
- patricciaAnswered on May 19, 2014 at 10:41 AMNew response receivedI think you have the wrong person, I did not email you.
- NRFEMSAnswered on January 11, 2015 at 01:14 AM
Seems like a great solution, but I use Wix as a web editor, and cant use java scripts or create the database.php file. Any work around for that?
Seems to be a lot of requests for pre-population using mySQL, from years ago why hasn't there been a integration code?
Thanks in advance for any advice
- frontrowjdhAnswered on October 07, 2015 at 12:36 AM
So here is a question somehow related to the conversation above.
I have a form in which I would like the reponder to type in NCES School ID: which is the look up key for retrieving name of school, address etc. The database is a public website
Is it possible to have the form look up the information above and then populate it back to the form?
Thanks for a great support forum
- JotForm SupportChriistianAnswered on October 07, 2015 at 06:09 AM
I have moved your concern to a separate thread so we can better assist you. You can check the thread here: http://www.jotform.com/answers/677758. We will attend to your concern on that thread accordingly.
If you have any further question, please feel free to contact us again by creating a new thread.