CONNECT APP INVENTOR TO MYSQL DATABASE by Derek Banas

print
http://www.newthinktank.com/2014/05/connect-app-inventor-mysql-database/

CONNECT APP INVENTOR TO MYSQL DATABASE

Posted by on May 11, 2014 in Android Development Tutorial | 60 comments

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

01 <?php
02
03 DEFINE ('DBUSER', 'YourDatabaseUserName');
04 DEFINE ('DBPW', 'YourPassword');
05 DEFINE ('DBHOST', 'YourDatabaseHost');
06 DEFINE ('DBNAME', 'YourDatabaseName');
07  
08 $dbc = mysqli_connect(DBHOST,DBUSER,DBPW);
09 if (!$dbc) {
10     die("Database connection failed: " . mysqli_error($dbc));
11     exit();
12 }
13
14 $dbs = mysqli_select_db($dbc, DBNAME);
15 if (!$dbs) {
16     die("Database selection failed: " . mysqli_error($dbc));
17     exit();
18 }
19
20 $result = mysqli_query($dbc, "SHOW COLUMNS FROM customer");
21 $numberOfRows = mysqli_num_rows($result);
22 if ($numberOfRows > 0) {
23
24 /* 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. */
25
26 $values = mysqli_query($dbc, "SELECT * FROM customer WHERE recorder_id='Fred'");
27 while ($rowr = mysqli_fetch_row($values)) {
28  for ($j=0;$j<$numberOfRows;$j++) {
29   $csv_output .= $rowr[$j].", ";
30  }
31  $csv_output .= "\n";
32 }
33
34 }
35
36 print $csv_output;
37 exit;
38 ?>

 

01 <?php
02
03 DEFINE ('DBUSER', 'YourDatabaseUserName');
04 DEFINE ('DBPW', 'YourPassword');
05 DEFINE ('DBHOST', 'YourDatabaseHost');
06 DEFINE ('DBNAME', 'YourDatabaseName');
07
08 $dbc = mysqli_connect(DBHOST,DBUSER,DBPW);
09 if (!$dbc) {
10     die("Database connection failed: " . mysqli_error($dbc));
11     exit();
12 }
13
14 $dbs = mysqli_select_db($dbc, DBNAME);
15 if (!$dbs) {
16     die("Database selection failed: " . mysqli_error($dbc));
17     exit();
18 }
19
20 $FirstName = mysqli_real_escape_string($dbc, $_GET['FirstName']);
21 $LastName = mysqli_real_escape_string($dbc,$_GET['LastName']);
22 $Street = mysqli_real_escape_string($dbc,$_GET['Street']);
23 $City = mysqli_real_escape_string($dbc,$_GET['City']);
24 $State = mysqli_real_escape_string($dbc,$_GET['State']);
25 $Zip = mysqli_real_escape_string($dbc,$_GET['Zip']);
26 $Email = mysqli_real_escape_string($dbc,$_GET['Email']);
27 $Phone = mysqli_real_escape_string($dbc,$_GET['Phone']);
28 $Recorder = mysqli_real_escape_string($dbc,$_GET['Recorder']);
29
30 $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')";
31
32 $result = mysqli_query($dbc, $query) or trigger_error("Query MySQL Error: " . mysqli_error($dbc));
33
34 mysqli_close($dbc);
35
36 ?>

 

view sourceprint?
01 <?php
02
03 DEFINE ('DBUSER', 'YourDatabaseUserName');
04 DEFINE ('DBPW', 'YourPassword');
05 DEFINE ('DBHOST', 'YourDatabaseHost');
06 DEFINE ('DBNAME', 'YourDatabaseName');
07
08 $dbc = mysqli_connect(DBHOST,DBUSER,DBPW);
09 if (!$dbc) {
10     die("Database connection failed: " . mysqli_error($dbc));
11     exit();
12 }
13
14 $dbs = mysqli_select_db($dbc, DBNAME);
15 if (!$dbs) {
16     die("Database selection failed: " . mysqli_error($dbc));
17     exit();
18 }
19
20 $FirstName = mysqli_real_escape_string($dbc, $_GET['FirstName']);
21 $LastName = mysqli_real_escape_string($dbc,$_GET['LastName']);
22 $Street = mysqli_real_escape_string($dbc,$_GET['Street']);
23 $City = mysqli_real_escape_string($dbc,$_GET['City']);
24 $State = mysqli_real_escape_string($dbc,$_GET['State']);
25 $Zip = mysqli_real_escape_string($dbc,$_GET['Zip']);
26 $Email = mysqli_real_escape_string($dbc,$_GET['Email']);
27 $Phone = mysqli_real_escape_string($dbc,$_GET['Phone']);
28
29 $CustomerId = mysqli_real_escape_string($dbc,$_GET['CustomerId']);
30
31 $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'";
32
33 $result = mysqli_query($dbc, $query) or trigger_error("Query MySQL Error: " . mysqli_error($dbc));
34
35 mysqli_close($dbc);
36
37 ?>
38
39 <!-- http://newjustin.com/updatecust.php?FirstName=Sue&LastName=Banas&Street=123&City=Pittsburgh&State=PA&Zip=15222&[email protected]&Phone=4125551212&CustomerId=14
40 -->

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.