Connecting Android with MySQL,PHP
I'm going to show you how simple Android app will call php script to perform basic operation(insert,view,delete and update).First android app calls a PHP script in order to perform an operation. PHP script then connect to MySQL database to perform the operation.data flow is
Android app -> php script -> mysql
1.What is XAMPP server
XAMPP server provides an environment to develop PHP,MySQL web application.By installing this software you will be installing the Apache,PHP and MySQL.Ones you install the xampp server You can test your server by opening the address localhost/ in your browser.Also you can check phpmyadmin by opening localhost/phpmyadmin
2.Run a php script
Now environment is fixed to develop the project.You have to create php script inside the xampp folder where you install the xampp server(In my case D:\xampp)and go to the htdoc. Create folder Android_connect and include all the php script inside that folder to run.To check the output localhost/android_connect/example.php
3.Creating MySQL database
4.Connecting MySQL database Using PHP
Open the connection to database and close connection when not needed have to have two php script.
db_config_mdl,db_connect_mdl
References
5.Basic operation
5.a).View data in a List View
Create a new php file called mdl_getall_subjects.php and write the following code. This file will get all the enroll subjects details by taking user id as post parameter.
Create a new php file called mdl_getall_subjects.php and write the following code. This file will get all the enroll subjects details by taking user id as post parameter.
<?php
// array for JSON response
$response = array();
// include db connect class
require_once __DIR__ . '/db_connect_mdl.php';
// connecting to db
$db = new DB_CONNECT();
if (isset($_GET["userid"])) {
$userid =$_GET['userid'];
$result = mysql_query("select c.id as id,c.fullname as subject from mdl_course c where c.id IN(select e.courseid as courseid from mdl_enrol e,mdl_user_enrolments ue where ue.userid='$userid' and e.id=ue.enrolid and e.enrol='self')") or die(mysql_error());
// check for empty result
if (mysql_num_rows($result) > 0) {
// looping through all results
// products node
$response["subjects"] = array();
while ($row = mysql_fetch_array($result)) {
$subject = array();
// temp user array
$subject["id"] = $row["id"];
$subject["subject"] = $row["subject"];
// push single product into final response array
array_push($response["subjects"], $subject);
}
// success
$response["success"] = 1;
// echoing JSON response
echo json_encode($response);
} else {
// no products found
$response["success"] = 0;
$response["message"] = "No courses found";
// echo no users JSON
echo json_encode($response);
}
}
else{
echo "testing this";
}
?>
5.b).Create data in a List View
Create a new php file called mdl_create_reply.php and write the following code. This file will reply forum by taking postid, subjects,message and username as post parameter.
<?php
// array for JSON response
$response = array();
// check for required fields
if (isset($_POST['id']) && isset($_POST['subject']) && isset($_POST['message']) && isset($_POST['username']) ) {
$id = $_POST['id'];
$subject = $_POST['subject'];
$message = $_POST['message'];
$username=$_POST['username'];
// include db connect class
require_once __DIR__ . '/db_connect_mdl.php';
// connecting to db
$db = new DB_CONNECT();
$get_username=mysql_query("select id from mdl_user where username='$username'");
if (mysql_num_rows($get_username) > 0) {
// looping through all results
$userid=null;
while ($row = mysql_fetch_array($get_username)) {
$userid = $row["id"];
}
}
//________________________________________________________________________________________________________________________________________________________
//$result_mdldiscussion=mysql_query("INSERT INTO mdl_forum_discussions(id,course,forum,name,firstpost,userid) VALUES('','$course','$forum_id','$subject',5,'$userid')");
//$select_fdid=mysql_query("select fd.id from mdl_forum_discussions fd");
// $select_fpid=mysql_query("select fp.id from mdl_forum_posts fp where fp.parent='$select_fdid' and fp.subject='$subject' ");
$select_fdid=mysql_query("select fd.id from mdl_forum_posts fp,mdl_forum_discussions fd where fd.id=fp.discussion and fp.subject='$subject' ");
// $select_fpid=mysql_query("select fp.id from mdl_forum_posts fp where fp.parent='$id'");
$select_ftime=mysql_query("select created from mdl_forum_posts fp where fp.subject='$subject' ");
$fdid = array();
$fpid = array();
if (mysql_num_rows($select_fdid) > 0) {
// looping through all results
while ($row = mysql_fetch_array($select_fdid)) {
$fdid["id"] = $row["id"];
}
}
$discussion_id=$fdid["id"];
//$fdid = array();
if (mysql_num_rows($select_fpid) > 0) {
// looping through all results
while ($row = mysql_fetch_array($select_fpid)) {
$fpid["id"] = $row["id"];
}
}
$post_id=$fpid["id"];
// mysql inserting a new row
date_default_timezone_set('Asia/Colombo');
//$serverTime = date('Y-m-d H:i:s', time());
$serverTime = time();
$result = mysql_query("INSERT INTO mdl_forum_posts(id,discussion,parent,userid,created,modified,message,subject) VALUES('','$discussion_id','$id','$userid','$serverTime','$serverTime','$message', '$subject')");
// check if row inserted or not
if ($result) {
// successfully inserted into database
$response["success"] = 1;
$response["message"] = "Post successfully created.";
// echoing JSON response
echo json_encode($response);
} else {
// failed to insert row
$response["success"] = 0;
$response["message"] = "Oops! An error occurred.";
// echoing JSON response
echo json_encode($response);
}
} else {
// required field is missing
$response["success"] = 0;
$response["message"] = "Required field(s) is missing";
// echoing JSON response
echo json_encode($response);
}
?>
Create a new php file called mdl_create_reply.php and write the following code. This file will reply forum by taking postid, subjects,message and username as post parameter.
<?php
// array for JSON response
$response = array();
// check for required fields
if (isset($_POST['id']) && isset($_POST['subject']) && isset($_POST['message']) && isset($_POST['username']) ) {
$id = $_POST['id'];
$subject = $_POST['subject'];
$message = $_POST['message'];
$username=$_POST['username'];
// include db connect class
require_once __DIR__ . '/db_connect_mdl.php';
// connecting to db
$db = new DB_CONNECT();
$get_username=mysql_query("select id from mdl_user where username='$username'");
if (mysql_num_rows($get_username) > 0) {
// looping through all results
$userid=null;
while ($row = mysql_fetch_array($get_username)) {
$userid = $row["id"];
}
}
//________________________________________________________________________________________________________________________________________________________
//$result_mdldiscussion=mysql_query("INSERT INTO mdl_forum_discussions(id,course,forum,name,firstpost,userid) VALUES('','$course','$forum_id','$subject',5,'$userid')");
//$select_fdid=mysql_query("select fd.id from mdl_forum_discussions fd");
// $select_fpid=mysql_query("select fp.id from mdl_forum_posts fp where fp.parent='$select_fdid' and fp.subject='$subject' ");
$select_fdid=mysql_query("select fd.id from mdl_forum_posts fp,mdl_forum_discussions fd where fd.id=fp.discussion and fp.subject='$subject' ");
// $select_fpid=mysql_query("select fp.id from mdl_forum_posts fp where fp.parent='$id'");
$select_ftime=mysql_query("select created from mdl_forum_posts fp where fp.subject='$subject' ");
$fdid = array();
$fpid = array();
if (mysql_num_rows($select_fdid) > 0) {
// looping through all results
while ($row = mysql_fetch_array($select_fdid)) {
$fdid["id"] = $row["id"];
}
}
$discussion_id=$fdid["id"];
//$fdid = array();
if (mysql_num_rows($select_fpid) > 0) {
// looping through all results
while ($row = mysql_fetch_array($select_fpid)) {
$fpid["id"] = $row["id"];
}
}
$post_id=$fpid["id"];
// mysql inserting a new row
date_default_timezone_set('Asia/Colombo');
//$serverTime = date('Y-m-d H:i:s', time());
$serverTime = time();
$result = mysql_query("INSERT INTO mdl_forum_posts(id,discussion,parent,userid,created,modified,message,subject) VALUES('','$discussion_id','$id','$userid','$serverTime','$serverTime','$message', '$subject')");
// check if row inserted or not
if ($result) {
// successfully inserted into database
$response["success"] = 1;
$response["message"] = "Post successfully created.";
// echoing JSON response
echo json_encode($response);
} else {
// failed to insert row
$response["success"] = 0;
$response["message"] = "Oops! An error occurred.";
// echoing JSON response
echo json_encode($response);
}
} else {
// required field is missing
$response["success"] = 0;
$response["message"] = "Required field(s) is missing";
// echoing JSON response
echo json_encode($response);
}
?>
5.b).Delete data in a List View
Create a new php file called mdl_delete_forum_reply.php and write the following code. This file will delete reply forum by taking postid, userid as get parameter.
<?php
// array for JSON response
$response = array();
// include db connect class
require_once __DIR__ . '/db_connect_mdl.php';
// connecting to db
$db = new DB_CONNECT();
if (isset($_GET["id"])&& isset($_GET["userid"]) ) {
$id=$_GET['id'];
$userid=$_GET['userid'];
$delete_reply = mysql_query("DELETE FROM mdl_forum_posts where id='$id' AND userid='$userid'") or die(mysql_error());
$result = mysql_query($query) or die("Unable to Delete forum reply : ". mysql_error());
}
?>
