PHP Classes

File: examples/sqlSnippet.class.php

Recommend this page to a friend!
  Classes of wim niemans   SIREN PHP Templating Library   examples/sqlSnippet.class.php   Download  
File: examples/sqlSnippet.class.php
Role: Example script
Content type: text/plain
Description: example of an application for education / studying purposes only
Class: SIREN PHP Templating Library
Template engine featuring recursion and nesting
Author: By
Last change:
Date: 3 years ago
Size: 4,264 bytes
 

Contents

Class file image Download
<?php

/**
 * This file is an example of an application with Snippet class.
 * Distribution is intended for education / studying purposes only.
 *
 * Copyright [2020] [Wim Niemans <niemans@pbsolo.nl>]
 */

include_once '../Snippet.class.php';

/**
 * @author wim niemans, Rotterdam, Bonn
 * @license EUPL
 */

class sqlSnippet {

public
$name = ''; // name, will be used for tVar's

private const SNIPPETS =
[
   
'UPDATE' => 'UPDATE {which} SET {what} {where} {orderBy} {limit}', // general UPDATE
   
'DELETE' => 'DELETE FROM {which} {where} {orderBy} {limit}', // general DELETE
   
'INSERT' => 'INSERT INTO {which} SET {what}', // general INSERT
   
'CREATE' => 'INSERT INTO {which} ({cols}) VALUES ({values})', // alternative INSERT syntax
   
'MODIFY' => 'REPLACE INTO {which} SET {what}', // alternative UPDATE syntax, general REPLACE
   
'REPLACE' => 'REPLACE INTO {which} ({cols}) VALUES ({values})', // alternative UPDATE syntax, alternative REPLACE syntax
   
'LOOKUP' => 'SELECT {what} FROM {which} {where}', // general LOOKUP: getOne item
   
'SUMMARY' => 'SELECT DISTINCT {what} FROM {which} {where}', // get DISTINCT items
   
'COUNT' => 'SELECT COUNT(*) AS rowCount FROM {which} {where}', // get COUNT rows
];

protected
$sql = ''; // complete sql statement

private $snippet = '';

/**
 * syntax elements : where, orderBy, Limit
 */
public $orderBy = ''; // optional orderBy colNames
public $limit = ''; // optional Limit clause
public $where = ''; // where conditions, condensed from $this->when, optional extended by addPredicate()

public function __construct($command)
{
   
$name = strtoupper($command);
   
$this->name = 'command::' . $name;
   
$this->snippet = self::SNIPPETS[$command];
}

public function
getSQL()
{
    return
$this->sql;
}

/**
 * creates a sql statement
 *
 * @param array $what column names
 * @param array $values column values :note: string values should contain proper quoteing
 * @returns string sql statement
 */
public function createSql($what = array(), $values = array())
{
   
$templet = new Snippet('remove');
   
$templet->setVar('snippet', $this->snippet);
   
$templet->setVar('which', $this->which);

    if (!empty(
$what)) {
        if (!
is_array($what)) {
           
$templet->setVar('what', $what);
        } elseif (
array_diff_key($what, array_keys(array_keys($what)))) { // is assoc ?
           
$templet->setVar('cols', implode(', ', array_keys($what)));
           
$templet->setVar('values', implode(', ', array_values($what)));
        } elseif (!empty(
$values)) {
           
$templet->setVar('cols', implode(', ', $what));
           
$templet->setVar('values', implode(', ', $values));
        } else {
           
$templet->setVar('what', implode(', ', $what));
        }
    }

    if (!empty(
$this->where)) { $where = ' WHERE ' .$this->where; }
    if (!empty(
$this->orderBy)) { $orderBy = ' ORDER BY ' .$this->orderBy; } // $this->guessOrderBy();
   
if (!empty($this->limit)) { $limit = ' LIMIT ' .$this->limit; } // $this->guessLimit();

   
$templet->setVar('where', $where);
   
$templet->setVar('orderBy', $orderBy); // $this->guessOrderBy();
   
$templet->setVar('limit', $limit); // $this->guessLimit();

   
$this->sql = $templet->parse('output', 'snippet');
   
$this->sql = $templet->tidy('output');

    return
$this->getSQL();
}

/**
 * sets which: sqlTable name
 */
public function setWhich($which)
{
   
$this->which = $which;
}

public function
addWhere($_string, $how = 'AND')
{
    if (!empty(
$_string)) {
       
$string = $this->makeNative($_string);
        if (empty(
$this->where)) { $this->where = $string; }
        else {
$this->where .= ' ' .$how. ' (' .$string. ')'; }
    }
}

public function
setLimit($limit)
{
   
$this->limit = $limit;
}

public function
setOrderBy($orderBy)
{
    if (!empty(
$this->orderBy)) { $this->orderBy .= ', '; }
   
$this->orderBy .= $orderBy;
}

}
// end of sqlSnippet class
 
?>