how can I input data from a database into a sketch?

Programming Questions

david_71

how can I input data from a database into a sketch?

in Programming Questions • 2 years ago

Hello all,

I am putting together a website (using html, php, mySQL, javascript, jquery, and using the joomla system) and have been having trouble figuring out how to input data which is accessed from the website database for use in an applet which was built as a sketch in Processing and exported as an applet. I am a bit of a programming noob, but have managed to figure out this much:

- create a working sketch using the processing application (I'm making basically a replica of the facebook app Social Graph -- this part is working pretty well actually :) )
- export the sketch as an applet which runs successfully in my browser by opening index.html in the folder "applet"
- move the folder "applet" and its contents (including index.html) to a directory in my website
- display the applet on my website (currently hosted locally on my MacBook Pro) (I just call index.html)

Also, I do know how to access data from the database and use it for other purposes. Easy once you know how to do it, a pain in the ass to figure out the first time! ;)

What I have not figured out is how to pass data to my applet. Right not, I have a file data.txt which contains the relevant data (a list of users and a list of who is friends with whom), and when I run the sketch in Processing, the file is read successfully. So my first plan was to write some php or html code to modify the .txt file before the applet gets called by my website. However, I have found that once I've exported the applet, any changes I make to the .txt file (in the folder: data) are not reflected when I call the applet. So apparently, data.txt is accessed when I run the sketch in Processing, but the file is no longer needed once the sketch is exported as an applet.

I am sure there is a veeeerrrry simple way to do this, I just don't know it. Suggestions?

Replies(8)

mbraendle

Re: how can I input data from a database into a sketch?

2 years ago

There are various options:

1. You could use the SQLibrary to query the database directly.

2. You could query MySQL using PHP and transform the results to XML, which can be read (as file or via an URL) using Processing XMLElement.

PhiLho

Re: how can I input data from a database into a sketch?

2 years ago

I think the text file you mention is copied in the jar, so the applet will always use the same one.
As mbraendle mentioned, the best option is to make a PHP script doing the query: the SQLibrary is good, but it is unsafe to use it in an applet (one can easily get the DB credentials from it).
The PHP script doesn't need to write a text file. Actually, it is quite a bad idea as different users can access the data at the same time, so there can be a clash.
The common idea is to make a Web API. For example, you build an URL like http://www.example.com/SocialGraph/query.php?r=someName where I suppose the query depends on parameter(s). It is not mandatory...
The PHP script query the database, build the result (can be CSV, XML, Json, etc.) and just send it in answer to the HTTP request.
The data can be fetched from the applet with a simple loadStrings() or XMLElements() or similar, using an URL as path.

david_71

Re: how can I input data from a database into a sketch?

2 years ago

Hey mbraendle and phi.lho,

Thanks for the replies. I've managed to make a .xml file according to the instructions here:

http://www.processing.org/reference/XMLElement.html

(I put the file 'sites.xml' in the data folder with my sketch and access it with the command:

xml = new XMLElement(this, "sites.xml");

) and got that working, but once again once I make the applet (and launch by opening index.html in my browser), the output no longer depends on sites.xml (I can change or even delete sites.xml and the applet acts as if reading the original file), so I'm surmising the data gets copied to the jar just like phi.lho says happens with the txt file.

So I placed sites.xml on my website (actual hosted site, not my local computer) and I've gotten my sketch to access it by replacing the above line with

xml = new XMLElement(this, "http://valencia.directrouter.com/(my website)/sites.xml");

but this time when I export as an applet and launch it (opening index.html on my browser), it acts as if it can no longer read it.

Any ideas what I am doing wrong?

mbraendle

Re: how can I input data from a database into a sketch?

2 years ago

If your applet is hosted by another computer than where the sites.xml file is hosted, you need to sign your applet: See Processing Wiki.

Indeed, the files in the data folder are embedded into the applet .jar file upon export.

david_71

Re: how can I input data from a database into a sketch?

