Connect App Inventor to MySQL Database
In 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 [googleplusone]
MySQL Database Setup
App Inventor MySQL Design View
App Inventor MySQL Blocks
Click the image below a few times to view it full screen
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
-->
The blocks are too low resolution 🙁
Click on them and they will eventually show full screen
I’m looking forward for the next contest…. my android app is almost ready.. is it soon?
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 🙂
Hi Derek,
Thank you so much for an amazing video!
You’re very welcome 🙂
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.
Thank you 🙂 I have everything pretty much here for that. If you need more help learning SQL I cover that as well. Tell me if you need anything else.
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
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.
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.
Thank you very much for the nice compliment 🙂 I’m glad I could help
hi
how to create table search in database and app invertor
tanks for learning
You’re very welcome 🙂 You’ll have to program everything in php. I’ll be making a new PHP tutorial very soon and here is my latest one that covers everything you need.
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.
What errors are you getting?
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.
Can you pull up the php page in your browser? It is probably a permissions error if not
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.
Thank you 🙂 It is hard to figure out what could cause that error. Are you using the exact same blocks, database, etc. that I use?
Great video
Super clear
is it possible to receive a link for aia download?
Thanks
Thank you 🙂 I have everything for free on YouTube. My host doesn’t allow me to host the videos myself.
Thanks alot it is great tutorial.
I have question. How can I add image url to ListPicker?
You can change images in a list picker that has been uploaded, but I don’t believe you can use a url. I’ll have to test, but I’m guessing this is a limitation.
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!!
Thank you for taking the time to say such nice things 🙂 I’m glad I could help
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 🙂
I’m glad I could help. Most of the work of logging into Facebook will happen on the PHP side. I’ll see if I can help.
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
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.
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.
The problem probably lies with the database. It has to be exactly the same as mine. Please double check that and the problem should be fixed.
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
Sorry, but I’m not sure what you need. I have all of the files used under the video.
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
There is probably something wrong with the database setup. Make sure everything is exactly the same as I present here.
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
Thank you 🙂 You can change the query in any way, but you’ll then have to figure out how to find the data in the list through trial and error.
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
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
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.
Sorry,
your Web1 for the “Get Customers”, and the my Web2 for the “Submit”, “Update” and “Delete” buttons.
Great I’m glad you fixed it. Thank you for posting your fix 🙂
I receive this error:
Cannot parse text argument to “list from csv table” as a CSV-formatted table
Are you using the same setup as I used here?
is this safe from sql injection?
No that wasn’t the goal for this tutorial
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?
There is something wrong with the url. Try testing in your browser. Send me the whole link and I’ll take a look.
i have same problem when i click send Error 1109:The specified URL is not valid:%20h%20t%20t%20……..} do you have solution?
Yes your code is putting a %20 between every letter. %20 should only replace any spaces in your URL.
Derek,
You’re awesome. Yours is the only complete tutorial I waas able to find. Thank you millions..
Thank you 🙂
Can you use localhost instead of online hosting?
Yes
hey Derek!
Please can you tell how to use blocks to retrieve data from sql database server on another screen!!!
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?
Thank you 🙂 I’ll try to make an example for that in my new Android tutorial soon.