Using a PHP script to make a MySQL connection for a desktop application

Question

Ok here's the thing.

My webhost, which happens to be the cheapest and most reliable in my country, doesn't allow direct access to my MySQL database. I can only connect to it using "localhost" on the website.

This is a bit of a piccle since I have to write a desktop application to interact with the database (could use a web interface, but I'd like to avoid it if I can). I'll probably write it in C#.

So maybe I could make a simple PHP MySQL connection script and somehow query the database with C# using that connection. Is this possible?

Answer 1

It's certainly possible. Commonly direct database interface scripts are used for that purpose. But take care to limit access (.htpasswd, Allow From, https?) to it.

You could use a simple JSON or POST interface which directly accepts SQL queries, and simply returns the result as JSON array:

<?php
// maybe even: $_POST = json_decode(file_get_contents("php://input"));

$db = new PDO(...);
$stmt = $db->prepare($_POST["query"]);
$stmt->execute($_POST["params"]);   // enumerated array

print json_encode($stmt->fetchAll());

You might need to add some error checking and devise a signaling mechanism (typically it suffices to send a result array with magic values or identifiers to differentiate it from ordinary data lists).

Answer 2

I can tell u can use SQLyog php-mysql tunnel, but even this is a fast tunnel script u have to think about some delay process, when u get big or huge results this has to be processed first by script before it send answer to your application, so if that software its not time critical one, will just work fine with this, otherwise, i encourage u to get a better hosting solution, even outside your country.

Answer 3

I would write a web service to gather the data and return it in a more usable format instead of allowing the script to execute raw queries. That gives you better security (by not allowing arbitrary queries) and easier maintenance (by being able to change the underlying infrastructure without messing with both the server script and the desktop client) at once.

Answer 4

Yes. It is possible. You can use PHP for connecting and run queries there. Please don't forget adding some security codes for php code. because http access to your php code would couse dangers. You can encrypt PHP outputs.

Answer 5

If your requirement from a client is direct access I would change hosts and use a third party mysql client and not reinvent the wheel again.