Connect App Inventor to MySQL Database

Connect App Inventor to MySQLIn this tutorial I want to show you how to connect App Inventor to a MySQL database using PHP. I tried to keep everything as simple as possible while at the same time teaching enough so that you can do anything.

You’ll be able to submit to, update and then receive a CSV file from the database using the PHP files below. You’ll also be able to limit access to data simply by creating a different PHP file for each person that would need to access the database. Everything you need can be found below.

If you like videos like this, it helps my search results if you tell Google Plus with a click here

MySQL Database Setup 

App Inventor Database

App Inventor MySQL Design View

App Inventor Designview PHP MySQL

App Inventor MySQL Blocks

Click the image below a few times to view it full screen

AppInventor22Blocks

PHP Code that You’ll Upload to a Server

<?php

DEFINE ('DBUSER', 'YourDatabaseUserName'); 
DEFINE ('DBPW', 'YourPassword'); 
DEFINE ('DBHOST', 'YourDatabaseHost'); 
DEFINE ('DBNAME', 'YourDatabaseName'); 
 
$dbc = mysqli_connect(DBHOST,DBUSER,DBPW);
if (!$dbc) {
    die("Database connection failed: " . mysqli_error($dbc));
    exit();
}

$dbs = mysqli_select_db($dbc, DBNAME);
if (!$dbs) {
    die("Database selection failed: " . mysqli_error($dbc));
    exit(); 
}

$result = mysqli_query($dbc, "SHOW COLUMNS FROM customer");
$numberOfRows = mysqli_num_rows($result);
if ($numberOfRows > 0) {

/* By changing Fred below to another specific persons name you can limit access to just the part of the database for that individual. You could eliminate WHERE recorder_id='Fred' all together if you want to give full access to everyone. */

$values = mysqli_query($dbc, "SELECT * FROM customer WHERE recorder_id='Fred'");
while ($rowr = mysqli_fetch_row($values)) {
 for ($j=0;$j<$numberOfRows;$j++) {
  $csv_output .= $rowr[$j].", ";
 }
 $csv_output .= "\n";
}

}

print $csv_output;
exit;
?>

<?php 

DEFINE ('DBUSER', 'YourDatabaseUserName'); 
DEFINE ('DBPW', 'YourPassword'); 
DEFINE ('DBHOST', 'YourDatabaseHost'); 
DEFINE ('DBNAME', 'YourDatabaseName'); 

$dbc = mysqli_connect(DBHOST,DBUSER,DBPW);
if (!$dbc) {
    die("Database connection failed: " . mysqli_error($dbc));
    exit();
}

$dbs = mysqli_select_db($dbc, DBNAME);
if (!$dbs) {
    die("Database selection failed: " . mysqli_error($dbc));
    exit(); 
}

$FirstName = mysqli_real_escape_string($dbc, $_GET['FirstName']);
$LastName = mysqli_real_escape_string($dbc,$_GET['LastName']);
$Street = mysqli_real_escape_string($dbc,$_GET['Street']);
$City = mysqli_real_escape_string($dbc,$_GET['City']);
$State = mysqli_real_escape_string($dbc,$_GET['State']);
$Zip = mysqli_real_escape_string($dbc,$_GET['Zip']);
$Email = mysqli_real_escape_string($dbc,$_GET['Email']);
$Phone = mysqli_real_escape_string($dbc,$_GET['Phone']);
$Recorder = mysqli_real_escape_string($dbc,$_GET['Recorder']);

$query = "INSERT INTO customer (first_name, last_name, street_address, city, state, zip_code, email, phone_number, recorder_id) VALUES ('$FirstName', '$LastName', '$Street', '$City', '$State', '$Zip', '$Email', '$Phone', '$Recorder')";

$result = mysqli_query($dbc, $query) or trigger_error("Query MySQL Error: " . mysqli_error($dbc)); 

mysqli_close($dbc); 

?>

<?php 

