1

I've successfully managed to use POST to run a PHP script on my website, which allows the phone application to add a new entry to the database (MySQL), and delete an entry.

The next step is the one I have been struggling with for the last few hours now, and that is getting the information FROM the DB onto the phone!

I would like a method that initially just connects to the DB upon starting the activity and populating listview or something will all entries, and later down the line I plan on copying the information to a SQLite DB within the phone.

What is the easiest method I can look into for achieving this? I can be resourceful but I just need to know what I'm looking for!

aspirant_sensei
  • 1,568
  • 1
  • 16
  • 36
  • 2
    What is preventing you from using GET requests to retrieve the data from PHP scripts as well? Cache them locally (SQLite or a NoSQL alternative) and retrieve them at will! – Sébastien Renauld Apr 11 '13 at 21:24
  • I don't need to access the php scripts, I just need to get data from the MySQL DB. Is there no way to straight connect to it, are you saying I have to create a webpage that retrieves the data and then access that script on the phone? I'm kinda confused... – aspirant_sensei Apr 11 '13 at 21:39
  • Two things. 1. Most mobile operators block access to port 3306. 2. Most MySQL servers block connections not coming from localhost (or a very specific subset of servers). You're better off "tunneling" (well, kind of) through a webserver. – Sébastien Renauld Apr 11 '13 at 21:42

3 Answers3

1

In general you need to create server API: choose some format to talk to between your web service and android application. Then you'll always be need to request some data from server, that will be returned to you in format described above in a body of network response of some sort. Next all you need is parse this data and populate to your adapters or whatever.

Note that networking operation might take quite some time depending on your connection, so you can't wait while it ends to show your UI - you need to do this in async manner, and give user a feedback that data is retrieving.

Nowadays json format passed in body of http post requests are quite popular. Take a look at this tutorial on how to parse json on android and this video about how to create json api in php.

Of course you can try to connect to remote MySQL server directly.. It really is more simple solution in some cases (you don't need to code server-side api), but might be not so accessible because standard MySQL ports aren't opened in all networks. Also your API server might hide some implementation details on how is data stored in reality, thus allowing you to migrate for example from MySQL to PostgreSQL without pain for android application.

Don't forget to secure your data from unauthorized access!

EDIT

It's 2017 and what would be the easiest option now is to use opensource project which will provide rest api for your database, for instance ArrestDB or postgrest

dant3
  • 966
  • 9
  • 26
1

you have many options.

  1. higher level abstraction over HTTP (REST/SOAP/etc) like already mentioned
  2. HTTP as a proxy for plaintext/CSV data (without abstraction)
  3. a direct JDBC connection from android device to the MySQL
  4. database data export/import

I guess you're looking for option 3. That is syncing a remote database to local (SQlite on android) and then working with local data? In this case you just get a mysql-client jar (JDBC drivers) into your app and you can start. There're some restrictions though, like Sébastien Renauld already mentioned in the comment. Yet, those issues can be worked around, i.e. with custom configuration of MySQL or with option (2) which can be implemented in generic way (write once)

comeGetSome
  • 1,913
  • 19
  • 20
  • this sounds perfect, i'm only making this application as a concept it can run through localhost over a wi-fi network. I'll google JDBC and see if it's for me + within my skill level, thanks! – aspirant_sensei Apr 11 '13 at 22:48
  • sorry dude, I really can't find the JDBC driver, can you provide a link please? – aspirant_sensei Apr 11 '13 at 23:50
  • the driver you can download here http://dev.mysql.com/downloads/connector/j/ unpack the file and locate a .jar, that is the driver to use. A sample code http://stackoverflow.com/questions/4447692/jdbc-connection-in-android to make a connection and in case you need more than 1 hour you can still fallback to (2) as described here http://www.basic4ppc.com/forum/basic4android-getting-started-tutorials/8339-connect-android-mysql-database-tutorial.html its well written. – comeGetSome Apr 12 '13 at 09:00
0

I personaly had to develop this following REST API Service (based on Laravel framework, which I call it lRapi) for an iOS and Android devices, and works great (the version in use for the apps, is much more complex).

https://github.com/w0rldart/lRapi

There are plenty Models and Controllers there that you may use to get started. Responses are JSON formatted, with proper headers. I still have to add some more documentation to it, but there is some on the main example view, which you may access by just setting the virtual host and opening the root page in browser.

Laravel is a MVC PHP Framework, and it's really easy to get used to it.

This a good way to avoid to do most of the work, and just focus on implementing what else you need.

Alex
  • 7,538
  • 23
  • 84
  • 152