interface with mysql via php

Tom Duncan · 2013-12-12 11:39:30

Hi,
Last year I was developing software to interface with an Android device with basic4android.
Well this part is going wellWhat I have done and doing is use sqlite on the Android device and Update the files via a mysql database on my server.
This I need because the user will not be in internet availablity some of the time.

I want to use this same framework on my pc side of things as well.
Sofar with your software I can do a sql select statement and get the results onto a dbgrid using a modded version of your demo 17 (I think)
My question is how can I sent inserts onto the sql database.
I have no idea how to setup an insert. I assume the data needs to be entered into a json string then the insert event called.
Have you any demos on this or is it easier to interface with zeos and your components to do the inserting.

Hope this makes sense.

Tom Duncan

Tom Duncan · 2013-12-12 11:53:54

I do not want to use zeos to connect to MySQL directly since my server limits who has access to the databases.
The http idea is so much easier for clients.
The MySQL is a link to my sqlite database on the PC and the sqlite databases on the android devices.
I will have an update table so any device can check if they have the latest data for any table.

Tom

ab · 2013-12-12 15:27:10

I'm not sure I understood your needs...

You want to write a Delphi client accessing MySQL via a PHP server backend, over HTTP?

That is:

Delphi Client <---HTTP---> PHP <---dblink---> MySQL

I suppose your PHP server is serving some JSON content.

You need to write some JSON commands from the Delphi Client, then write code on the PHP server to access the database.

It is far away from mORMot, since the server is not mORMot, but you have to write your own PHP server.
See e.g. http://coreymaynard.com/blog/creating-a … -with-php/
Then write your own JSON client in Delphi. Here mORMot units may help, but you have other options around.

I found an already existing Open Source project for it:
http://phprestsql.sourceforge.net/download.html

Tom Duncan · 2013-12-12 19:37:30

The PHP script is here.

<?

$databasehost = "localhost";
$databasename = "xxxx";
$databaseusername ="xxxx";
$databasepassword = "xxxx";

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());
mysql_query("SET CHARACTER SET utf8");
$query = file_get_contents("php://input");
$sth = mysql_query($query);

if (mysql_errno()) {
    header("HTTP/1.1 500 Internal Server Error");
    echo $query.'\n';
    echo mysql_error();
}
else
{
    $rows = array();
    while($r = mysql_fetch_assoc($sth)) {
        $rows[] = $r;
    }
    print json_encode($rows);
}
?>

By calling the server and then using your demo I can get the information.
I.e. select id,address from houses.
So this bit works well and I can get this data into my sqlite database.
The sending the data back to the server is the hard part.

Tom

ab · 2013-12-12 23:13:48

Your code is just a huge security break.
This should never be use for any serious app.

Please try to use something more evolved, like my links above.

Tom Duncan · 2013-12-13 00:25:56

Thanks for that.
Will have a look when I get home.
Tom

mORMot Open Source

#1 2013-12-12 11:39:30

interface with mysql via php

#2 2013-12-12 11:53:54

Re: interface with mysql via php

#3 2013-12-12 15:27:10

Re: interface with mysql via php

#4 2013-12-12 19:37:30

Re: interface with mysql via php

#5 2013-12-12 23:13:48

Re: interface with mysql via php

#6 2013-12-13 00:25:56

Re: interface with mysql via php

Board footer