DEFINE ('DBUSER', 'YourDatabaseUserName'); 
DEFINE ('DBPW', 'YourPassword'); 
DEFINE ('DBHOST', 'YourDatabaseHost'); 
DEFINE ('DBNAME', 'YourDatabaseName'); 

$dbc = mysqli_connect(DBHOST,DBUSER,DBPW);
if (!$dbc) {
    die("Database connection failed: " . mysqli_error($dbc));
    exit();
}

$dbs = mysqli_select_db($dbc, DBNAME);
if (!$dbs) {
    die("Database selection failed: " . mysqli_error($dbc));
    exit(); 
}

$FirstName = mysqli_real_escape_string($dbc, $_GET['FirstName']);
$LastName = mysqli_real_escape_string($dbc,$_GET['LastName']);
$Street = mysqli_real_escape_string($dbc,$_GET['Street']);
$City = mysqli_real_escape_string($dbc,$_GET['City']);
$State = mysqli_real_escape_string($dbc,$_GET['State']);
$Zip = mysqli_real_escape_string($dbc,$_GET['Zip']);
$Email = mysqli_real_escape_string($dbc,$_GET['Email']);
$Phone = mysqli_real_escape_string($dbc,$_GET['Phone']);

$CustomerId = mysqli_real_escape_string($dbc,$_GET['CustomerId']);

$query = "UPDATE customer SET first_name='$FirstName', last_name='$LastName', street_address='$Street', city='$City', state='$State', zip_code='$Zip', email='$Email', phone_number='$Phone' WHERE cust_id='$CustomerId'";

$result = mysqli_query($dbc, $query) or trigger_error("Query MySQL Error: " . mysqli_error($dbc)); 

mysqli_close($dbc); 

?>

<!-- http://newjustin.com/updatecust.php?FirstName=Sue&LastName=Banas&Street=123&City=Pittsburgh&State=PA&Zip=15222&Email=derek@aol.com&Phone=4125551212&CustomerId=14
-->

