Welcome to the LimeSurvey Community Forum

Ask the community, share ideas, and connect with other LimeSurvey users!

Reading from a Database

  • FewPexia
  • FewPexia's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
10 years 9 months ago #97389 by FewPexia
Reading from a Database was created by FewPexia
Just discovered LimeSurvey and it looks very useful!
We have been working on a long time enterprise application that includes some surveys and related items along with many other functions.
We want to be able to develope new surveys and deploy them easily. LimeSurvey seems to be close to what we need.
Wondering about Oracle as the database but that is a secondary issue since we can manage that using the mysql database on the backend if needed. Any information on this would also be useful, though.

We need to be able to look-up information in our database based on user entered information and then display this back in the survey.
Some examples:
- if the user enters a dealer code, we want to be able to look up the dealer name, address, etc. from a database and then include this in that user's survey.
- if the user enters a VIN we want to look up the Division, Model, and Model Year, etc. from a database and then include this in that user's survey.
Also some of this looked up information may drive further question directions. And some information may need to be read only and some editable while some may be hidden.

We are working with large data so having users place all values in the survey itself is not feasible.
I bet this have been done before. Any info on this would be great.

Thanks in advance.
The topic has been locked.
  • tpartner
  • tpartner's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
10 years 9 months ago #97412 by tpartner
Replied by tpartner on topic Reading from a Database
You can make an AJAX call to remote scripts that access your external database(s). The details of the PHP are out of the scope of this forum but if you search the forum for "AJAX", you should find examples of how to handle the returned data (load questions).

Cheers,
Tony Partner

Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.
The topic has been locked.
  • FewPexia
  • FewPexia's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
10 years 9 months ago #97432 by FewPexia
Replied by FewPexia on topic Reading from a Database
Thanks for the advise.
I started experimenting.
I have Javascript calling a php script that just returns text.
I should have no problem making the php scripts I need to access the database and return values.
I am having problems figuring out all the question (and answer) attributes or properties.
Are these documented some where?

Here is my php file:
<?php
echo "HelloWorld!";
?>

Here is the JavaScript I am using for a question which calls this php file.
<p> Test Text</p>
<p>
<script type="text/javascript" charset="utf-8">
$(document).ready(function() {
$.get("upload/templates/helloWorld.php", function(data) {
alert("data loaded: " + data);
$('body').append(data);
$('#answer{SGQ}').val('ABC ' + data);
$('#question{QID}').text("XYZ " + data); // works but then other stuff does not work.
});
});
</script></p>

The code gets executed when the page is loaded.
There is some HTML to write out some text "Test Text" as the question.
Then the php script is called to get "HelloWorld".
The alert work fine.
Writing to the body of the page works find.

I can write thr answer as above if I comment out writing to the question.
Or
I can write the question text above and then the answer seems to be gone.

Are there some attributes/properties that need to be set to write and keep both the question and answer?
Where are things like .append, .val, .text documented?
What other attributes/properties/methods are there for questions or answers?

Thanks for any help or pointers.
The topic has been locked.
  • tpartner
  • tpartner's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
10 years 9 months ago #97433 by tpartner
Replied by tpartner on topic Reading from a Database
Assuming the question that you are trying to load is a short-text and your code is in the source of that question, try:
Code:
$('#question{QID} input[type="text"]').val('XYZ ' + data);

Cheers,
Tony Partner

Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.
The topic has been locked.
  • tpartner
  • tpartner's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
10 years 9 months ago #97434 by tpartner
Replied by tpartner on topic Reading from a Database

Where are things like .append, .val, .text documented?

jQuery - jquery.com/

Cheers,
Tony Partner

Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.
The topic has been locked.
  • FewPexia
  • FewPexia's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
10 years 9 months ago #97435 by FewPexia
Replied by FewPexia on topic Reading from a Database
Thanks for the help but this does not do what I want.
this:
$('#question{QID} input[type="text"]').val('XYZ ' + data);
writes to the answer. (I am trying to write the question.)
This also writes to the answer:
$('#answer{SGQ}').val('ABC ' + data);
I am trying to write the question.
This writes the question but then the answer is no longer displayed (or visible).
$('#question{QID}').text("123 " + data);
The topic has been locked.
  • tpartner
  • tpartner's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
10 years 9 months ago #97436 by tpartner
Replied by tpartner on topic Reading from a Database
Ah, sorry, I assumed you were trying to load a question answer.

The selector required to pipe in the question text would depend on the template used.

What template are you using?

Can you activate a test survey for us to see the source code?

Cheers,
Tony Partner

Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.
The topic has been locked.
  • FewPexia
  • FewPexia's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
10 years 9 months ago #97437 by FewPexia
Replied by FewPexia on topic Reading from a Database
I am using the default template -
I am very new to this
This is all local so I cannot activate anything but if you explain how I can export the survey I can do that if that would help.
In a nut shell, I am trying to learn what it would take to read and write between survey pages and a database.
Since I am referencing the question, I assumed there would be an easy way to write to the question text as well as the answer to the question.
Like I explained above, I seem to be able to write to the question text but then the answer is no longer shown on the page. I guess I am writing to the question text incorrectly.
The question is Short free text and not Mandatory.
Thanks again!
The topic has been locked.
  • tpartner
  • tpartner's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
