Offline Form Submits To Local DB but pushes data to MySQL Server when online

[JonC]

Hi All

This is my first post, but i have been having fun creating some app's  on AI for quite a while now.

I have a form, that when submitted connects to a PHP script on my web server, which inserts the form data into a MySQL database, this all works perfectly but the device has to be online for this to work.

What I need to happen is that the users can submit the form multiple times when offline, the data is stored on the device, and when the device is back online it sends all the data to the MySQL database.

The form has 13 fields

Is this possible? and if so can anyone help pointing me in the right direction?

Thank you

Jon

[Enis]

Hi Jon:

Your best bet would be to store the data, as lists, in a TinyDB.  THEN, try to upload.  If the upload is successful, then clear the TinyDB so you can start over.  That way, you have the data stored until there's an active connection.

[Taifun]

as Enis said...

btw. how does your php script look like?

did you define an input  parameter for each column?

or did you follow my example http://puravidaapps.com/mysql.php ?

Taifun

[JonC]

Hi both, thanks for your replies

My php script is:

<?php

$con=mysqli_connect("localhost","username","password","database");

// Check connection

if (mysqli_connect_errno()) {

  echo "Failed to connect to MySQL: " . mysqli_connect_error();

}

// escape variables for security

$jobnumber = mysqli_real_escape_string($con, $_GET['jobnumber']);

$assetnumber = mysqli_real_escape_string($con, $_GET['assetnumber']);

$serialnumber = mysqli_real_escape_string($con, $_GET['serialnumber']);

$make = mysqli_real_escape_string($con, $_GET['make']);

$model = mysqli_real_escape_string($con, $_GET['model']);

$description = mysqli_real_escape_string($con, $_GET['description']);

$refrigeranttype = mysqli_real_escape_string($con, $_GET['refrigeranttype']);

$refrigerantqty = mysqli_real_escape_string($con, $_GET['refrigerantqty']);

$itemcondition = mysqli_real_escape_string($con, $_GET['itemcondition']);

$installdate = mysqli_real_escape_string($con, $_GET['installdate']);

$location = mysqli_real_escape_string($con, $_GET['location']);

$comments = mysqli_real_escape_string($con, $_GET['comments']);

$maintcomp = mysqli_real_escape_string($con, $_GET['maintcomp']);

$sql="INSERT INTO appdata (jobnumber, assetnumber, serialnumber, make, model, description, refrigeranttype, refrigerantqty, itemcondition, installdate, location, comments, maintcomp)

VALUES ('$jobnumber', '$assetnumber', '$serialnumber', '$make', '$model', '$description', '$refrigeranttype', '$refrigerantqty', '$itemcondition', '$installdate', '$location', '$comments', '$maintcomp')";

if (!mysqli_query($con,$sql)) {

  die('Error: ' . mysqli_error($con));

}

echo "1 record added";

mysqli_close($con);

?>

The form may be submitted quite a few times when offline, and one thing I would like is for it to automatically upload to to the MySQL server when the device comes back online

I don't know if this is possible

Thanks for your help, much appreciated

Jon

[JonC]

Hi Again

To further update...

I have added the TinyDB and submitted the data via a list to it. 

I can view the data in TinyDB in the format:

(a1 a2 a3 a4 a5 a6 a7 a8 a9 a10 a11 a12 a13) (b1 b2 b3 b4 b5 b6 b7 b8 b9 b10 b11 b12 b13)

I have created a sync button that checks if the device is online, and if it is submits the data, but I cant get it to pull the data from the TinyDB in the correct format, then submit it all to the PHP Script, then clear the TinyDB once submitted

Please see attached

Thanks

Jon

[Taifun]

probably this helps: How to work with Lists by Saj

I you are asking for help, I recommend you to make it as easy for others to be able to help you ...

You probably will get more feedback then...

which means in your case post a screenshot of your relevant blocks...

To download the aia file, upload it to  App Inventor, open it, do some bug hunting for you, etc... this takes time, and most people will not do that...

Thank you.

Taifun

---

Trying to push the limits of App Inventor! Snippets and Tutorials from Pura Vida Apps by Taifun.         

[JonC]

Thanks for your reply, and sorry for posting the .aia file, but thanks for your tip in posting the screen shot

I had already seen the article that you advised and it helped me to get to where i am now

I have attached a screenshot of part of my blocks, as previously mentioned:

I can view the data in TinyDB in the format:

(a1 a2 a3 a4 a5 a6 a7 a8 a9 a10 a11 a12 a13) (b1 b2 b3 b4 b5 b6 b7 b8 b9 b10 b11 b12 b13)

I have created a sync button that checks if the device is online, and if it is submits the data, but I cant get it to pull the data from the TinyDB in the correct format, then submit it all to the PHP Script, then clear the TinyDB once submitted

Really appreciate your help

Thanks

Jon

[Taifun]

your screenshot is almost correct

1. put an create empty list block into the valueIfTagNotThere socket

2. instead of the TinyDB.GetValue block use an select list item block

    to get the first item from the list dbValue, (which is the JobNumber), just write 

    select list item get dbValue

                            1 

3. to get the 2nd item from the list dbValue (which is the AssetNumber), just write 

    select list item get dbValue

                            2 

etc.

Taifun

http://appinventor.mit.edu/explore/ai2/support/blocks/lists.html#selectlistitem

select list item

Selects the item at the given index in the given list. The first list item is at index 1.

[JonC]

Thank you so much, that has solved that part, and the data is now submitted to the database into the correct fields.

The only issue now is that it continues to submit the same data to the database over and over again in a loop

So if the form had been submitted via the App 8 times with different information in, therefore there are 8 records in the TinyDB each with 13 fields, how can i alter my script so that when pressing the sync button, it loops through the 8 records submitting them to the MySQL DB, then stops, displays a message saying "data sent" and clears the TinyDB?

Thanks again

Jon

[Taifun]

let me suggest the following logic

1. read only the first record and send it to the php script

2. in the web.GotText event: 

    if you get success back: delete the first record, if there are further records to transfer, repeat 1. else stop

Taifun

[JonC]

Got it...

Thank you so much for your help

Jon