PhoneGap PHP MySQL Example

phonegap-php-mysql-tutorial

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:

  1. MySQL Database Creation
  2. Writing PHP code for Serverside
  3. Writing Jquery code for PhoneGap / Apache Cordova side

Dependencies: PHP, MySQL, JQuery

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

Working with MySQL database

Let's create a new table called course details where, we can store information about courses such as title, duration, and price.we also need to set primary key & auto increment for id

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; 

After creating a new table, we need to create simple REST API

Working with PHP code

Let's create a simple API (no security layer added) for fetching data from MySQL database & display as JSON.here we're going to create multiple files for insert, delete, update operations. If you want you can merge all files into one single PHP file

Connecting Database Server ( db.php )

This file allows you to connect your MySQL database

<?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

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 ok as 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";
    }
?>

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";
}
?>

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"; 
}
?>

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 Parse JSON using Cordova (PhoneGap)

<?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); 
?>

Connecting JSON with PhoneGap

Insert data using PhoneGap / Apache Cordova ( insert.html )

<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>

Display / Reading JSON data using PhoneGap ( 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

<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>

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

<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;
}

Known mistakes

  • localhost won't work in mobile devices, If you're testing on the device you need to host your file or you need to connect with IP address
  • Make sure you've included geturi.js
  • Download the files and try
  • Still facing any issues ?, Feel free to comment below