json data from mysql

[Kirsten_K_]

I am about to make my first hybrid application and I want to host it on my windows server and get access to the data on my MySqlServer.

The MySqlServer has opened for the IP of my Windows server to conntect to the database and get data.

In my application I want to get json data from a asp.net file that is positioned in the same folder as the application.  The asp.net file is collecting the data from the mySql database.

If I understand it right, when I want to test my application on Intel XDK, I am pushing my application on the test server of Intel XDK. From the test server of Intel XDK I cannot access my MySqlServer because it does not accept the IP address of Intel XDK test server. Is it possible to test my application while the files are on my Windows Server?

[Nick_F_2]

Yes definitely, I have a number of PHP scripts and ASP.NET scripts that are used to access data from a MySQL database and returned as JSON data. They work fine in the emulator and through App Preview.

[Kirsten_K_]

Hi Nick

Thank you very much for replaying.
Did you install intel XDK directly on your server and work direktly on your server when you are developing?

[Nick_F_2 · Accepted solution]

No XDK installs on your local PC or MAC, this is the development environment.

Your server will host both the MySQL database and the PHP or ASP.NET scripts. I use Visual Studio 2010 for the ASP.NET scripts and NetBeans for my PHP scripts.

You call the scripts through javascript in XDK, the result is then returned as a JSON encoded array which you then parse in Javascript to display within your XDK app as a table or list or other suitable layout..

[Kirsten_K_]

Hi Nick

Thank you very much for your answer. It is really helpful.

This setup is very new for me, so therefor I have some additional questions.
Until now all my applications were in asp.net. When I make a database request for a logged in user, the query will not even go on, if the user have not got a valid session variable that proves he is logged in.

If I have to make a json request from a development environment javascript file to the asp.net script on the server, the asp.net script has no valid session variable that secures me that the request comes from a genuine user.

How does security goes?

[Nick_F_2]

Hi

Here is a simple method using PHP but can easily be used with an ASP.NET web service.

In XDK create a javascript function like this, I take a value from some inputs with the ID username and password, note I also store this info in localStorage items for later use. This is how I preserve data that also persists after an App restart. The returned result is displayed in HTML. This line of code writes the html to a DIV with the ID myuser. $(html).appendTo("#myuser"). Note how I parse the JSON data from the jsonArray with each column name from the selection in PHP:-

function getUser() {
    $("#myuser").empty();
    var myuser  = $("#username").val();
    var mypassword = $("#password").val();

    var sqlstr = "Your URL/getUser.php?user="+myuser;

    var xhr = new XMLHttpRequest();

    xhr.open("GET",sqlstr,false);

    xhr.onload = function(){

        if(xhr.status==200)

        {

        jsonArray = $.parseJSON(xhr.responseText);

                      var html = "<table width='100%' border='0'><tbody>"

                      html += "<tr><td width='120px' align='right' valign='middle'>Your Details</td><td align='left' valign='middle'><div class='table-thing with-label widget uib_w_60 d-margins SelectWidth' data-uib='app_framework/select' data-ver='1'>";

             for(i=0; i < jsonArray.length; i++)

                       {

                       user = jsonArray.username;

                       pass = jsonArray.password;

                       localStorage.setItem("myusername", user);

                       localStorage.setItem("my password", pass);

                       html += "<tr><td><input value='"+user+"'></td><td><input value='"+pass+"'></td></tr>";

                       }

            html += "</tbody></table>";

            $(html).appendTo("#myuser");

        }

        else if(xhr.status == 404)

    {

        navigator.notification.alert("Web Service Doesn't Exist", "Error","OK");

    }

    else

    {

       navigator.notification.alert("Unknown error occured while connecting to server", "Error","OK");

    }

}

}

xhr.send()

}
 

You will see that xhr calls the PHP page getUser.php?user="+myuser that returns the result as JSON data. I have hidden my SQL server settings in the code below.

<?php

$User = $_GET['user'];

$DB_NAME = "*****";

$DB_USER = "******";

$DB_PASSWORD = "******";

$DB_HOST = "******";

$db_handle = mysql_connect($DB_HOST,$DB_USER,$DB_PASSWORD)

or die("Unable to connect to MySQL");

$db_found = mysql_select_db($DB_NAME, $db_handle);

$SQL = "SELECT username, password FROM users WHERE username= $User";  

$result = mysql_query($SQL);

$rows = array();

while($r = mysql_fetch_assoc($result)) {

$rows[] = $r;

}

echo json_encode($rows);

Hope that helps. Apologies for any coding errors, this may not run as written.

[Kirsten_K_]

Hi Nick

Thanks very much for your code. I have looked at it and I will be able to use part of the idea.

I think that I have to use express-jwt and jsonwebtoken  to secure the serverside like I read on this blog:
https://auth0.com/blog/2014/01/07/angularjs-authentication-with-cookies-vs-token/

Thanks for your help 

[Krishna_A_]

Hello Friends,

I am new to mobile and web development, so please give me a working example of Web API or web service to retrieve and insert data on SQL and MySQL server using Intel XDK for mobile applications.

How to write, deploy Self Developed Web API or Web Service and consume those service for my mobile app.

Thanks to you in advance you can contact me 20krish@gmail.com

[Krishna_A_]

Nick F. wrote:

No XDK installs on your local PC or MAC, this is the development environment.

Your server will host both the MySQL database and the PHP or ASP.NET scripts. I use Visual Studio 2010 for the ASP.NET scripts and NetBeans for my PHP scripts.

You call the scripts through javascript in XDK, the result is then returned as a JSON encoded array which you then parse in Javascript to display within your XDK app as a table or list or other suitable layout..

Hello Nick,

I think you can definitely troubleshoot my problem, Please help me bro...

