#1 2015-12-30 15:29:27

datadon
Member
Registered: 2015-04-23
Posts: 108

Connecting to mysql database and update or get info

I warned you I would have questions. :-)

A primary thing I would like to do with the app builder is to create an app that would allow me to update information in a mysql data base.  I have other app that was made for me that can do that that uses a php script to accomplish the task.

I think it connects to the database, finds the record based on a primary field variable that is transmitted along with the new data for another field I will call status for this question.

I do have the script and can share it with you if that would be helpful.  As you are aware I am not a php guy so my knowledge on this only goes so far.

I'm guessing they are using an httppost to send the information to the script.  Once the script has the variables it executes and does the work.  Again I am only guessing.

I am looking for something simple that would allow me to do similar from your appbuilder.

Would this be the way to proceed by interfacing with a server side script?  Or is there another way?

Thanks for any help or ideas.

Offline

#2 2015-12-30 15:39:29

David
Admin
From: Alcobendas, Madrid, Spain
Registered: 2015-04-21
Posts: 1,660
Website

Re: Connecting to mysql database and update or get info

Hello Donald,

Certainly we can deal with remote or server databases from our applications using some server side language (no matter what language) and some server database (no matter what database). Maybe you want to take a look at the Database application sample (Menu -> File -> Samples), which works with PHP (as the server language) and MySQL (as the server database). The sample include the database structure and also the PHP scripts who receive the application requests. *

On the other hand, if your PHP script is prepared to receive HTTP POST or GET arguments, then yes, our App Buidler's applications can perform the appropiate HTTP POST or GET calls in order to pass the appropiate information to your script, who then can store it into the database. I can take a look at your PHP script if you like Donald, so send me and may I can tell you something more about.

* Once open the Database sample, choose the Menu -> App -> Show in explorer to open the sample directory and then look for the PHP scripts.

Offline

#3 2015-12-30 16:49:01

datadon
Member
Registered: 2015-04-23
Posts: 108

Re: Connecting to mysql database and update or get info

Well now we have a can of worms.

I will email you later my script with some notes about it.

In your sample database, I see you have alot of scripts located locally.  Where does this database exist?  Is it created on my pc when run?

Is it possible at some time to see a sample database that will connect to a remote server, login and access an existing database?

Then via the script on the server, make changes to the database,  If that is what the current sample is doing I cannot follow it.


I would envision an app that selected the database name, table and field and then allowed a input to provide new data for the field selected.

If it can all be done without a script then great if not then a script would be needed.  (I am not asking you to do that). 

Thanks

Offline

#4 2015-12-30 17:11:58

David
Admin
From: Alcobendas, Madrid, Spain
Registered: 2015-04-21
Posts: 1,660
Website

Re: Connecting to mysql database and update or get info

Hello Donald,

datadon wrote:

Well now we have a can of worms.

I will email you later my script with some notes about it.

In your sample database, I see you have alot of scripts located locally.  Where does this database exist?  Is it created on my pc when run?

The PHP script must be placed along with our application if we deploy in order to run in mobile or desktop browsers. If our target is also the Android platform, for example, then the PHP scripts must be located in our app's server.

If we take a look at the Database sample PHP scripts there is exactly what we need:

Database.php

It's the PHP Database useful because we share it between the other scripts and provide us the hability to connect to databases (not only MySQL, but all supported by PHP PDO), retrieve data, insert data, etc. In an easy way. This PHP Database class is picked from my Humm PHP project.

PDOExtended.php

Not too much to say except this is our PHP PDO extended class used internally by the above Database class.

Initialize.php

This is just a "template" script, which is included by others in order to reuse their code. Basically we provide the appropiate responses for CORS requests (from our application) and connect to the database, leave it ready to be used.

The above PHP scripts are the stuff we use to interact with the Database. The below scripts are "entry points" for our application's HTTP calls:

DeleteNotes.php

Used to delete notes from the database from our application.

NewNotes.php

Used to add notes to the database from our application.


ShowNotes.php

Used to request notes from the database to be shown in our application.

UpdateNotes.php

Used to update notes in the database from our application.

datadon wrote:

Is it possible at some time to see a sample database that will connect to a remote server, login and access an existing database?

Then via the script on the server, make changes to the database,  If that is what the current sample is doing I cannot follow it.

That is what the Database sample does. In fact the application connect to the server PHP scripts, who are responsible to connect and perform operation over the database. The Database sample performs HTTP calls using the HTTP Client control in order to connect to the server. When we want to insert a new note, the application calls to the "NewNotes.php" providing the new note data, and wait for the server response (an error occur? is the note inserted?). If we want to update a note the Database sample calls to the "UpdateNotes.php" with the appropiate note data, and so on.

datadon wrote:

I would envision an app that selected the database name, table and field and then allowed a input to provide new data for the field selected.

If it can all be done without a script then great if not then a script would be needed.  (I am not asking you to do that). 

Thanks

Currently we need to deal with scripts "manually". Maybe in the future I can prepare something "visual", Donald, but, honestly, I can't promise nothing at this time. However, in fact the required server side script can be not too difficult to create or adapt, for example, take a look at the "InsertNote.php" used by the Database sample:

// Initialize our environment
require 'Initialize.php';

// By default, return a request error
$result = false;

// Find if we have a note ID
if (filter_has_var(INPUT_POST, 'id'))
{
  // Filter the ID
  $id = filter_input(INPUT_POST, 'id', FILTER_VALIDATE_INT);

  // Validate the ID
  if ($id !== false) {

    // Delete the specified note
    $result = Database::delete
    (
      'notes',
      array('id' => $id)
    );
  }
}

// Our app expect a response code
if ($result) {
  // Setup a good response code
  http_response_code(200);
} else {
  // Setup an error response code
  http_response_code(400);
}

Of course we need to know something about PHP (or the language we want to use in the app's server), but, maybe look into the above code (which is reponsible to delete notes from the database) appear more or less easy than if we look into the entire PHP scripts in a whole.

On the other hand, taking a little look at your PHP script, apparently they expects two input arguments: "TYPE" and "MESSAGE", so, probably we can use such script as is, without touch, just by perform the appropiate HTTP call from our application, providing such arguments.

Offline

Board footer

Powered by FluxBB


Copyright ©2018 DecSoft. All rights reserved.