how to create db tables in api ?

[adrianmak]

is there any api to create db table?

I have a project required user to input raw  numeric data which is used for generation of charts.

The default pw's pages is not quit an option for user input.

I would like to create a module, enable it will create all necessary data tables ?

I will build a template for data user input.

Is there any db query api for db creation, table query, update, insert, delete operations ?

[kongondo]

I haven't seen any. You have at least two choices:

1. Create a Fieldtype module that you can attach to a page and use that to input your data. The page would mainly be a container for your input fields, although you would have to think about how to retrieve your stored data in which case the page ID might be handy. Behind the scenes, Fieldtype modules directly interact with the db - saving -> sleepValue() and retrieving -> wakeupValue(). This assumes that you will create a table only once for holding your data (when you create fields that use the Fieldtype) after which you will just be adding/modifying/removing db rows in THAT table. You would also be able to use raw MySQL if you wanted to. If you choose that, I highly recommend you use prepared statements for both security and future compatibility with ProcessWire. Have a look at the module matrix for ideas if you wish.

2. Create a ProcessModule with input fields to send/display your data. In this case you would have to use raw SQL to CRUD your data. Again use prepared statements and strict validation. Here too I assume you want to create only one table before hand (e.g. on install of the module - see changelog module for instance) and use that to store your data.

There could be better ways of approaching this.....but I would create the tables once before-hand (on install of your module).

[adrian]

Just to add to kongondo's info, there is a little helper variable ($database) for PDO database work:

$sql = "Your table and field creation statement";
$query = $database->prepare($sql);
$query->execute();

[adrianmak]

Just to add to kongondo's info, there is a little helper variable ($database) for PDO database work:

$sql = "Your table and field creation statement";
$query = $database->prepare($sql);
$query->execute();

the $database is a pw's api variable or php built-in? I could not find in pw api documentation.

And can it be used on template and module directly ?

[kongondo]

/wire/core/Database.php

This is pretty high level stuff that you won't find in the (current) API documentation which (the documentation) is aimed at everyday PW use rather than module dev....

The class Database is available everywhere...

[adrian]

It's a PW variable and yes you can use it in your templates/modules if you need to do a direct SQL query. 

There's not that much on it, but a few posts that might give you some background/info:

https://processwire.com/talk/topic/3768-processwire-dev-branch/?p=36787

https://processwire.com/talk/topic/1918-mix-api-with-standard-query/

https://processwire.com/about/news/introducing-processwire-2.4/

[Wanze]

If you're not afraid of pirates, there's also the Table ProField: https://processwire.com/api/modules/profields/table/

[adrianmak]

It's a PW variable and yes you can use it in your templates/modules if you need to do a direct SQL query. 

There's not that much on it, but a few posts that might give you some background/info:

https://processwire.com/talk/topic/3768-processwire-dev-branch/?p=36787

https://processwire.com/talk/topic/1918-mix-api-with-standard-query/

https://processwire.com/about/news/introducing-processwire-2.4/

From the first link, $db and $database are both existed, which is refer to mysqli and PDO driver resp.

Should I specify $this->database in module ?

[teppo]

@adrianmak: MySQLi ($db) is kept around for backward compatibility, but $database should be used for all new stuff.

In templates you use $database. In module context you'll have to use $this->database, which is automatically available there. You can also use wire('database'), available everywhere.