Print

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.

Was this article helpful?
0 out Of 5 Stars
5 Stars 0%
4 Stars 0%
3 Stars 0%
2 Stars 0%
1 Stars 0%
How can we improve this article?
Please submit the reason for your vote so that we can improve the article.
Next PRO: JCI-twig-extensions for Cookies and http-forward
On this page: