Checking on if we're able to get applications we make to link to our site's database?

chrisrog

Member
Messages
33
Reaction score
0
Points
6
Just wondering since I've seen it done with php code but that's purely web based so I was curious about if we could pull off the same thing with say a java applet since it would be on the site?


Busy working on a few things for a game and using a database just happened to be an idea to test in place of having a long txt files of things that can get hard to manage even if setup right.
 

essellar

Community Advocate
Community Support
Messages
3,295
Reaction score
227
Points
63
Re: Checking on if we're able to get applications we make to link to our site's datab

Not directly, no. Since the databases are only accessible through "localhost" and the code will be running on remote machines, there's no way for the applet/app to access the database. You can, however, create a PHP-based API for your applet to call to for its data needs, returning JSON- or XML- formatted data (or something similar, but those two are usually covered in libraries).
 

chrisrog

Member
Messages
33
Reaction score
0
Points
6
Re: Checking on if we're able to get applications we make to link to our site's datab

OK, was searching the forums too but haven't seen very many attempts at doing this. So far I'm testing this at home and got it partly working with javascript but haven't found a php way to do it yet.
 

essellar

Community Advocate
Community Support
Messages
3,295
Reaction score
227
Points
63
Re: Checking on if we're able to get applications we make to link to our site's datab

The PHP doesn't have to do a lot — just fetch and organize (structure) the data before sending it to your page/app.

"API" sounds scary, but it's just a formal way of saying "if I ask you this kind of question, you'll give me this kind of answer". Text with some kind of hierarchical structure is the easiest kind of response to create and parse, especially if you stick to commonly-used conventions for organizing the text so that you can use existing libraries (and don't have to reinvent that particular wheel). JSON (JavaScript Object Notation) is one way to do it; XML is another.

The remote code (web page, applet, app) makes a background HTTP request to your API URL. In the PHP world, "API" is just another way of saying "web page", except that the page won't be outputting HTML. Otherwise it's a PHP script that processes an HTTP request (either a GET or a POST) and writes a response back to the user agent (the browser, applet or app). The hard part is figuring out what the request and the response are going to look like.

For the most part, the request is going to consist of name/value pairs in the form of "param1=some+value&param2=another+value", passed to the server as a GET (a query string appended to the URL) or a POST (form data). You can also use cookies to throw some of the more persistent data — things like user or session ids — back and forth if the remote user agent supports cookies. That should provide enough data to allow you to write a SQL query (preferably using prepared statements since part of the query will depend on user input, and you can never trust user input¹).

The rest is just writing the data out as text in a way that your remote code can process. JSON is generally quicker and less verbose (it takes fewer characters to say the same thing), but it can be harder to read than XML. JSON is essentially a way of listing name/value pairs grouped with curly braces. It's just JavaScript, so if you are familiar with JavaScript object literals and nesting, it won't be hard to create it. There will usually be a library call available to parse it into usable data at the "client" (the call is often simply dataStructure = JSON.parse(responseString)). XML involves more text, but it's the same idea. In both cases, you'll be sending a collection of data objects back to the browser/applet/app. For instance, if you ask for "category=fruit&family=citrus", a typical response might look like this (using XML, and not even trying to be exhaustive):

Code:
<response>
   <subtype>
      <name>grapefruit</name>
      <members>
         <variety>
            <name>Duncan</name>
         </variety>
         <variety>
            <name>Marsh</name>
         </variety>
         <variety>
            <name>Pink</name>
         </variety>
         <variety>
            <name>Ruby Red</name>
         </variety>
      </members>
   </subtype>
   <subtype>
      <name>lemon</name>
      <members>
         <variety>
            <name>Bonny Brae</name>
         </variety>
         <variety>
            <name>Eureka</name>
         </variety>
         <variety>
            <name>Meyer</name>
         </variety>
         <variety>
            <name>Sorrento</name>
         </variety>
      </members>
   </subtype>
   <subtype>
      <name>lime</name>
      <members>
         <variety>
            <name>Blood</name>
         </variety>
         <variety>
            <name>Key</name>
         </variety>
         <variety>
            <name>Kaffir</name>
         </variety>
      </members>
   </subtype>
   <subtype>
      <name>orange</name>
      <members>
         <variety>
            <name>Blood</name>
         </variety>
         <variety>
            <name>Navel</name>
         </variety>
         <variety>
            <name>Queen</name>
         </variety>
         <variety>
            <name>Valencia</name>
         </variety>
      </members>
   </subtype>
</response>

Both JSON and XML support deep nesting, so you have a lot of options for organizing your data and queries. You'll also need to write a content-type header to the response so the page doesn't automatically generate text/html. It may be easier to write something that corresponds closely to what the SQL result set would have looked like (if you think in SQL), or it may be easier to generate ready-to-go objects that are easier to translate into object data types in your client app. As with all programming, data design ("naming things") is the hard part, the rest is just twiddling stuff. Just don't get hung up on the dreaded TLA (three-letter acronym); the API concept is easy to work with, especially in a language that does most of the heavy lifting of parsing the request and writing the response, for you.
__________________________________________
¹ In this case, your application will be the "user", so you might be tempted to think you can trust it. That's not the case. Not only might your remote code fail in unexpected ways, the API will answer any HTTP request thrown at it, so you need to be aware that malicious users can spoof a request using values that you didn't plan for (a SQL injection attack). Prepared statements are really the only safe way to handle user input.
 
Top