Use ODK with MySQL Database and PHP

was wondering if there is an API in PHP or a PHP script that may receive finalized forms from ODK collect and store into a mysql database ??....

1 Like

I'm not aware if there's anything official, but I (and a few others, I think) have worked with custom PHP/MySQL backends for Collect for some years. My implementations are all very project-specific rather than using a general framework, but I'll be happy to provide some code samples or help with solving specific problems if you would find that useful.

Hi. I'd be interested in some code samples to get kick-started. I've just set up Aggregate on my own Synology with a MariaDB back-end. Would be great to have a Web page for reports.

How I will proceed,
thank you in advance

Hi, Your code would be interesting to me because it will allow me to solve my problem

Out of curiosity, why do you specifically need a PHP and MySQL solution?

because i have developped my platform in symfony and i will get the data by odk

Perhaps I am just being nosy here, but might it be easier to set up ODK Aggregate as-is with its own stack, and integrate with it over an API such as the OpenRosa API?

I'm afraid I don't use Aggregate at all, my serverside stuff is all PHP interacting directly with Collect using the OpenRosa API. So I have no idea how to extract data from Aggregate - I only know how to use PHP to send forms to Collect, and to process submitted form data into my own MySQL backend. Sorry!

ok your method is interresting, how do you did it.

Thank you

Caveat - some of this code is very old, and may include stuff which is deprecated or no longer required by the latest version of Collect. If more knowledgeable folks know better, please correct any misunderstandings in the content below. All I can say for definite is that this approach does work with the latest Collect version.
Also, the loose typing inherent in this kind of PHP will be alien to any Java people, but please don't criticise, because that's what the OP asked for!
To accept Collect submissions, you first need to set your server hostname and submission URL in Collect's Preferences - you'll need to set the Server URL to point to your own server. The Submission Path is "/submission/" by default, but again, it can be changed in the Collect prefences.
At the submission URL, you need to provide an OpenRosa-compliant set of responses to Collect's submission request.
The submission request has two stages - a HEAD request first, then a POST request - the HEAD request expects a Location header response which gives Collect a submission URL for the POST request.
The POST request posts the XML file of completed form data, plus any extra asset files such as photos or images of signature captures etc.
So my form processor looks something like this (NB this is for illustration only and untested, and by no means optimal or elegant!).

//With default Collect Submission path setting, this script would be located at https://your.server/submission/index.php
//this is where all the uploaded files will eventually be stored.
//Set a variable to tell us whether an XML file has successfully been uploaded later:
//we need to process POST and HEAD requests separately, and cater for GET just in case
//Issue these response headers to both HEAD and POST requests:
header("Content-Type: text/xml; charset=utf-8");
header("Content-Language: en");
header("X-OpenRosa-Version: 1.0");
header("X-OpenRosa-Accept-Content-Length: 10000000");//limit upload to 10Mb - probably too high for most realistic scenarios!
//Now process POST and HEAD requests differently
if ($method=='post') {
	//we should have at least one POSTED file
	if (isset($_FILES)) {
		//we need to move all the POSTed files from the tmp upload folder, as with any file POST, then respond with a Success code and message.
		foreach($_FILES as $file) {
			//There should only be one XML file, which is the completed form.  Any other files are assets, whose file names will be included in the form data.
			if ($file['type']=="text/xml") $xmlfile=$file['name'];
		header("HTTP/1.1 202 Accepted");
		echo '<?xml version="1.0" encoding="UTF-8" ?>';
		echo '<OpenRosaResponse xmlns="" items="1">';
		echo '<message nature="submit_success">Form submitted successfully</message>';
		echo '</OpenRosaResponse>';	
	else {
		/* If $_FILES is not set in the POST, that means we have no XML file of the completed form, so we need to return an error.  This should really be a 400, but 
		in my original implementation circa 2012, Collect didn't interpret a 400 response correctly, and only a 403 response would produce a human-readable 
		error message in Collect. */
		header("HTTP/1.1 403 Forbidden");
		echo '<?xml version="1.0" encoding="UTF-8" ?>';
		echo '<OpenRosaResponse xmlns="" items="1">';
		echo  '<message nature="submit_success">Submission unsuccessful - no data files were received.</message>';
		echo '</OpenRosaResponse>';	
if ($method=='head' || $method=='get') { 
	//This is the initial HEAD request from Collect, so we need to return a Location header telling Collect where to POST the completed form.  
	//We also provide this response to GET requests to give us browser testing options.
	header("Location: $_SERVER[SCRIPT_URI]");//this script - 
	header("HTTP/1.1 204 No Content");//that's all, no actual content in the response.
if ($xmlfile!==false){
  //now process the contents of $path.$xmlfile however you want to!
  require 'my_form_processor_script.php';	

Comments and corrections welcome!

1 Like

thanks, I will try this

@abdoulaye_diop1, we use collect with kobotoolbox and deliver data to a mysql database. My frontend is php (charts, maps, table output) .

Have you tried

Hi I'm just new to this forum and want to show what I have accomplished using ODK as my backend. I've been developing in MYSQL/PHP for ODK. Its just a simple website that translate data received from ODK Collect. I'm renting out my application if anyone is interested. You can host your data with full access.
The site is
password: 1234567
Please note some modules are still in development.

1 Like

New update. Please have a look at my latest development.
username: demo
password: 1234567