I am new to mobile and web development, so please give me a working example of Web API or web service to retrieve and insert data on SQL and MySQL server using Intel XDK for mobile applications.

How to write, deploy Self Developed Web API or Web Service and consume those service for my mobile app.

Thanks to you in advance you can contact me 20krish@gmail.com

[Nick_F_2]

Hi

ASP.NET or PHP.

[Nick_F_2]

Here is a PHP script called getCol.php

<?php
$DB_NAME = "myDB";
$DB_USER = "DBUsername";
$DB_PASSWORD = "DBPassword";
$DB_HOST = "xxx.xxx.xxx.xxx";
$db_handle = mysql_connect($DB_HOST,$DB_USER,$DB_PASSWORD)
or die("Unable to connect to MySQL");
$db_found = mysql_select_db($DB_NAME, $db_handle);
$SQL = "SELECT col1, col2, col3 FROM myDB ORDER BY col1";
$result = mysql_query($SQL);
$rows = array();
while($r = mysql_fetch_assoc($result)) {
$rows[] = $r;
}
echo json_encode($rows);

Here is the Javascript to call and parse the JSON result. myDiv is a named div on your XDK page that is used to display the data in whatever format you choose. I have used a table but you could use a ListView or Dropdown etc. myDiv is called at the beginning of the function to make sure it is empty before parsing and displaying the new data.

function getMon() {
    $("#myDiv").empty();
    var xhr = new XMLHttpRequest();
    xhr.open("GET","http://myDomain/getCol.php",false);
    xhr.onload = function(){
        if(xhr.status==200)
        {
        jsonArray = $.parseJSON(xhr.responseText);
                      var html = "<table align='center' width='99%' cellpadding='4' border='0' style='margin-left: 2px;color: black; font-size: 10pt; border-collapse: collapse;'><thead><tr></tr></thead><tbody>";
             for(i=0; i < jsonArray.length; i++)
                       {
                       col1=jsonArray.col1;
                       col2 = jsonArray.col2;
                       col3 = jsonArray.col3;
                       html += "<tr><td align='left'><font size='+1'><b>"+col1+"</b><td><b>"+col2+"</b></font></td></tr><tr><td>"+col3+"</td><td>"+col3+"</a></td></tr>";
                       }
            
            html += "</table>";
            $(html).appendTo("#myDiv");
        }
        else if(xhr.status == 404)
    {
        navigator.notification.alert("Web Service Doesn't Exist", "Error","OK");
    }
    else
    {
       navigator.notification.alert("Unknown error occured while connecting to server", "Error","OK");
    }
}
xhr.send();
}

[Nick_F_2]

Here is an ASP.NET Web Service example. Obviously you will need to create and store your connection string for your database.

 <WebMethod()> _
    Public Function getCols(byval col1 as string) As String
        Try
            sql = "SELECT col1,col2 FROM myTable WHERE col1 = '" & col1  "' ORDER BY col1;"
            mycommand.Connection = myconn
            If myconn.State <> ConnectionState.Open Then
                myconn.Open()
            End If
            da = New MySqlDataAdapter(sql, myconn)
            da.Fill(ds)
            myconn.Close()
            Dim jsonData As String = GetJson(ds.Tables(0))
            Return jsonData
        Catch ex As Exception
            myconn.Close()
            Return False
        End Try
    End Function

Below is where I get JSON data from my DataTable

Public Shared Function GetJson(ByVal dt As DataTable) As String
        Return New JavaScriptSerializer().Serialize(From dr As DataRow In dt.Rows Select dt.Columns.Cast(Of DataColumn)().ToDictionary(Function(col) col.ColumnName, Function(col) dr(col)))
    End Function

Here is the Javascript to call the web service and display the data

function getCols() {
    $.ajax({
        type: "POST",
        url: "http://webservice.mydomain.co.uk/service1.asmx/getCols",
        data: "{'col1':'" + localStorage.getItem("col1") + "'}",
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        async: true,
        crossDomain: true,
        success: function(json){
        jsonArray = $.parseJSON(json.d);
            $("#storeoptions").empty();
                       var html = "<option>[Select]</option>";
             for(i=0; i < jsonArray.length; i++)
                       {
                       rol1 = jsonArray.col1;
                       col2 = jsonArray.col2;
                       html += "<option value='"+col1+"'>"+col2+"</option>";
                       }
           //console.log(html);
            $('#mycols').html(html);
            $.mobile.changePage("#uib_page_1", {transition: "slide"});
        },
        error: function(msg) {
        navigator.notification.alert("Unable to retrieve Cols:" + msg.d);
        }
    });
return false;
}

[Krishna_A_]

Thank you very much Nick, You are awesome.

I am trying with ASP, i will let you know if i will done.

if you can send me a project/code then it will help me.

[Nick_F_2]

I can't send any more other than the example I have given. 

The implementation is the same whatever you are trying to return, just associate the  jsonArray.<columname> with a variable or display in your HTML code.

If you run your web service within Visual Studio, you can manually post the data to see the resulting JSON is correctly formatted. You can capture the data your are POSTING from XDK by inserting a breakpoint or writing to the console the output from your App, you can then use this data to manually feed your web service for testing.

Just make sure the variables your web service is expecting are sent correctly from the app.

These lines:-

 type: "POST",
        url: "http://webservice.mydomain.co.uk/service1.asmx/getCols",
        data: "{'col1':'" + localStorage.getItem("col1") + "'}", 

shows posting a value col1 contains the data localStorage.getItem("col1") to the web service getCols(byval col1 as string).

[brian_k_]

Hello Mr.Nick,needs some help,

Need to enter some few data in my mysql:That is username and password!

How can you help me kindly

thanks