Access Database rom outside ProcessWire

[@byte]

Hallo,

i need to access tables in the Processwire Database from a Android App to read, insert and update tables.
Is it possible to write a api.php to accomplish that task?

 

[zoeck]

Of course you can do that with Processwire, there is also a module for it 😉 

 

[bernhard]

This could also be an interesting read for you: https://processwire.com/talk/topic/26792-headless-processwire-how-do-you-do-it/

 

[@byte]

Thanks for the Response.
Right now i use somethin like this.

It works, just thought there is a way in Processwire to do it more professional.
Looked at the two modules and did not really understand how to solve my problem with them.

Edited February 13, 2023 by @byte
Bad code

[zoeck]

Do you want to simply access existing Processwire pages or create new ones?
Or do you have your own table ("kontrundgang") where the data should go?

You can have a look at the Database API: https://processwire.com/api/ref/wire-database-p-d-o/

By the way: you should never execute unsanitized values in a sql statement!

[@byte]

For now (testing the app) the tables are my own. Later i want to manage the entry's from the Backend.
Still don't know how exactly i am going to do this, but i am in contact with bernhard for a solution.
The posted code is part of the template for a api-page "example.com/api/" and i send the data from the App with POST.

Just changed it with $input->urlSegment(1), so i can take the Post mode out.

[bernhard]

1 hour ago, @byte said:

It works, just thought there is a way in Processwire to do it more professional.

I'd not call it more professional. It's just different how you usually do such things in ProcessWire.

In common PHP frameworks you'll mostly have the same setup: Database tables and PHP + mysql queries.

In ProcessWire this is totally different: You have Pages + the PW API.

The PW way has a lot of benefits, for example:

• You don't have to build a GUI - you get the PW Backend for free
• You don't need to write plain SQL - you have the PW API for reading/writing data which is usually a lot easier and also more secure

In your example instead of having a DB table "rundgang" with columns "person, wasser, start, ende" you'd add a PW template called "rundgang" and add fields "person, wasser, start, ende" to that template.

To add records to your system instead of doing "INSERT INTO ..." you do this:

<?php namespace ProcessWire;
$p = new Page();
$p->template = 'rundgang';
$p->parent = 1234; // your parent page id
$p->person = $pages->get("template=person,name=bernhard");
$p->start = "2023-01-05 20:00";
$p->ende = "2023-01-05 21:30";
$p->save();

And to output entries on the frontend instead of doing "SELECT ... FROM ..." you'd do something like this:

<?php namespace ProcessWire;
$start = strtotime("2023-01-01 00:00");
$end = strtotime("2023-02-01 00:00");
foreach($pages->find("template=rundgang, person=123, start>=$start, ende<$end") as $p) {
  echo "Rundgang: {$p->person->title} von {$p->start} bis {$p->ende}<br>";
}

To create a custom endpoint for your App have a read about URL Hooks and Bootstrapping PW.

[@byte]

Well for now i can't do it with a page, because i can't get a Table-View.
We will see wednesday, if we take your Module. I think it's a total different discussion then.

[bernhard]

2 minutes ago, @byte said:

I think it's a total different discussion then.

Well, the whole concept of storing data in PW and Pages vs. DB-Tables is the same.

RockGrid just helps you to present that data in a tabular way with client side sorting and filtering. But the data needs to be present in pages. 🙂 

[zoeck]

19 minutes ago, @byte said:

Well for now i can't do it with a page, because i can't get a Table-View.

Do you already know the lister function of the Processwire?
Simply go to Pages -> Find, then you can display a table with pages (with adjustable columns).

There is also an extended (paid) variant: ListerPro

[Jan Romero]

@@byte the code you posted is vulnerable to SQL injections, because you're concatenating raw strings from the request into your SQL query (as @zoeck said). Even if it's just an example you're not using in production, I'd prefer if you didn't post it like that, because people might copy it.

[@byte]

1 hour ago, Jan Romero said:

@@byte the code you posted is vulnerable to SQL injections, because you're concatenating raw strings from the request into your SQL query (as @zoeck said). Even if it's just an example you're not using in production, I'd prefer if you didn't post it like that, because people might copy it.

Sorry, deleted the Code. I am not using it in production, just on localhost. to test the Android-App.

[@byte]

Changed my Code to that now

<?php namespace ProcessWire;
if($input->urlSegment(1) == 'rundgang'){
	$kontrolleur = $sanitizer->text($input->post->kontrolleur);
	$gewaesser = $sanitizer->text($input->post->gewaesser);
	$start = $sanitizer->text($input->post->start);
	$ende = $sanitizer->text($input->post->ende);

	$statement = "INSERT INTO kontrundgang (kontrolleur, gewaesser, start, ende) VALUES (:kontrolleur, :gewaesser, :start, :ende)";
	$query = $database->prepare($statement);
	$query->bindValue(':kontrolleur', $kontrolleur);
    $query->bindValue(':gewaesser', $gewaesser);
    $query->bindValue(':start', $start);
    $query->bindValue(':ende', $ende);
	
	try {
        $query->execute();
        $response['result'] = 'Daten übertragen..';
        $query->closeCursor();
    } catch (\Exception $e) {
        $response['result'] = 'Fehler, bitte erneut übertragen!';
    }
    echo json_encode($response);
}
?>

 

[bernhard]

11 hours ago, @byte said:

$kontrolleur = $sanitizer->text($input->post->kontrolleur);

Thx for updating the code! Nothing wrong with it, but this would also work and is a little shorter 🙂 

$kontrolleur = $input->post('kontrolleur', 'text');

 

[@byte]

Thanks for the tipp.

Got a problem with json_encode. The template Content-Type is set to application/json but i still get the html including the json array instead of just the json array  as result.
Are there any other settings i have to change in order to get just the json array returned?

[zoeck]

Possibly the same problem as here:

 

If Tracy is installed, you should deactivate it in the template (you can do this in the Tracy settings):

No debug bar in selected frontend templates
Disable the debug bar on pages with the selected templates.

[@byte]

$config->prependTemplateFile = '_init.php';
$config->appendTemplateFile = '_main.php';

Thanks a lot, that did the Trick. Just deactivated it for the template.