Check out the LimeSurvey source code on GitHub!
Welcome, Guest
Username: Password:

TOPIC: Reading from a Database

Reading from a Database 3 years 3 months ago #97389

  • FewPexia
  • FewPexia's Avatar
  • Offline
  • Fresh Lemon
  • Posts: 6
  • Karma: 0
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 administrator has disabled public write access.

Reading from a Database 3 years 2 months ago #97412

  • tpartner
  • tpartner's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 6305
  • Thank you received: 1407
  • Karma: 546
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 administrator has disabled public write access.

Reading from a Database 3 years 2 months ago #97432

  • FewPexia
  • FewPexia's Avatar
  • Offline
  • Fresh Lemon
  • Posts: 6
  • Karma: 0
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 administrator has disabled public write access.

Reading from a Database 3 years 2 months ago #97433

  • tpartner
  • tpartner's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 6305
  • Thank you received: 1407
  • Karma: 546
Assuming the question that you are trying to load is a short-text and your code is in the source of that question, try:
$('#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 administrator has disabled public write access.

Reading from a Database 3 years 2 months ago #97434

  • tpartner
  • tpartner's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 6305
  • Thank you received: 1407
  • Karma: 546
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 administrator has disabled public write access.

Reading from a Database 3 years 2 months ago #97435

  • FewPexia
  • FewPexia's Avatar
  • Offline
  • Fresh Lemon
  • Posts: 6
  • Karma: 0
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 administrator has disabled public write access.

Reading from a Database 3 years 2 months ago #97436

  • tpartner
  • tpartner's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 6305
  • Thank you received: 1407
  • Karma: 546
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 administrator has disabled public write access.

Reading from a Database 3 years 2 months ago #97437

  • FewPexia
  • FewPexia's Avatar
  • Offline
  • Fresh Lemon
  • Posts: 6
  • Karma: 0
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 administrator has disabled public write access.

Reading from a Database 3 years 2 months ago #97438

  • tpartner
  • tpartner's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 6305
  • Thank you received: 1407
  • Karma: 546
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:
$('#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 administrator has disabled public write access.

Reading from a Database 3 years 2 months ago #97439

  • FewPexia
  • FewPexia's Avatar
  • Offline
  • Fresh Lemon
  • Posts: 6
  • Karma: 0
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 administrator has disabled public write access.

Reading from a Database 3 years 2 months ago #97475

  • FewPexia
  • FewPexia's Avatar
  • Offline
  • Fresh Lemon
  • Posts: 6
  • Karma: 0
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 administrator has disabled public write access.
Time to create page: 0.297 seconds
Imprint                   Privacy policy                  Revocation information and revocation form