Dynamically display ODK data on a website using MySQL

Development
1

Hi there!
I am new to this community, and ODK in general. I am working in the pharmaceutical industry, and have limited coding knowledge. I started learning python and recently moved into web-development and other front-end stuff.

My setup:
Apache, Tomcat, virtual debian machine, Aggregate

The story
I am working on adapting ODK data collection for "live-updating" a website with submissions from ODK collect. Up untill now, it seems the best approach for this is using ajax calls to retrieve data from the MySQL tables to load data and chart (or display image galleries) based on that. Unfortunately, I am not really good at php/javascript, and I cannot seem to load the images from the *_BLN tables. I have found a similar (How to Access Photos from MySQL Database? - #6 by yanokwa) question about how to display images from the SQL database, but I really do not understand the link he is trying to get at..
So far, I have been able to load numerical data from the SQL rows associated with certain forms, but I cannot seem to do that with the images. I have also found several question/ask sites stating that images should never be retrieved directly from a database, but rather just the file-path should be retrieved, and then the images should be stored in a directory on the server. As I understand the MySQL/Aggregate behaviour, the BLOB image is really the data, but is a binary file that needs to be reconstructed in order to get the full image?? In that case my question is this:

The questions I have

  1. Is there some API-tool to acces form-data (automatically! Remember I need the website to update with new submissions without retyping any code, as long as the forms stay the same).
  2. How do I acces and/or reconstruct image-data stored in the MySQL database, so that I can display it on my website.

PS: I have to keep everything offline, so anything including google fusion tables etc. won't do :frowning:

Thank you so much!

2

If the MySQL database store the images in BLOB fields then the image is stored as binary data. Follow this post which show how to do it.

On question 1. You want to have a website that needs to do something with each submission in aggregate?

1 Like
3

Thanks for your response qlands. I have, since my question post, been able to replicate the URL to the images by urlencode/decode, passing in the URI identifier (which I pull from my SQL database), so I decided to use te html code to display my submitted images.
I could acheive "auto-updating" of my website by running a php script that queries my sql database for the URI which I used to make a list of links. Then a for-loop runs making thumbnail html code blocks as long as the list of image-links.

Thanks for your link to the post. In case I wouldn't have figured the links out, it would have been very helpful!

All the best,
Andreas

3 Likes
4

Hi Kjaer,

I have just created an ODK aggregate server using MySQL. But Wanted to display the data online through a web. Can you give me some direction on to go about it?good if it is live updating.

Thank you so much

5

https://www.nature.com/articles/d41586-018-01322-9?utm_source=twt_na&utm_medium=social&utm_campaign=naturemarketing

6

hi Kjaer,

you could try my site https://accounts.gocollectinfo.com . Login with demo password is 1234567. This is base on php mysql javascript. Let me know if this is what you need.

7

Hi Clickuinternet,

Is this for live updating of ODK data? it doesn't login with demo username and 1234567 password.

Thanks,
Naod

8

Sorry my mistake, use demo@gocollectinfo.com as username

9

Thank you Clickuinternet. That is exactly what i am looking for. So can you give me a brief idea how the data at the ODK aggregate is connected with this page?

Thanks,
Naod

10

Naod,

I bypassed the ODK aggregate. I used mysql queries and php script for the data. Modified some odk tables inside Mysql.

11

Here is the link to display data and images on html using php and ajax.
https://github.com/ysumit52/ODK-Fetch-Data-

2 Likes
12

HI
it is my pleasure to come across your post because i have been searching for the method to use to save odk into database using php and mysql. Please i am a beginner in odk, can you allow me to see the source code or give me time to explain how you bypass the odk aggregate. Very urgent please.Thanks.