B4J Question connect to remote database using an API (SOLVED)

[Colin Evans]

Hi I haven't used B4J yet but I need to create a program that will connect to a database held on my web server, I have been in contact with the tech support and they state I must have an API to use the data held in the database.

The data is updated via a third party app , I know my server name being my web address, and I know the database name used by the app and user name and password

database server name: localhostL3306 or my web address
Database name: rmosfdov_
User name: colinsql
Password: ********

I've tried to find examples in the forum but I'm not having much look,

I assume I need to create an API which in turn will return the information in a JSON format

The table contains five fields

Username
telephone
date and time
email address
identifier

Any advice on where to start would be greatly appreciated

 

[Colin Evans]

Thanks for that but I still haven't got anything displayed from thew GetAllPersons which each subroutine calls

 

[aeric]

You should show what you have updated. You have changed the code and database and then only provide partly of the updated source. It is difficult for us to help. You also need to learn about SQL. Make sure all your table and columns are correctly defined. For example pid vs ID.

I recommend you try set up a local server for development. Try Laragon and connect to 127.0.0.1 with your PHP using web browser. You can also check the result returned in B4J log.

 

[Colin Evans]

Cheers, I'll have a go, I think I'm confusing myself using examples and trying to modify them

 

[DonManfred]

exit (json_encode(array(array('InsertPerson' => 'failed'))));

You are not using exit correctly. exit is expected to return a resultcode (Integer) which is interpreted as a ResultCode from the calling Application (in this case Apache).

If you want to return a result first you

- should register a shutdown function and return the json here. See the page

PHP: exit - Manual

www.php.net

- should ECHO the json just before the exit and then use exit(0); for a successfully exit of your php. If not successfull then return any negative integer.

To make it sinple for you i suggest to use the second variant.

Change

exit (json_encode(array(array('InsertPerson' => 'failed'))));

to

echo json_encode(array(array('InsertPerson' => 'failed')));
exit(-1);

Here a adapted phpcode based on the code you posted in this thread.

<?php

    $host = "localhost";
    $user = "colin";
    $pw = "CmA112209";
    $db = "DITLLogin";


    $con = mysqli_connect($host,$user,$pw) or die(mysqli_error());
    mysqli_select_db($con,$db) or die(mysqli_error());
    mysqli_query($con,"SET CHARACTER SET utf8");
    mysqli_query($con,"SET NAMES 'utf8'");

  
    $json = file_get_contents("php://input");
    $jsall = array();
    $jsone = array();
    $jsall=json_decode($json, true);
    $jsone=$jsall[0];
  
    $action = $jsone["Action"];
  
    switch ($action)
    {
  
    Case "InsertPerson":
        $pname=stripslashes(mysqli_real_escape_string($con,$jsone["name"]));
        $pphone=stripslashes(mysqli_real_escape_string($con,$jsone["phone"]));
        $pparty=stripslashes(mysqli_real_escape_string($con,$jsone["party"]));
      
        $stmt = $con->prepare("INSERT INTO persons (pname, pphone, pparty) VALUES (?, ?, ?)");
        $rc=$stmt->bind_param("sss", $pname, $pphone, $pparty);
        $rc=$stmt->execute();
        $iid=$stmt->insert_id;
        if ($iid == 0)
        {
            echo json_encode(array(array('InsertPerson' => 'failed')));
            exit (-1);
        }
        else
        {
            echo json_encode(array(array('InsertPerson' => 'ok', 'pid' =>  $iid)));
            exit (0);
        }
      
        break;
  
    Case "DeletePerson":
        $pid=stripslashes(mysqli_real_escape_string($con,$jsone["pid"]));
      
        $stmt = $con->prepare("DELETE FROM persons WHERE pid = ?");
        $rc=$stmt->bind_param("i", $pid);
        $rc=$stmt->execute();
        $ar=$stmt->affected_rows;
        if ($ar < 1)
        {
            echo json_encode(array(array('DeletePerson' => 'deletenorow')));
            exit(-2);
        }
        else
        {
            echo json_encode(array(array('DeletePerson' => 'deleteok'))); 
            exit(0);
        }
      
        break;
  
    Case "GetAllPersons":
        $stmt = $con->prepare("SELECT * from login ORDER BY pid ASC");
        //$rc=$stmt->bind_param("s", $uname);
        $rc=$stmt->execute();
        $q = $stmt->get_result();
        $count=mysqli_num_rows($q);
        $stmt->close();

        $rows = array();
         while($r = mysqli_fetch_assoc($q))
         {
             $rows[] = $r;
         }
        //exit (json_encode(array(array('persons' => 'ok', 'personlist' => json_encode($rows)))));
        echo json_encode($rows);
        exit(0);
      
        break;
      
    default:
        print json_encode ("Error: Function not defined (" . $action . ")");
    }   
?>

Best is not to use exit at all.

 

[Colin Evans]

Hi DonManfred, many thanks for the input, I believe I am making the connect to the PHP file but I'm still not getting anything displayed in the table, as Aeric stated I need to learn a lot more about the various components, although it's a bewildering landscape out there and too much to take in, it may take a while...

I attach the project as it stands now, is there any way to see if the GetAllPersons returns any info, I have logged the result but only shows '(ArrayList) []' if you or anyone as a minute to inform me where I'm still going wrong and just return the info contained in the small database, I would be extremely grateful

Until then I will try and learn more when the time permits

