Connect to a remote MySQL server

How-to Discussions
1

Hi,

I new to app development, but I have experience in C#, PHP, MySQL. I want to connect to a remote MySQL database and I have read much about that in Fuse Community I didn’t find a guide. The only thing I found is that I have to use an API to do that.

Any guide would be appreciated.

Best Gotaiba

2

I’m a Fuse absolute beginner too and I had to work this out by myself. To make it really simple, suppose that you are creating a web site that has to get some data from a MySQL dB but you don’t want that the web page refresh. In this case you are going to use Ajax. The most easy and common way to do Ajax calls inside a web site is with Javascript or Jquery: you connect to your MySQL via a PHP script which does all the query job and returns the result with a simple echo. The echo is then used by Javascript to update the page without refresh.

With Fuse you need to do the same thing, but instead of returning a echo string you need to echo a json_encoded string. So you need to create a PHP script which connects to your dB, do all the queries you want and return a json_encoded string. (If you need to have more control over the query you may create a script that accepts some URL parameters)

Once you have made your script, in Fuse you may call it using Fetch. In this example Fetch calls a JSON file, but you may change it with the path of your PHP script:

fetch('http://www.my_domain.com/myscript.php')
    .then(function(response) { 
        return response.json(); 
    })
    .then(function(responseObject) { 
        data.value = responseObject; 
    });

And this could be your myscript.php:

<?php
    $query_result = array('a' => 1, 'b' => 2, 'c' => 3, 'd' => 4, 'e' => 5);
    echo json_encode($query_result);
?>
3

Great Enrico.

@GFlakes, Enrico explained how to build a basic rest-api. However, there are some guide lines to follow. This will enable you build a great RESTful APi. Please follow the guide on the link below:

NB: You should note that when trying to fetch content via an API from another website, you might get an access control issue. In PHP, to solve this;

Add header(“Access-Control-Allow-Origin: *”); at the beginning of your codes.

Regards

4

Thank you for the rich reply.

I’m now down to it and I hope everything works fine for me . I appreciate the great help.

5

You first need to allow ip by whitelisting it on your mysql database server. After that, configure connection using following code in your connection db file

<?php

function getdb(){

$servername = "46.101.5.233"; // put your cloudways server IP here
$username = "qxxfumxxxbd";
$password = "xxxxbQxxmM";
$db = "qxxfumxxxbd";

try {

   $conn = mysqli_connect($servername, $username, $password, $db);

    //echo "Connected successfully";

   }

catch(exception $e)

   {

   echo "Connection failed: " . $e->getMessage();

   }

   return $conn;

}

Now use mysql client, like workbench to connect to database.

Source: Enabling remote mysql connection.