Phonegap PHP MySQL Example

In this article, I would like to write an article about PhoneGap with PHP & MySQL. we’ll see how to perform CRUD (Create, Read, Update, Delete) operations with MySQL Database.

Step By Step Guide:

MySQL Database Creation
Writing PHP code for Serverside
Writing Jquery code for PhoneGap / Apache Cordova side

Dependencies: PHP, MySQL, JQuery

Learn More about PHP & MySQL http://www.w3schools.com/php/php_mysql_intro.asp or http://www.tutorialspoint.com/php/php_and_mysql.htm

1. MYSQL DATABASE CREATION

In this example, I’m going to create some basic database & table (course_details) with following fields such as id, title, price.

PhoneGap-MySQL-database

CREATE TABLE `course_details` (
`id` int(1) NOT NULL,
 `title` varchar(25) NOT NULL,
 `duration` varchar(50) NOT NULL,
 `price` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

ALTER TABLE `course_details` ADD PRIMARY KEY (`id`);
ALTER TABLE `course_details` MODIFY `id` int(1) NOT NULL AUTO_INCREMENT;

WRITING PHP CODE (SIMPLE WEBSERVICE)

1. Connecting Database Server (db.php)

<?php
 header("Access-Control-Allow-Origin: *");
 $con = mysqli_connect("localhost","root","root","phonegap_demo") or die ("could not connect database");
?>

NOTE

header("Access-Control-Allow-Origin: *"); use to allow cross domains

2. Insert data into MySQL database using PHP (insert.php)

This code will receive data from the mobile / PhoneGap Apache Cordova using POST method & It’ll insert data to MySQL, If it is successful it will return okas output otherwise error as output

<?php
 include "db.php";
 if(isset($_POST['insert']))
 {
 $title=$_POST['title'];
 $duration=$_POST['duration'];
 $price=$_POST['price'];
 $q=mysqli_query($con,"INSERT INTO `course_details` (`title`,`duration`,`price`) VALUES ('$title','$duration','$price')");
 if($q)
  echo "success";
 else
  echo "error";
 }
 ?>

3.Update Database using PHP (update.php)

This code will receive data from the mobile / PhoneGap / Apache Cordova using POST method & It’ll Update data to MySQL with respect to course_id, If it is successful it will return ok as output otherwise error as output


<?php
 include "db.php";
 if(isset($_POST['update']))
 {
 $id=$_POST['id'];
 $title=$_POST['title'];
 $duration=$_POST['duration'];
 $price=$_POST['price'];
 $q=mysqli_query($con,"UPDATE `course_details` SET `title`='$title',`duration`='$duration',`price`='$price' where `id`='$id'");
 if($q)
 echo "success";
 else
 echo "error";
 }
 ?>

4. Delete data from Database using PHP (delete.php)

This code will receive data from the mobile / PhoneGap / Apache Cordova using POST method & It’ll delete data to MySQL with respect to course_id, If it is successful it will return ok as output otherwise error as output


<?php
 include "db.php";
 if(isset($_GET['id']))
 {
 $id=$_GET['id'];
 $q=mysqli_query($con,"delete from `course_details` where `id`='$id'");
 if($q)
 echo "success";
 else
 echo "error";
 }
 ?>

5. Displaying Data using JSON (json.php)

For displaying data from MySQL database to Phonegap / Apache Cordova, we need to create json based output. we can display JSON data to phonegap using Jquery getJSON or AJAX method. Read more https://phonegappro.com/tutorials/how-to-parse-read-json-using-phonegap-apache-cordova/


<?php
include "db.php";
$data=array();
$q=mysqli_query($con,"select * from `course_details`");
while ($row=mysqli_fetch_object($q)){
 $data[]=$row;
}
echo json_encode($data);
?>

Writing Jquery code for PhoneGap / Apache Cordova Side

1. Insert data using Phonegap / Apache Cordova (insert.html)

insert data to mysql phonegap — insert data to MySQL PhoneGap


<html>
<head>
 <link rel="stylesheet" type="text/css" href="css/ionic.css">
 <script type="text/javascript" src="js/jquery.js"></script>
 <script type="text/javascript">
 $(document).ready(function()
 {
 $("#insert").click(function(){
 var title=$("#title").val();
 var duration=$("#duration").val();
 var price=$("#price").val();
 var dataString="title="+title+"&duration="+duration+"&price="+price+"&insert=";
 if($.trim(title).length>0 & $.trim(duration).length>0 & $.trim(price).length>0)
 {
 $.ajax({
 type: "POST",
 url:"http://localhost/phonegap/database/insert.php",
 data: dataString,
 crossDomain: true,
 cache: false,
 beforeSend: function(){ $("#insert").val('Connecting...');},
 success: function(data){
 if(data=="success")
 {
 alert("inserted");
 $("#insert").val('submit');
 }
 else if(data=="error")
 {
 alert("error");
 }
 }
 });
 }return false;
 });
 });
 </script>
</head>
<body>
 <div class="bar bar-header bar-positive" style="margin-bottom:80px;">
 <h1 class="title">Insert Database</h1>
 <a class="button button-clear" href="readjson.html">Read JSON</a>
 </div><br/><br/>
 <div class="list">
 <input type="hidden" id="id" value=""/>
 <div class="item">
 <label>Title</label>
 <input type="text" id="title" value=""/>
 </div>
 <div class="item">
 <label>Duration</label>
 <input type="text" id="duration" value=""/>
 </div>
 <div class="item">
 <label>Price</label>
 <input type="text" id="price" value=""/>
 </div>
 <div class="item">
 <input type="button" id="insert" class="button button-block" value="Insert"/>
 </div>
 </div>
</body>
</html>

2. Display / Reading JSON data using PhoneGap / Apache Cordova (readjson.html)

It’ll display list the data from the server as a link. when you click the link, it’ll redirect you to form.html. you can update data from form.html

read data from mysql phonegap — read data from MySQL PhoneGap


<html>
<head>
 <link rel="stylesheet" type="text/css" href="css/ionic.css">
 <script type="text/javascript" src="js/jquery.js"></script>
 <script type="text/javascript">
 $(document).ready(function()
 {
 var url="http://localhost/phonegap/database/json.php";
 $.getJSON(url,function(result){
 console.log(result);
 $.each(result, function(i, field){
 var id=field.id;
 var title=field.title;
 var duration=field.duration;
 var price=field.price;
 $("#listview").append("<a class='item' href='form.html?id="+id+"&title="+title+"&duration="+duration+"&price="+price+"'><span class='item-note'>$"+price+"</span><h2>"+ title + " </h2><p>"+ duration +"</p></a>");
 });
 });
 });
 </script>
</head>
<body>
 <div class="bar bar-header bar-positive" style="margin-bottom:80px;">
 <a href="index.html" class="button button-clear">Home</a>
 <h1 class="title">Read Database (JSON)</h1>
 </div><br/><br/>
 <ul class="list" id="listview">
 </ul>
</body>
</html>

3. Updating MySQL database from Phonegap / Apache Cordova (form.html)

This example shows how to update data and delete data from database using Phonegap / Apache Cordova

upddate data from mysql phonegap — update data from mysql phonegap

update data from mysql phonegap — update data from MySQL PhoneGap


<html>
<head>
 <link rel="stylesheet" type="text/css" href="css/ionic.css">
 <script type="text/javascript" src="js/jquery.js"></script>
 <script type="text/javascript" src="js/geturi.js"></script>
 <script type="text/javascript">
 $(document).ready(function(){
 var id = decodeURI(getUrlVars()["id"]);
 var title = decodeURI(getUrlVars()["title"]);
 var duration = decodeURI(getUrlVars()["duration"]);
 var price = decodeURI(getUrlVars()["price"]);
 $("#id").val(id);
 $("#title").val(title);
 $("#duration").val(duration);
 $("#price").val(price);
 $("#update").click(function(){
 var id=$("#id").val();
 var title=$("#title").val();
 var duration=$("#duration").val();
 var price=$("#price").val();
 var dataString="id="+id+"&title="+title+"&duration="+duration+"&price="+price+"&update=";
 $.ajax({
 type: "POST",
 url:"http://localhost/phonegap/database/update.php",
 data: dataString,
 crossDomain: true,
 cache: false,
 beforeSend: function(){ $("#update").val('Connecting...');},
 success: function(data){
 if(data=="success")
 {
 alert("Updated");
 $("#update").val("Update");
 }
 else if(data=="error")
 {
 alert("error");
 }
 }
 });
 
 });
 $("#delete").click(function(){
 var id=$("#id").val();
 var dataString="id="+id+"&delete=";
 $.ajax({
 type: "GET",
 url:"http://localhost/phonegap/database/delete.php",
 data: dataString,
 crossDomain: true,
 cache: false,
 beforeSend: function(){ $("#delete").val('Connecting...');},
 success: function(data){
 if(data=="success")
 {
 alert("Deleted");
 $("#delete").val("Delete");
 }
 else if(data=="error")
 {
 alert("error");
 }
 }
 });
 
 });
 });
 </script>
</head>
<body>
 <div class="bar bar-header bar-positive" style="margin-bottom:80px;">
 <a href="index.html" class="button button-clear">Home</a>
 <h1 class="title">Update Database</h1>
 <a class="button button-clear" href="readjson.html">Read JSON</a>
 </div><br/><br/>
 <div class="list">
 <input type="hidden" id="id" value=""/>
 <div class="item">
 <label>Title</label>
 <input type="text" id="title" value=""/>
 </div>
 <div class="item">
 <label>Duration</label>
 <input type="text" id="duration" value=""/>
 </div>
 <div class="item">
 <label>Price</label>
 <input type="text" id="price" value=""/>
 </div>
 <div class="item">
 <input type="button" id="update" class="button button-block" value="Update"/>
 <input type="button" id="delete" class="button button-block" value="Delete"/>
 </div>
 </div>
 
</body>
</html>

This is all about PhoneGap PHP MySQL example for performing CRUD Operation. If you’ve any doubts feel free to comment below

geturi.js

this code is used for getting value from URL.like site.com/file?

E.g http://localhost/file.html?name=sundar&country=india

if you want to get name and URL, you can use like this

var name = gerUrlVars()[“name”];

var country = gerUrlVars()[“country”];


function getUrlVars() {
 var vars = [], hash;
 var hashes = window.location.href.slice(window.location.href.indexOf('?') + 1).split('&');
 for (var i = 0; i < hashes.length; i++) {
 hash = hashes[i].split('=');
 vars.push(hash[0]);
 vars[hash[0]] = hash[1];
 }
 return vars;
}

Shikha Pathak

Phonegap PHP MySQL Example | Apache Cordova