Many thanks to all, I have set my PHP to the above so graciously donated by DonManfred

 

[DonManfred]

Am i able to connect to your DB from my Server if i would setup a Test there?
Is your Database accessible from other places than localhost?

 

[Colin Evans]

To lessen my complication I've removed input and delete's from the equation, as all I really want is to display the info held in the database, so the php code is

<code>
<?php

$host = "localhost:3306";
$user = "colin";
$pw = "CmA112209";
$db = "DITLLogin";

$con = mysqli_connect($host,$user,$pw) or die(mysqli_error());
mysqli_select_db($con,$db) or die(mysqli_error());
mysqli_query($con,"SET CHARACTER SET utf8");
mysqli_query($con,"SET NAMES 'utf8'");

$json = file_get_contents("php://input");
$jsall = array();
$jsone = array();
$jsall=json_decode($json, true);
$jsone=$jsall[0];

$action = $jsone["Action"];
switch ($action)

{


Case "GetAllPersons":
$stmt = $con->prepare("SELECT * from login ORDER BY pid ASC");
//$rc=$stmt->bind_param("s", $uname);
$rc=$stmt->execute();
$q = $stmt->get_result();
$count=mysqli_num_rows($q);
$stmt->close();

$rows = array();
while($r = mysqli_fetch_assoc($q))
{
$rows[] = $r;
}
//exit (json_encode(array(array('persons' => 'ok', 'personlist' => json_encode($rows)))));
echo json_encode($rows);
exit(0);

break;

default:
print json_encode ("Error: Function not defined (" . $action . ")");
}
?>
</code>

and the attached program code also as the input delete removed.

Is there anyway of receiving the JSON data outside of the program to see what if anything is being sent back on exit

 

[aeric]

$stmt = $con->prepare("SELECT * from login ORDER BY pid ASC");

As I said, check your column names. From what I see from the table schema in phpMyAdmin image you posted, you have ID but not pID. Change to following code:

$stmt = $con->prepare("SELECT * from login ORDER BY ID");

 

[Colin Evans]

Hi, attached the connection info as supplied by the database utility on the server, hope that helps

 

[Colin Evans]

I can imagine it's very frustrating to all concerned, I've made the changes as suggested so this should be the basic one to just return the info held in the database

<code>
<?php

$host = "localhost:3306";
$user = "colin";
$pw = "CmA112209";
$db = "DITLLogin";

$con = mysqli_connect($host,$user,$pw) or die(mysqli_error());
mysqli_select_db($con,$db) or die(mysqli_error());
mysqli_query($con,"SET CHARACTER SET utf8");
mysqli_query($con,"SET NAMES 'utf8'");

$json = file_get_contents("php://input");
$jsall = array();
$jsone = array();
$jsall=json_decode($json, true);
$jsone=$jsall[0];

$action = $jsone["Action"];
switch ($action)

{


Case "GetAllPersons":
$stmt = $con->prepare("SELECT * from login ORDER BY ID ASC");
//$rc=$stmt->bind_param("s", $uname);
$rc=$stmt->execute();
$q = $stmt->get_result();
$count=mysqli_num_rows($q);
$stmt->close();

$rows = array();
while($r = mysqli_fetch_assoc($q))
{
$rows[] = $r;
}
//exit (json_encode(array(array('persons' => 'ok', 'personlist' => json_encode($rows)))));
echo json_encode($rows);
exit(0);

break;

default:
print json_encode ("Error: Function not defined (" . $action . ")");
}
?>
</code>

 

[aeric]

Please show your login table structure.

 

[Colin Evans]

I believe it's shown in post 17, thanks

 

[aeric]

Your PHP seems fine. If the structure also correct, please check the database username and password are correct and has all required permissions.

 

[Colin Evans]

Yes User name and password are correct, if the PHP is correct is there any way of seeing the returned data so I can make sure the program code is correct

 

[aeric]

Yes User name and password are correct, if the PHP is correct is there any way of seeing the returned data so I can make sure the program code is correct

Since you have added log to some return values in B4J, you should see them in the IDE.

 

[Colin Evans]

Wow, finally got it working, many many thanks to all involved, learned a lot but still way off being a basic user, but will keep trying, thanks again

 

[aeric]

Wow, finally got it working, many many thanks to all involved, learned a lot but still way off being a basic user, but will keep trying, thanks again

So, what was the mistake? Mind to share?

 

[Colin Evans]

What you said some time ago, down to variable names, I was using pid, pname etc instead of ID, Name etc in the application code

<code>
Row(0)=m.Get("ID")
Row(1)=m.Get("_sfm_form_submision_time_")
Row(2)=m.Get("Name")
Row(3)=m.Get("Phone")
Row(4)=m.Get("Party")
Row(5)=m.Get("Email")
</code>

Thanks again, I've learnt something new, will start to expand on the code now I've got the basics, so will probably return with more questions, many thanks Colin

 

[TILogistic]

There are various ways to make JSON or XML data requests, I have seen various ways in the community.

But the simplest way is to use a RESTfull API framework.

In PHP there are various RESTFull API frameworks.

I am preparing a tutorial on how to use a PHP RESTfull API framework with B4X.

Example:

Slim Framework

Slim is a PHP micro framework that helps you quickly write simple yet powerful web applications and APIs.

www.slimframework.com

Regards

 

[Colin Evans]

look forward to the tutorial