60 Responses to “Connect App Inventor to MySQL Database”

  1. Ghost says:

    The blocks are too low resolution 🙁

  2. Manuel Silverio says:

    I’m looking forward for the next contest…. my android app is almost ready.. is it soon?

    • Derek Banas says:

      I haven’t fully decided when it will end. I want to give everyone enough time. I’ll probably give everyone up until the end of next month to finish. best of luck 🙂

  3. Reny says:

    Hi Derek,

    Thank you so much for an amazing video!

  4. Zack says:

    Hey Derek, love your videos. I am trying to make an app for a company that allows them to search an existing database based on entered input. For example: they enter Janitor as job and building as east tower, it will retrieve all janitors who work in the east tower.

  5. Jesse says:

    Thanks for the video Derek, it’s a big help and much appreciated! I was wondering if you could at some point cover security and validation for the scripts as mentioned in the video? Or point us to some good examples we can use with your scripts above?

    Thanks for sharing

    • Derek Banas says:

      You’re very welcome 🙂 I’m planning a PHP / Zend tutorial in which i’ll cover all that you requested. A learn PHP in 30 minutes and a PHP web services tutorial should be out this month.

  6. John Gondek says:

    Mr. Banas Your video tutorials are by far the best available anywhere. I am retired and relatively new to programming and your
    tutorials have propelled me forward in PHP, Java and now Android APP Inventor. Thank you so much.

  7. ali says:

    hi
    how to create table search in database and app invertor

    tanks for learning

  8. Jen says:

    Hi Mr.Bana,
    I have been following your instructions for this tutorial, however I am having issues with retrieving the information from the mysql database to the app inventor.

  9. larry says:

    I continue to get an error code:
    500 Internal Server Error
    I have copies your code and the blocks exactly with the exception of the database connection.
    any suggestions would be wonderful.

  10. danish says:

    hi this is a great tutorial , you are my favourite for online tutorials by far! however i am having problems with implementing this code i have resolved several issues however ive hit a block with one which keeps popping up, i get the error

    “Select list item: Attempt to get item number 2 of a list of length 1: (
    )
    Note: You will not see another error reported for 5 seconds.”

    ive tried everything i could think of, but not sure why i cannot fix this?!

    please advise.

  11. David says:

    Great video
    Super clear
    is it possible to receive a link for aia download?

    Thanks

  12. Mowgli says:

    Thanks alot it is great tutorial.
    I have question. How can I add image url to ListPicker?

  13. PeterN says:

    Derek, you are awesome. By watching not only these App Inventor videos but also the others too, I’m learning so much about programming. This is building on my html, javascript, php etc knowledge.

    Thank you so much for all the time and effort you put into these tutorials, you are a star!!

  14. rafli says:

    hi derek.. u amazing.. I looking for this article for so long.. this very helpful.. now i,m working to make login by FB form on app inventor.. would u help? plss 🙂

  15. Xavier says:

    Thank you for this excellent tutorial.
    I followed the instructions as you explain it to read a database and show only one row of them in AppInventor.
    When I play the application the screen show as follow:
    “CSV text has multimple rows. Expected just one row”
    Cannot parse text argument to “list from csv row” as CSV-formatted row”

    When I show the php file in my browser, I can see one row only.
    What have I done wrong?

    Thanks in advance

    • Derek Banas says:

      You’re very welcome 🙂 Are you certain that you set up the database in the same way? Make sure your rows are all the same. Sorry, but I’m not sure what could have gone wrong.

  16. Eric Coutinho says:

    Hi Mr.Bana, I have been following your instructions for this tutorial, however I am having issues.. The ‘GetCust’ and ‘UpdateCust’ button don’t do anything.. And the ‘Listpicker’ return just a black screen to me.

  17. Roderick says:

    Hello, I could not connect the blocks I put this and that is in the video, please you can send me the files you upload to the server with their names, sorry my language is Spanish because it google translated

  18. Roderick says:

    me presenta este error The operation list to csv table cannot accept the arguments: Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /home/u647556080/public_html/custcsv.php on line 17

  19. tech says:

    Hi Derek, nice video, I have a quick question if I want to give full access by removing the WHERE clause in the select query do I need any other modifications in app inventor block. Thanks

  20. manuel says:

    Hello Derek Your video is amazing.

    I’m about the app works 100%, but when datas are saved, app sends me the following error: can not parse text argument to “list from csv table” as a csv-formatted table.

    I think this error comes from the module “When web1. GotText” but I do not know why.

    Database is recording all right but ListPicker doesn’t run because the error.

    Could you help me?

    Thank you

    • Derek Banas says:

      Thank you 🙂 I do my best. There must be something different in the database. Everything thing has to be exactly the same or you’ll get that error. Sorry, but it is hard for me to guess

      • manuel says:

        No problem Derek.

        I’ve already fixed the problem. I have added a “Web2” for the “Update” and for a new Button as “Delete”. When Web2.GotText runs, data are cleared on screen. With your programming when runs Web1.Got Text after “Update”, there was a problem with the creation of an unnecessary “csv table text”. Now all is ok. Besides I have added a “Exit” button.

  21. Marco says:

    I receive this error:
    Cannot parse text argument to “list from csv table” as a CSV-formatted table

  22. deval says:

    is this safe from sql injection?

  23. tony says:

    Hello!
    I have some questions
    After pressing the “Submit” appear {Error 1109:The specified URL is not valid:%20h%20t%20t%20……..}
    How can I solve?

  24. Ugur says:

    Derek,

    You’re awesome. Yours is the only complete tutorial I waas able to find. Thank you millions..

  25. Hussein says:

    Can you use localhost instead of online hosting?

  26. Mayank says:

    hey Derek!
    Please can you tell how to use blocks to retrieve data from sql database server on another screen!!!

  27. Arthur Akango says:

    Hey Derek/ Your video was really inspirational and insightful. Thank you so much. Will you be making one on how to connect your app inventor project on a local host any time soon?

Leave a Reply

Your email address will not be published.

Google+