2 years ago

OK, working now! I didn't sign the applet but I placed it on my web-hosted site (applet and .xml file both web hosted) so it shouldn't need to be signed and it works. If I manually alter the web-hosted sites.xml file, the applet output is duly altered. (I haven't incorporated php code into the .xml file yet but that shouldn't be a problem. )

So iiuc, the locally hosted sites.xml file gets embedded in the .jar file, but the web-hosted file does not. Damn that was frustrating that doing this

xml = new XMLElement(this, "sites.xml");

behaved differently than doing this

xml = new XMLElement(this, "http://valencia.directrouter.com/(my website)/sites.xml");

but I think I have it figured out now.

Thx guys -

david

david_71

Re: how can I input data from a database into a sketch?

2 years ago

OK, so I've encountered another hurdle.

Right now, it's up and running on my web-hosted site and works like this: when a user accesses the relevant page, some php code gets executed which queries the database and spits out a file 'socialGraphData.xml,' and then the applet gets called which accesses the newly created file using the line:

xml = new XMLElement(this, "http://(my.website.com)/.../socialGraphData.xml");

My next hurdle is to design it so that the output of the applet will be different for different users (obviously they each have a unique social network). I'm thinking to set it up so that whenever a new user account (e.g. user_id_35) is created, a new folder (user35) will be set up in which will be placed a copy of the applet, and that the user-specific socialGraphData.xml will be written to that folder (or to a subfolder 'data' I suppose).

My first thought would be to code the sketch with this line:

xml = new XMLElement(this, "socialGraphData.xml");

but from the above discussion, iiuc the resulting applet would have a fixed set of data embedded in the .jar file and would not depend on the xml file. (I'm a bit perplexed as to why it works this way, what's the point of putting the data in a separate file in the first place??)

So instead, I figured I could write the sketch with a line like this, specific to the user:

xml = new XMLElement(this, "http://(my.website.com)/.../user35/socialGraphData.xml");

The problem is that, for each and every user, it will be necessary to make a new sketch, generate the applet, and upload it to the website by hand. I'm sure there's a simpler way to do this ... ??

( I've been trying to figure out a way to access the current url, so my sketch would have something like this:

xml = new XMLElement(this, " [...current url...] /socialGraphData.xml");

but haven't figured out how to do that yet ...)

mbraendle

Re: how can I input data from a database into a sketch?

2 years ago

As phi.lho and I tried to explain, don't create files, but create the XML data via PHP on the fly and send it through HTTP.

In PHP, you can do that by using

header('Content-type: text/xml');

which tells the browser (or the applet) that XML data will come instead of HTML, and then spit out the XML data directly instead of saving it to a file.

I use different technology for feeding my applet with database content, but the overall scheme, whether one uses XML/XSLT or PHP is about the same:

Applet sends URL query via HTTP --> Web engine translates to DB query --> DB carries out query and send to Web engine --> Web engine sends XML in XML schema of DB --> Web engine transforms with XSLT to target XML --> web server sends XML data to applet

The Web engine and the Web server software keep track of from where the query was sent and to where the results must be sent, so you don't have to think about that.

david_71

Re: how can I input data from a database into a sketch?

2 years ago

Hey mbraendle and phi.lho,

I did finally manage to implement your suggestions.

I renamed the hosted file socialGraphData.xml to socialGraphData.php, changed its contents from:

<?xml version='1.0'?>

... xml content ...

</friends>

to:

<?php

header('Content-type: text/xml');

echo "<?xml version='1.0'?>

... xml content ...

</friends>";

and changed this:

xml = new XMLElement(this, "http://(my.website.com)/.../socialGraphData.xml");

to:

xml = new XMLElement(this, "http://(my.website.com)/.../socialGraphData.php");

Also, I inserted php code into socialGraphData.php to do a simple query of the database, echo the result into the xml content, and have the data show up in the applet output. So it seems to be working, whew!

Top Reply