CONNECT APP INVENTOR TO MYSQL DATABASE

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

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 

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

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=derek@aol.com&Phone=4125551212&CustomerId=14

40

-->