ZDB Documentation


Description

A PHP database handler class based on PDO, the input can be an array or a JSON file, it supports multiple queries, as well. (it works with mariadb and mysql)

I wrote this small helper class because I didn't like to write the SQL statements all the time. I think it is much easier to work with the most used statements like SELECT or INSERT like this.

We can declare all of the SQL statments of the application in JSON format in a JSON file or we can use an associative array like the second parameter.

Easy to define multiple SELECT or INSERT statements.

Installation

Installation with Composer

If you use composer you can install ZDB easily if you add it to your composer.json



            {
                "require": {
                    "vendor/ZDB": "dev-master"
                },
                "repositories": [
                    {
                        "type": "vcs",
                        "url":  "[email protected]:zashboy/ZDB.git"
                    }
                ]
            }

Make sure you require the Composer's autoload.php file

Manual installation

You can clone or download the repository to your hdd or server into your preferred directory

git clone [email protected]:zashboy/ZDB.git

If the folders are already on your harddrive you have to require the necessary files into your PHP script where you need to use them.



        <?php 
        
require_once('path/to/zdb/folder/ZDB/src/ZDB.php');
        require_once(
'path/to/zdb/folder/ZDB/src/select.php'); // you need to require all the necessary files... 
        
?>

I recommend to use spl_autoload_register(); and then you don't need to include these files all the time to all your files only the one with the spl function in it.



        <?php 
        spl_autoload_register
(function($class){
            foreach(
glob('*/your-path-to-ZDB-folder/ZDB/src/class.' $class '.php') as $config){
                require_once (
$config);
            }
        });
        
?>
        

Database connection credentials

The ZBD class expects your credentials on two ways. If you use the class with array input you have to define your credentials in there in a config key.into global variables.



    <?php
    define
('DNS''host=your_hostname');
    
define('DBNAME''your_databasename');
    
define('USERNAME''your_username');
    
define('PASSWORD''your_password');
    
define('PATH''your/base/path');
    
?>

The other way if you use a JSON map file for input then you can set your credentials in there in a config key.



            {
                "config": {
                    "DNS": "host=your_hostname",
                    "DBNAME": "your_dbname",
                    "USERNAME": "your_username",
                    "PASSWORD": "your_password"
                }
            }

Usage

Basics

When you need to do some work with the database you just need to instantiate the ZDB class. It takes 3 parameters depends on the input.

$test = new ZDB(array-of-sql,json-file,array-of-variables);

If you want to pass an array with the sql parameters.

You need only one parameter to get the work done and it have to be an array with the right values. It has to be a multidimensional array. On the first level you need to declare the clause ( select, insert, delete) and they have to be arrays, as well, on that level you have to declare your full statement in key value pairs (E.g.: 'tableName'=>'users'), you can find the necessary information about them below. So pass the values and call the data property afterwards.

If you want to use more clauses with the same name you have to name them like action-01, action-02... the name of the clause, a hyphen and a number or some identifier what you like (E.g.: select-01, select-02, select-important).



        <?php
        $test 
= new ZDB(NULL, ['delete'=>['tableName' => 'websites''where'=>['website_id'=>1]],
                
'insert'=>['tableName' => 'websites''values'=>['website_name'=>'jsdkjsakfsa','server_name'=>'dasdadasfas','creation_date'=>'1980-11-23']]]);
                
?>

and you can work with the data to call the data property



        <?php
        $mydata 
$test->data;
        
?>

Instantiated in the constructor

If you have instantiated your PDO instance once in the constructor already you can use the runFile() or runArray() methods.

runFile() --> the name of the section is the first parameter, and the array of variables the second one. The structure of the array has to be same like the structure of the section.

runArray() --> the full array with the clauses what you want to run.



        <?php
        $var 