10 years 9 months ago #97438 by tpartner
Replied by tpartner on topic Reading from a Database

I seem to be able to write to the question text but then the answer is no longer shown

You're not writing to the question text, you're writing to the while question :laugh:

To write to the question text in the default template, try this:
Code:
$('#question{QID} td.questiontext').text('XYZ ' + data);

Cheers,
Tony Partner

Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.
The topic has been locked.
  • FewPexia
  • FewPexia's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
10 years 9 months ago #97439 by FewPexia
Replied by FewPexia on topic Reading from a Database
Thanks much! That did the trick.
Now I see, by looking at the resulting source for the page where the question26 and the questiontext classes are and I am starting to get the hang of it.

I will try and post a simple way of reading from the database and writing questions and answers from the results in the next couple of days. Perhaps it will help someone else out.

Thanks for the quick help.

Also the pointer to jQuery which I will look more into also.
The topic has been locked.
  • FewPexia
  • FewPexia's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
10 years 9 months ago #97475 by FewPexia
Replied by FewPexia on topic Reading from a Database
Built a survey that reads from a database and then displays what is read back to the user.
One question gets a dealer code.
A second question reads the dealer code and then looks it up in a table to get the associated dealer information.
The dealer code is displayed back to the user in a question and the dealer information is displayed as the answer to the question so
the user can make adjustments to it if needed.

This was implemented as follows.
Javascript for the second question reads the dealer code (a previous answer.)
The dealer code is passed to a php script.
The php script then builds a database query and runs it.
This results in dealer information corresponding to the passed in dealer code or an error (dealer code not found) indication.
The question is then adjusted to include the dealer code.
The question answer is then set with the dealer information.

This was implemented using the xampp installation on a PC using mysql.

This uses the following tables created and populated as follows.
CREATE TABLE IF NOT EXISTS `dealer_code_table` (
`dealer_code` varchar(6) NOT NULL,
`dealer_info` varchar(120) DEFAULT NULL,
PRIMARY KEY (`dealer_code`)
) ;

INSERT INTO `dealer_code_table` (`dealer_code`, `dealer_info`) VALUES
('1001', 'Book Dealer'),
('1002', 'Antique Dealer'),
('1003', 'Bond Dealer'),
('1004', 'Appliance Dealer'),
('1005', 'Toy Dealer');

The survey uses the default template.
This uses a first dealer code question set as short free text.
This uses a second question in a second question group (another page.)
The second question is set as long free text.
The javascript for the second question is:
<p>
Adjust the dealer information for the given dealer code if necessary.</p>
<p>
<script type="text/javascript" charset="utf-8">
$(document).ready(function() {
$dealerCode = '{449977X13X27}';
//alert("dealerCode = " + $dealerCode); // For debugging
$('#question{QID} td.questiontext').text('Adjust the dealer information for dealer code ' + $dealerCode + ' if necessary. ');
$.get('upload/templates/getDealerInfo.php?dealerCode='+$dealerCode, function(data) {
//alert("data loaded: " + data); // For debugging
$('#answer{SGQ}').val('Dealer Information: ' + data);
});
});
</script></p>

The php script getDealerInfo.php located in C:\xampp\htdocs\upload\templates is:
<?php
//echo $_REQUEST; // For debugging

$link = mysql_connect('localhost', 'root', '', 'limesurvey'); // connect to database - better if there were persistent connection.

if (!$link) // if connection fails
{
echo "Failed to connect to database!" ; // indicate error condition
die('Could not connect: ' . mysqli_error($link));
}
//echo "connected to database!" ; // For debugging
mysql_select_db('limesurvey', $link);

//$sql = "SELECT surveyls_title FROM lime_surveys_languagesettings";
$sql = "SELECT dealer_info FROM dealer_code_table where dealer_code = '" . $_REQUEST . "'";
// echo $sql; // For debugging

$result = mysql_query($sql);
if (!$result)
{
echo "Dealer Code not found in database!";
//die('nuts ' . mysqli_error($link));
} else {
$row = mysql_fetch_assoc($result); // Assume each dealer code can only have one enter in the database.
if (!$row)
{
echo "Dealer Code not found in database!";
} else {
echo $row["dealer_info"] ; // Return information
}
}
?>

I hope this helps someone!
Please let me know it it does.

Some questions I still have:
Is there a way to make the database connection persistent?
I assume that LimeSurvey keeps a perisistent connection, can custom php scripts make use of it?

To reference a previous question's answer I seem to have had to use the Survey#QuestionGroup#Question#, is there a way to reference this without using the numbers?
Is there some way to get to just the previous question's answer?
It would be nice to have a tree navigation from {SGQ}.

I have just been playing with LimeSurvey for a couple of days and am not a frequent HTML, JavaScript developer, nor have any experience with php or even mysql, so please let me know any comments on the above.

Again I hope this helps someone.
The topic has been locked.
More
5 years 8 months ago #171850 by istewart6
Replied by istewart6 on topic Reading from a Database
This looks like it could be really helpful for me, thanks! I've been looking for a way to store tokens and retrieve those tokens for users on the fly. Question: is your php script stored on the LimeSurvey server or is it local to your computer?
The topic has been locked.

Lime-years ahead

Online-surveys for every purse and purpose