database lookup and input output tool

database lookup and input output tool

Jamie Tinley -
回帖数:10

I know how to create a database input with lookups using excel VBA and with google scripting but I have no idea how to do it using MYSQL database or the database module in moodle.

Here is an example of what I want to accomplish and I want to do it INSIDE moodle and not by a URL link to something outside moodle as I want the user to experience this happening inside moodle without leaving the site.\


A user types in a number say  they get a 25 in red at the beg of year that would be 97 on the lookup. Then they get a 30 in blue in the middle of the year which would be a an 88 and they end the year with 33 in green which is an 83.  Then maybe it looks up a different table depending on the year in school. 

Does anyone know how I can do this in Moodle. I can easily do it in excel but that's not the platform I want.

Thanks,

Jamie



回复Jamie Tinley

Re: database lookup and input output tool

Itamar Tzadok -

If what you need is just a sort of calculator and you're not storing any user data, this should be fairly easy to do as a javascript widget which you can simply add to the page either in a label resource or a page block. 微笑

回复Itamar Tzadok

Re: database lookup and input output tool

Jamie Tinley -

Thanks Itamar - you are correct that I'm not storing any user data.  Widgets sound good but I have never created a javascript widget before. Can you point me in a direction on how to do lookup functions in Javascript and making widgets?  Thanks


James

回复Jamie Tinley

Re: database lookup and input output tool

William Lu -
Particularly helpful Moodlers的头像

Hi Itamar, me too. I want to learn about how to make Javascript to work with Database module. Would you please recommend some websites where I can get some ideas?

Thanks

William

回复William Lu

Re: database lookup and input output tool

Itamar Tzadok -

Have you looked at Database_templates#Javascript_template for the basic idea? From there it is just a matter of what you want to do, googling a possible solution and implementing it in your activity. That's at least is how I go about that when I'm not writing to solution from scratch. 微笑

回复Itamar Tzadok

Re: database lookup and input output tool

Jamie Tinley -

I just checked out the template page but I don't know any Javascript and I googled basic Javascript templates for calculators and lookup tables but I don't get it yet.  For example, what would I put in for 'named' and how would I ask the user for input. Where would i place the table in the script below?  It's not intuitive for me even after googling examples..  Can you help me a little more.

var cnt = 0; var re = /foo|Foo/; function init(){ var namedElements = document.getElementsByName("named"); for (i=0; i < namedElements.length; i++) { if(re.test(namedElements[i].innerHTML)) cnt++; } var namedResult = document.getElementsByName("result"); namedResult[0].innerHTML = cnt; } window.onload = init;



回复Itamar Tzadok

Re: database lookup and input output tool

Jamie Tinley -

I tried another script example I found here but WHERE do I paste it?  I put it into a label but that does not work.  How do you make a javascript work?


var data = [
["female", "25", "182-28"],
["female", "30", "398-27"],
["female", "35", "287-39"],
["male", "25", "182-28"],
["male", "30", "298-21"],
["male", "35", "929-88"]
];
function search(searchValue1, searchValue2) {
var i;
for (i = 0; i < data.length; i++) {
if (data[i][0] === searchValue1 && data[i][1] === searchValue2) {
return data[i][2];
}
}
}
var result = search("male", "25");
回复Jamie Tinley

Re: database lookup and input output tool

Itamar Tzadok -
You can add the javascript to the header or footer part of the template in html mode inside a script tag, as in:
<script>
var data = [1, 2, 3];
...
</script>

This way it will apply only to that template but it may require you to turn off editor when editing the template as the editor may strip off the script tag.

Alternately, you put the javascript in the Javascript tab of the Database activity. More convenient for editing, but it will apply to all 3 templates (list, single, edit) unless you condition its execution in some way. One way is to put it in a question and then add to the target template a call to the question. For example,

In the javascript tab:

function myCalculator() {
var data = [1, 2, 3];
...
}

In the header or footer part of the template:

<script>
myCalculator();
</script>

Or, you can use an element that you know to exist only in the target template to condition the execution of the script (you can add that element to the template). For exmple:

In the header or footer part of the target template add something like:

<div id="my-calculator-div"></div>

In the javascript tab:

if (document.getElementById("my-calculator-div")) {   
var data = [1, 2, 3];
...
}

hth 微笑

回复Jamie Tinley

Re: database lookup and input output tool

Itamar Tzadok -

If you need ot make it work on a course page, you can add it to a label resource or to an html block. The javascript has to be in a script tag (see other post). Make sure the editor doesn't strip off the tag. If it does, turn off the editor in your user profile when editing this label/block. You also need to add some html to display input box and submit button. Again, you can google for simple examples. Basically, you need something like:

<input id="my-calculator-input" type="text" />
<div id="my-calculator-result" style="width:240px;background:#CCFF99;border:1px solid #DDD;margin:10px 0;">&nbsp;</div>
<div><input type="button" onclick="myCalculator();" value="Show result" /></div>
<script>
function myCalculator() {
var data = [];
data[30] = 89;
data[33] = 95;
var inputval = document.getElementById("my-calculator-input").value;
// Check that data for inputval is defined.
if (typeof data[inputval] == 'undefined') {
var result = 'No results for specified input';
} else {
var result = data[inputval];
}
document.getElementById("my-calculator-result").innerHTML = result;
}
</script>

hth 微笑

回复Itamar Tzadok

Re: database lookup and input output tool

Jamie Tinley -

Thanks you thank you Itamar!  it works now!  I am so thankful to learn how to do a calculator from you.

For anyone following just copy his code into a label after clicking <> to do it in html form. (may not be necessary)

save. type in 30 and it will output 89 as in my example (attached)


Thanks again.

James

附件 demo pic table.jpg
回复Jamie Tinley

Re: database lookup and input output tool

roger holroyd -

i found that the javascript that Jamie posted worked if it's placed in the javascript template, then called it from one of the html templates e.g.

<p id="demo"></p>

<script>
document.getElementById("demo").innerHTML = result.valueOf();
</script>