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

Colin Evans

Active Member
Licensed User
Longtime User
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
 
Sort by date Sort by votes

Colin Evans

Active Member
Licensed User
Longtime User
Thanks for that but I still haven't got anything displayed from thew GetAllPersons which each subroutine calls
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
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.
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
Colin Evans said:
exit (json_encode(array(array('InsertPerson' => 'failed'))));
Click to expand...
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

PHP is a popular general-purpose scripting language that powers everything from your blog to the most popular websites in the world.
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
PHP: 
exit (json_encode(array(array('InsertPerson' => 'failed'))));
to

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

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

PHP: 
<?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.
 
Last edited:
Upvote 0

Colin Evans

Active Member
Licensed User
Longtime User
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
 

Attachments

  • PHPMyattempt.zip
    4.3 KB · Views: 205
Upvote 0

Colin Evans

Active Member
Licensed User
Longtime User
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
 

Attachments

  • PHPMyattempt.zip
    3.9 KB · Views: 204
Upvote 0

aeric

Expert
Licensed User
Longtime User
Colin Evans said:
$stmt = $con->prepare("SELECT * from login ORDER BY pid ASC");
Click to expand...
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:
PHP: 
$stmt = $con->prepare("SELECT * from login ORDER BY ID");
 
Last edited:
Upvote 0

Colin Evans

Active Member
Licensed User
Longtime User
Hi, attached the connection info as supplied by the database utility on the server, hope that helps
 

Attachments

  • connection info.jpg
    17.2 KB · Views: 196
Upvote 0

Colin Evans

Active Member
Licensed User
Longtime User
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>
 

Attachments

  • PHPMyattempt.zip
    3.9 KB · Views: 200
Upvote 0

aeric

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



 
Upvote 0

Colin Evans

Active Member
Licensed User
Longtime User
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
 
Upvote 0

Colin Evans

Active Member
Licensed User
Longtime User
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
 
Upvote 0

Colin Evans

Active Member
Licensed User
Longtime User
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
 
Upvote 0

TILogistic

Expert
Licensed User
Longtime User
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
 
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Question
B4J Question Has anyone tried to connect a jRDC2 server to an oDataV4 data source?
Replies
8
Views
676
DonManfred
Y
  • Question
B4J Question how to connect to a linked sqlserver server
Replies
3
Views
696
Jorge M A
E
  • Question
B4J Question Remote Database connection MySQL on my Webhosting
Replies
2
Views
811
embedded
E
P
  • Question
B4J Question Connection to Database MSQL
Replies
9
Views
2K
Peter Lewis
P
R
  • Question
B4J Question SQL data not displayed on webpage
Replies
3
Views
1K
alwaysbusy
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…