PRO: JCI-twig-extensions for database and woocommerce
Direct access to the (WordPress-)Database with twig
jci_db_create
Create a database-table: TAKE CARE!!
To execute a command like “CREATE TABLE db_table_name(name-of-column1 datatype-column1, name-of-column2 datatype-column2,…)” you can use this twig-function.
- db_table_name: name of the new table
- json_for_db must be a JSON like {“name-of-column1″:”datatype-column1″,”name-of-column1″:”datatype-column2”,…}
Yet jci_db_create is a simple create. E. g. you can’t create primary keys (no “CONSTRAINT [constraint_name] PRIMARY KEY [ USING BTREE | HASH ] (column1, column2, … column_n)” or using “ALTER”-Commands). Do that with tools like PHPMyAdmin.
{% set dbresult = jci_db_create(db_table_name, json_for_db) %}
RETURN:
JSON as twig-array, so by {% if dbresult.success %} you can check if the execution was successful
{"success":false,"result":"Table 'newdb' already exists"}
or
{"success":true,"result":true}
Example:
{% set tname = "newdb" %}
{% set dbres = jci_db_create(tname, '{"no1":"int", "no2":"varchar(255)"}') %}
<br>{{dbres| dump }}, res: {{dbres.result}}
jci_db_insert
Insert data into a database-table. To execute a command like “INSERT INTO db_table_name (‘key1’, ‘key2’,… ) VALUES (‘val1’, ‘val2’,…)” you can use this twig-function.
- json_for_db must be a JSON like {“key1″:”val1″,”key2″:”val2”,…}
- is_wp_db: optional, default is 1. if set to any value than 1 the WordPress-Database Prefix is NOT added to get databases besides WordPress.
{% set dbresult = jci_db_insert(db_table_name, json_for_db, is_wp_db) %}
RETURN:
JSON as twig-array, so by {% if dbresult.success %} you can check if the execution was successful. dbresult{‘result’} has an Error-Description if success is FALSE.
{% set tname = "newdb" %}
{% set ti =("now"| date("U")) %}
{% set ra = random(50, 200) %}
{% set datain = '{"no1": '~ti~', "no2": '~ra~'}' %}
datain: {{datain }}<br>
{% set dbres = jci_db_insert(tname, datain, 'otherthan1') %}
insert:<br>
{{dbres | dump }}, res: {{dbres.result}}
jci_db_select
Select data from a database-table. To execute a command like “SELECT db_fields FROM db_table_name WHERE db_where” you can use this twig-function.
- db_where: optional, default empty
- is_wp_db: optional, default is 1. if set to any value than 1 the WordPress-Database Prefix is NOT added to get databases besides WordPress.
{% set dbresult = jci_db_select(db_fields, db_table_name, db_where, is_wp_db) %}
RETURN:
JSON as twig-array, dbresult{‘success’} either TRUE or FALSE. dbresult{‘result’} has an Error-Description if success is FALSE. dbresult{‘result’} has the result of the Database-select if success is TRUE
Example:
{% set tname = "newdb" %}
{% set dbres = jci_db_select("*", tname, '', 'whatever2') %}
{% if dbres.success %}
select:<br>
{% for i in dbres.result %}
{{i.no1}}-{{i.no2}}<br>
{% endfor %}
{% else %}
error: {{dbres.result}}
{% endif %}
jci_db_update
Update data in a database-table. To execute a command like “UPDATE db_table_name SET key1=val1, key2=val2, … WHERE db_where” you can use this twig-function.
- json_for_db must be a JSON like {“key1″:”val1″,”key2″:”val2”,…}
- db_where: optional, default empty
- is_wp_db: optional, default is 1. if set to any value than 1 the WordPress-Database Prefix is NOT added to get databases besides WordPress.
{% set dbresult = jci_db_update(db_table_name, json_for_db, db_where, is_wp_db) %}
RETURN:
JSON as twig-array, dbresult{‘success’} either TRUE or FALSE. dbresult{‘result’} has an Error-Description if success is FALSE.
Example:
{% set tname = "newdb" %}
{% set dbres = jci_db_update(tname, '{"no2":54}', "no2>0", 3) %}
<br>up: {{dbres| dump }}, res: {{dbres.result}}<hr>
jci_db_delete
Delete data from a database-table: TAKE CARE!!
To execute a command like “DELETE FROM db_table_name WHERE db_where” you can use this twig-function.
- db_where: optional, default empty
- is_wp_db: optional, default is 1. if set to any value than 1 the WordPress-Database Prefix is NOT added to get databases besides WordPress.
{% set dbresult = jci_db_delete(db_table_name, db_where, is_wp_db) %}
RETURN:
JSON as twig-array, dbresult{‘success’} either TRUE or FALSE. dbresult{‘result’} has an Error-Description if success is FALSE.
Example:
{% set tname = "newdb" %}
{% set dbres = jci_db_delete(tname, "no2>60", 53) %}
<br>del: {{dbres| dump }}, res: {{dbres.result}}<hr>
Use the Woocommerce-API
The Plugin Woocommerce is the a great software for runnung Online-Shops. You can use the JCI-Plugin to connect an API with Woocommerce.
Authentatication: Get key and secret
See the Woocommerce-Settings, tab “Advanced”. There you find a “REST API” Link. There you can define who can use the Woocommerce-API how. So set up a key with permissions “read/write”. You get an “Consumer key” and a “Consumer secret”.
Copy both to a textfile, you need them for accessing the API.
Woocommerce: Show all products
jci_woo_calc_auth('consumer_key', 'consumer_secret', 'YOUR_DOMAIN/wp-json/wc/v3/products' , 'GET')
Create a new JCI Template with this twig-template for the JSON-display.
This JCI-Template should show you all products in the Woocommerce at YOUR_DOMAIN.
<hr>woo-products:<br>
<ul>
{% for p in _context %}
{% if p.id %}
<li>{{p.id}}: {{p.name}}, sale_price: {{p.sale_price}}</li>
{% endif %}
{% endfor %}
</ul>
URL for the JCI Template (replace YOUR_DOMAIN):
YOUR_DOMAIN/wp-json/wc/v3/products
Curloptions (replace consumer_key, consumer_secret and YOUR_DOMAIN by real values):
CURLOPT_HTTPHEADER={{ jci_woo_calc_auth('consumer_key', 'consumer_secret', 'YOUR_DOMAIN/wp-json/wc/v3/products' , 'GET') }}
Woocommerce: Add Product
For that you must create two new JCI Templates:
- Template 1, name it “dowoo”:
twig:{{_context | json_encode }}
Curloptions:##param1## - Template 2, name it “insert woo”
URL:dummyrequest
twig-template:
{% set wurl = 'YOUR_DOMAIN/wp-json/wc/v3/products' %}
{% set wmeth = 'POST' %}
{% set customer_key= '.....' %}
{% set customer_secret = '.....' %}
{% set prod = '{"name": "new product", "type": "simple", "description": "this is my new product", "short_description": "short: this is my product in short"}' %}
{% set wooauthstr = "CURLOPT_HTTPHEADER=Content-Type:application/json##" ~jci_woo_calc_auth(customer_key, customer_secret, wurl , wmeth )~ ';CURLOPT_POSTFIELDS='~prod %}
{% set jc = '[jsoncontentimporterpro orderofshortcodeeval=10 httpstatuscodemustbe200=no nameoftemplate=dowoo url="'~wurl~'" method="'~wmeth~'" param1=\''~wooauthstr~'\']' %}
{% set z1 = (jc |doshortcode) %}
{% set z = (z1 | json_decode(TRUE) ) %}
product inserted
Using the Shortcode for Template 2 puts together the settings for a new dummy-product and calls the Woocommerce-API by Template 2.