App Inventor - MySQL interface


App Inventor - MySQL interface

Probably you want to read this first: What is MySQL and what is a relational database?

You can use this App Inventor example together with a php script on your web server to query your MySQL database on your web server.

Shival Wolf already provided a nice App Inventor Classic - MySQL interface. I now "translated" his example into App Inventor 2 with a few adjustments. The example presented here uses the same SQL statements as used in my SQLite example.
Example queries by James. I only had to adjust one of the columns long, because long is a reserved word in MySQL.

Setup

Special Notes

Do you need help with your App Inventor project?

I can help you: for one hour I charge 25 USD.
Please contact me at info [at] puravidaapps [dot] com for details!
For questions about App Inventor, please use the App Inventor forum. Thank you.

App Inventor blocks

Please see here for the SQL syntax.

Run Query


Response Received


Display Table

To display the result table, I'm using 2 nested for each loops. In case you are interested in a nicer and more flexible solution, take a look at my dynamic table layout example.


Queries


Further blocks used


Screenshots

PHP Script

I replaced the deprecated original php MySQL API by the php MySQL Improved Extension. However, as the example is designed, this update still will not prevent from SQL Injection attacks, because all queries coming from App Inventor will be executed. You will have to use prepared statements or stored procedures to be on the safe side. See also: SQL Injection Prevention Sheet or do a Google Search do find more about SQL Injections. Also it is recommended to do at least some data validation on the App Inventor side.

New SELECT logic adjusted (result stored in temp. file removed).


Comic provided by xkcd.com. Thank you.

<?php
/*
 * Written By: ShivalWolf
 * Date: 2011/06/03
 * Contact: Shivalwolf@domwolf.net
 *
 * UPDATE 2011/04/05
 * The code now returns a real error message on a bad query with the mysql error number and its error message
 * checks for magic_quotes being enabled and strips slashes if it is. Its best to disable magic quotes still.
 * Checks to make sure the submitted form is a x-www-form-urlencode just so people dont screw with a browser access or atleast try to
 * Forces the output filename to be JSON to conform with standards
 *
 * UPDATE 2011/06/03
 * Code updated to use the Web Module instead of tinywebdb
 *
 * UPDATE 2013/12/26 and 2014/02/18
 * minor modifications by Taifun, puravidaapps.com
 *
 * UPDATE 2014/07/11
 * mysql API (deprecated) replaced by mysqli by Taifun
 *
 * UPDATE 2015/04/30
 * SELECT logic adjusted (result stored in temp. file removed) by Taifun
 *
 * UPDATE 2016/02/21
 * Bugfix Undefined variable: csv
 */

/************************************CONFIG****************************************/
//DATABSE DETAILS//
$DB_ADDRESS="";
$DB_USER="";
$DB_PASS="";
$DB_NAME="";

//SETTINGS//
//This code is something you set in the APP so random people cant use it.
$SQLKEY="secret";

/************************************CONFIG****************************************/

//these are just in case setting headers forcing it to always expire 
header('Cache-Control: no-cache, must-revalidate');

error_log(print_r($_POST,TRUE));

if( isset($_POST['query']) && isset($_POST['key']) ){                                   //checks if the tag post is there and if its been a proper form post
  //set content type to CSV (to be set here to be able to access this page also with a browser)
  header('Content-type: text/csv');

  if($_POST['key']==$SQLKEY){                                                           //validates the SQL key
    $query=urldecode($_POST['query']);
    if(get_magic_quotes_gpc()){     //check if the worthless pile of crap magic quotes is enabled and if it is, strip the slashes from the query
      $query=stripslashes($query);
    }
    $conn = new mysqli($DB_ADDRESS,$DB_USER,$DB_PASS,$DB_NAME);    //connect

    if($conn->connect_error){                                                           //checks connection
      header("HTTP/1.0 400 Bad Request");
      echo "ERROR Database Connection Failed: " . $conn->connect_error, E_USER_ERROR;   //reports a DB connection failure
    } else {
      $result=$conn->query($query);                                                     //runs the posted query
      if($result === false){
        header("HTTP/1.0 400 Bad Request");                                             //sends back a bad request error
        echo "Wrong SQL: " . $query . " Error: " . $conn->error, E_USER_ERROR;          //errors if the query is bad and spits the error back to the client
      } else {
        if (strlen(stristr($query,"SELECT"))>0) {                                       //tests if it's a SELECT statement
          $csv = '';                                                                    // bug fix Undefined variable: csv
          while ($fieldinfo = $result->fetch_field()) {
            $csv .= $fieldinfo->name.",";
          }
          $csv = rtrim($csv, ",")."\n";
          echo $csv;                                                                    //prints header row
          $csv = '';

          $result->data_seek(0);
          while($row = $result->fetch_assoc()){
            foreach ($row as $key => $value) {
              $csv .= $value.",";
            }
            $csv = rtrim($csv, ",")."\n";
          }
          echo $csv;                                                                    //prints all data rows
        } else {
          header("HTTP/1.0 201 Rows");
          echo "AFFECTED ROWS: " . $conn->affected_rows;       //if the query is anything but a SELECT, it will return the number of affected rows
        }
      }
      $conn->close();                                          //closes the DB
    }
  } else {
     header("HTTP/1.0 400 Bad Request");
     echo "Bad Request";                                       //reports if the secret key was bad
  }
} else {
        header("HTTP/1.0 400 Bad Request");
        echo "Bad Request";
}
?>

Test

Tested successfully on HTC Desire running Android 2.2, Samsung Galaxy Tab 10.1N running Android 3.2 and Nexus 5 running Android 4.4.4.

Notes

You also might be interested in the video tutorial Connect App Inventor to MySQL Database by Derek Banas.

Questions and Answers

Q1: The instructions say to add the php file to my webserver. Do I just place it in the public directory anywhere?
A: Just put it in a directory which is accessible for everyone.

Q2: Also do I need to enable remote access and add my own domain?
A: See the setup instructions.

Q3: When I insert not latin characters as values to the table I get ???? symbols on my app. My database is UTF 8 General ci. Is there a solution for this?
A: I now tested again and as you can see in the screenshot everything works fine with the default posttext block... I also use UTF-8 in the database settings.

Celog Comunidad provided the following solution:
I use mysqli_set_charset($dbc, "utf8"); and it works fine.

Q4: I installed my mysql server online and when trying your app im getting the following error: Cannot parse text argument to "list from csv table" as a CSV-formatted table. Note: You will not see another error reported for 5 seconds.
A: The list from csv table block is used in the Web1.GotText event. As a test replace the blocks in the then part of the if-then-else statement by
to find out what is going on...

Download


If you find this example helpful and you are using it in one of your projects,
be nice and donate a small amount!

Donation amount:

or donate some mBTC to Bitcoin Address:
1Jd8kXLHu2Vkuhi15TWHiQm4uE9AGPYxi8
Bitcoin

Thank you! Taifun
 

Download aia file for App Inventor
Download php script for your web server

Back to top of page ...

Creative Commons License
This work by Pura Vida Apps is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License
with attribution (name=Pura Vida Apps and link to the source site) required.


Home | Snippets | Tutorials | Extensions | News | Forums | Links | Search | Contact

'