= [
            
'update-hits' => [
                
'tableName' => 'categories',
                
'set' => '`hits` = hits + 1',
                
'where' => [
                    
'slug' => $section_2
                
]
            ],
            
'select-catname' => [
                
'what' => '`slug`,`title`,`numLinks`',
                
'tableName' => 'categories',
                
'where' => [
                    
'slug' => $section_2
                
],
                
'fetch' => 2
            
],
            
'select-category' => [
                
'tableName' => 'xhamsterallstraight',
                
'where' => '`slugCat` LIKE "%' $section_2 '%"',
                
'orderby' => ['DATE''DESC'],
                
'limit' => $limit,
                
'fetch' => 2
            
],
            
'select-totalitems' => [
                
'what' => 'COUNT(*) AS totalitems',
                
'tableName' => 'xhamsterallstraight',
                
'where' => '`slugCat` LIKE "%' $section_2 '%"',
                
'fetch' => 2
            
]
        ];

        
$this->content $this->db->runArray($var);
                
?>

Select

You use the select statement when you want to get some data from the database

Your array can contain the keys below the keys with bold font must be in the array to start the query.

  • 'what' represents the data what you want to get back from the database, the default value is '*'. It has to be a string (E.g.: 'id, username, date')
  • 'tableName' represents the name of the table which you want to get your data out from. It has to be a string (E.g: 'users')
  • 'where' represents where you want to looking for your data in the table. It has to be an array where the key is one column name and the value is one value (E.g.: 'id' => $_POST['id']). If you have got more items then it is going to use the AND condition between them by default (E.g.: the statement will look like 'id' = :id AND 'name' = :name) any different conditions you want to use you need to write it to a string (E.g.: '`id` = :id OR `name` = :name) and use the 'bindarr' key to include the variables (see below). It uses the PDO prepared statements so it has the basic protection against SQL injection.
  • 'orderby' represents the the order of the data what you expect back. It can be an array (E.g.: array('title', 'ASC')) if you want to set the column name or a string (E.g: 'ASC')
  • 'limit' represents the limit which can be an array if you want to set the start value and the total number of rows (E.g.: array('5,10') or a integer if you set the number of rows only. (E.g.: 1000)
  • 'bindarr' represents an array of variables if give a string to the where clause and you need to bind some values (E.g.: the 'where' string '`id` = :id OR `name` = :name' and in this case the 'bindarr' looks like [':id => $_POST['id'], :name => $_POST['name']]
  • 'fetch' you can set the PDO fetch type here, it has to be an integer, the options are below
    • 0 = PDO_FETCH_USE_DEFAULT
    • 1 = PDO_FETCH_LAZY
    • 0 = PDO_FETCH_USE_DEFAULT
    • 2 = PDO_FETCH_ASSOC
    • 3 = PDO_FETCH_NUM
    • 4 = PDO_FETCH_BOTH
    • 5 = PDO_FETCH_OBJ
    • 6 = PDO_FETCH_BOUND, return true/false only; rely on bound columns
    • 7 = PDO_FETCH_COLUMN, fetch a numbered column only
    • 8 = PDO_FETCH_CLASS, create an instance of named class, call ctor and set properties
    • 9 = PDO_FETCH_INTO, fetch row into an existing object
    • 10 = PDO_FETCH_FUNC, fetch into function and return its result
    • 11 = PDO_FETCH_NAMED, like PDO_FETCH_ASSOC, but can handle duplicate names
    • 12 = PDO_FETCH_KEY_PAIR, fetch into an array where the 1st column is a key and all subsequent columns are values
    • 13 = PDO_FETCH__MAX
    you can find more info about them in the PDO documentation

Insert

You use the insert statement when you want to insert some data to the database

Your array can contain the keys below the keys with bold font must be in the array to start the query.

  • 'tableName' represents the name of the table which you want to insert your data into. It has to be a string (E.g: 'users')
  • 'values' represents your data. It has to be an array where the key is one column name and the value is one value (E.g.: 'username' => $_POST['username']).

Update

You use the update statement when you want to change some data in the database

Your array can contain the keys below the keys with bold font must be in the array to start the query.

  • 'tableName' represents the name of the table which you want to update your data in. It has to be a string (E.g: 'users')
  • 'set' represents the data what you want to change in the table. It has to be an array where the key is one column name and the value is one value (E.g.: 'username' => $_POST['username']) it uses the PDO prepared statements so it has the basic protection against SQL injection.
  • 'where' represents where you want to update your data in the table. It has to be an array where the key is one column name and the value is one value (E.g.: 'username' => $_POST['username']) it uses the PDO prepared statements so it has the basic protection against SQL injection.

Delete

You use the delete statement when you want to delete some data from the database.

Your array can contain the keys below the keys with bold font must be in the array to start the query.

  • 'tableName' represents the name of the table which you want to delete your data out from. It has to be a string (E.g: 'users')
  • 'where' represents where you want to looking for that data in the table. It has to be an array where the key is one column name and the value is one value (E.g.: 'id' => $_POST['id']) it uses the PDO prepared statements so it has the basic protection against SQL injection.
  • 'orderby' represents the the order of the data what you want to delete. It can be an array (E.g.: array('title', 'ASC')) if you want to set the column name or a string (E.g: 'ASC')
  • 'limit' represents the limit which can be an array if you want to set the start value and the total number of rows (E.g.: array('5,10') or a integer if you set the number of rows only. (E.g.: 1000)

Generic

With this key you can input any sql statement. You have to follow the SQL syntax

Your array can contain the keys below the keys with bold font must be in the array to start the query.

  • 'sql' represents full sql syntax. It has to be a string (E.g: 'DROP TABLE `users`')
  • 'bindarr' represents the data if you want to bind some. It has to be an array where the key is one column name and the value is one value (E.g.: ':id' => $_POST['id'])

Transaction

With this key you can make a complete trnsaction with several statements. If one of them fail then the whole transaction will be rolled back.

Your array can contain all of the clause keys above (E.g.: ['transaction' => ['insert' => ...],[delete => ...], [update => ...]]).

ZDB map

What is ZDB map?

ZDB map is a JSON file what contains all the data what you need to do all the necessary jobs with the database. It contains the credentials in the config key and you can name your own sections and declare the necessary clauses in these sections. In this case you need to set which section you want to use at the time when you instantiate the class.

Example of map file



                {
                    "config": {
                        "HOSTNAME": "your_hostname",
                        "DBNAME": "your_dbname",
                        "USERNAME": "your_username",
                        "PASSWORD": "your_password"
                    },
                    "getPages": {
                        "select-1": {
                            "tableName": "pages",
                            "where": {
                                "name": "home"
                            },
                            "fetch": 2
                        },
                        "select-2": {
                            "tableName": "pages",
                            "where": {
                                "name": "about"
                            },
                            "fetch": 2
                        },
                        "select-3": {
                            "tableName": "pages_blog",
                            "where": {
                                "name": "blog"
                            },
                            "fetch": 2
                        },
                        "insert-0": {
                            "sql": "CREATE DATABASE test2"
                        }
                
                    },
                    "getTestPages": {
                        "select": {
                            "tableName": "pages",
                            "where": {
                                "name": "home"
                            }
                        }
                    }
                }

Example of instantiation with map file

In this case if you need to use some variables in the queries then you need to pass an array with exactly the same layout like your file's layout. (E.g.: with the file above I want to change one select statement in the getPages section ['getPages' => ['select-1' => ['where' => ['name' => 'disclaimer']]]])



    <?php
    $test 
= new ZDB(NULL'relative/path/to/json/file'$array-of-variables);
    
?>

Coding


Some of my codes what I can share, hopefully this list keep growing in the future.

You can find my latest codes below


There is no content, yet.


Close Programming See more

Blog


My little blog where I try to note down what I learned lately, or mostly some code related stuff. No, I don't want to teach, they are not really tutorials just small thoughts, ideas how I sort out some issues what comes to my way. Probabaly, these articles can help someone or give some ideas to someone, I know how hard to find the infos at the moment when you need them.

You can find my latest posts below


There is no content, yet.


Close Blog See more

Gaming


If I've got time I really like to play with some video games, I wish I could do it more. I don't say I'm a hc gamer and I'm not even an expert who wants to explain every game in the videos. I play mostly story based games, rpgs, mostly without any comment just to enjoy the story. I know from myself I like better to watch some random gameplay instead of to watch tv.

You can find my latest gameplay videos below


There is no content, yet.


Close gaming See more