Class Properties and Methods - Sql.class.php

This class is the nerve center of communicating with the database. It's designed to work in tandem with the CMS class to take different parameters and dynamically construct different types of queries. It's what allows the developer of a new site to bypass any database scripting when creating a new directory, as long as all the inputs on the form are formatted correctly. More on that later (link to directory content editors here)

Makes exclusive use of mysqli prepared statements.

requires nd_mysqli plugin to be installed & enabled in php settings on server

Properties

• private static $dbhost
• private static $dbuser
• private static $dbpass
• private static $dbname
  • Database credentials
    
    
• private static $prep_Script
  • Container for building the prep statement. Also handy for debugging; it's a normal string, so it won't return false or something else un-useful if the mysqli prepare() method fails.
    
    
• private static $prep_Statement
  • Always set to $prep_Script, and fed into the mysqli prepare() method
    
    
• public $conn
  • Establishes mysqli connection to database

Methods

• public function __construct($args)
  • Sets properties to the class on declaration, and connects to the database
    
    
  • Returns: None. Sets values to the database credentials properties, the $conn property, and runs mysqli_connect() on $conn.
    
    
  • Parameters
    • $args
      • Must be an array
      • Indices must be
        • $args['dbhost']
        • $args['dbuser']
        • $args['dbpass']
        • $args['dbname']

• public function __call($member, $arguments)
  • Magic method used to overload the following prep_Select() method
    
    
  • returns:None. Detects what method the script is attempting to call, appends number of arguments to the method name, and calls the appropriate private method within the scope.
    • In this case, the script will always call prep_Select(), with 1 - 4 arguments. The Sql object won't find any prep_Select method, and __call() is called instead.
      
      
    • Below the __call() method are four private methods
      • prep_Select1($table)
      • prep_Select2($table, $columns)
      • prep_Select3($table, $whereOrder, $columns)
      • prep_Select4($table, $where, $order, $columns)
        
        
    • Depending on the number of arguments the script sent with the prep_Select method, __call will load the parameters into the appropriate iteration that's matched.
      
      
  • Note: This behavior overrides the default error when calling a method that doesn't exist within the Sql class scope. Instead, __call() will search for the erroneous method within its scope (such as prepSelect() or prep_Selec()), and report that one can't be found.

• public function prep_Select1($table)
  • Builds prep statement for retrieving data from a database table
    
    
  • Returns: None. Sets the value of $prep_Statement for a SELECT query
    
    
  • Parameters
    • $table
      • Mandatory. Sets which table to select from in the database
  • Note: This is the most basic form of the prep_Select() method. It simply builds & prepares: 
    

    • SELECT * FROM $table

• public function prep_Select2($table, $columns)
  • Builds prep statement for retrieving data from a database table
    
    
  • Returns: None. Sets the value of $prep_Statement for a SELECT query
    
    
  • Parameters
    • $table
      • Mandatory. Same function as prep_Select1()
    • $columns
      • Mandatory. Can be an array, or a string
        
        
      • Declares which columns from the table to retrieve data from.

• public function prep_Select3($table, $whereOrder, $columns)
  • Builds prep statement for retrieving data from a database table
    
    
  • Returns: None. Sets the value of $prep_Statement for a SELECT query
    
    
  • Parameters:
    • $table
      • Mandatory. Same function as prep_Select1() and prep_Select2()
    • $whereOrder
      • Mandatory. Can be an array, or a string
        
        
      • Depending on the type of this parameter, this method will build out a SELECT query with either a WHERE statement or an ORDER BY statement.
        • Arrays will be fed to the where_Helper() method to produce a WHERE statement
          
          
        • Strings simply contain the ORDER BY statement whole and are added to the query
          
          
    • $columns
      • Mandatory. Can be an array or string. Same function as prep_Select2().

• public function prep_Select4($table, $where, $order, $columns)
  • Builds prep statement for retrieving data from a database table
    
    
  • Returns: None. Sets the value of $prep_Statement for a SELECT query
    
    
  • Parameters:
    • $table
      • Mandatory. Same function as prep_Select1(), prep_Select2(), and prep_Select3()
    • $where
      • Mandatory. Must be an array
      • Fed to the where_Helper() method to produce a WHERE statement
        
        
    • $order
      • Mandatory. Must be a string
      • Simply contains an ORDER BY statement and is added to the query
        
        
    • $columns
      • Mandatory. Can be an array or string. Same function as prep_Select2() and prep_Select3()

• private function where_Helper($where = array())
  • Helper function to build out the WHERE keyword section of the prep statement. Used by every method that takes a $where[] array as a parameter and loops through the indices, building out chunks of the prep statement and attaching them to parameters

• public function prep_Insert($table, $columns = array())
  • Builds prep statement for inserting data into the database
    
    
  • Returns: None. Sets the value of $prep_Statement
    
    
  • Parameters
    • $table
      • Mandatory
      • Sets which table to retrieve from in the database
        
        
    • $columns
      • Mandatory
      • Defines which table columns to post data to
      • Looped through, each index building a chunk of the prep statement matched to a parameter. Similar to where_Helper()

• public function prep_Update($table, $columns, $where)
  • Builds prep statement for updating data into the database. Works almost exactly the same as prep_Insert, except for adding the WHERE selectors onto it, and formatting for an update prepared statement instead of an insert
    
    
  • Returns: None. Sets the value of $prep_Statement
    
    
  • Parameters
    • $table
      • Mandatory
      • Sets which table to retrieve from in the database
        
        
    • $columns
      • Mandatory
      • Defines which table columns to post data to
      • Must be an array
      • Looped through, each index building a chunk of the prep statement matched to a parameter. Similar to where_Helper()
        
        
    • $where
      • Mandatory
      • Must be an array
      • Each index either corresponds to the title of a column, or is a brief comparative such as

        • columnName LIKE

        • columnName NOT LIKE

•  public function prep_Delete($table, $column)
  • Takes the name of a table and a single column title, and builds a prep statement for removing that row from the database
    
    
  • Returns: None. Sets the value of $prep_Statement
    
    
  • Not very dynamic. Tentative plans to make a more substantial & discriminating delete method, but for now, this is all that's needed.

•  public function bind_Prep($types, $values = array())
  • Takes the $prep_Statement we've been building and binds the parameters
    
    
  • Returns: None. Just runs bind_param() on $prep_Statement using the method's parameters
    
    
  • Parameters
    • $types
      • Mandatory
      • Data type indicator string for bind_param's first parameter
        • For instance, if the you're adding a string, integer, double, and BLOB (very unlikely, but w/e), $types would be:

          • sidb

            • s - string
            • i - integer
            • d - double
            • b - blob
              
              
          • If you're familiar with prepared statements, you know the drill
            
            
    • $values
      • Mandatory
      • Must be an array
      • Each index is a value that corresponds with a column / parameter pair constructed by
        • prep_Select()
        • prep_Update()
        • where_Helper()
          
          
  • Additional notes
    • This is usually where you find out if your script is working or not. I scripted in an exception block here, and in prep_Update(), with plans to go back to all the methods that build out the prep statement.
      
      
    • It is instrumental that each column assigned to a parameter by the framework is paired with a corresponding value IN THE CORRECT ORDER. Just like with vanilla prepared statements. The indices of the $values array must line up with the data types in the $types parameter, and the $types parameter must correspond to the column / parameter pairs built out by the framework in the order that the framework produced them.

•  public function execute_Prep()
  • Runs execute() on $prep_Statement
    
    
  • Returns: None. Just runs execute().

• public function retrieve_Prep($reset = true)
  • Runs get_result() on $prep_Statement, and stores it into an array
    
    
  • Returns: Array(s). Either an array of rows (2 dimensional), or a single row.
    • Detects if the multi dimensional array has a single entry, and by default, sets the object to be returned to its first index via reset()
      
      
  • Parameters
    • $reset
      • Optional
      • Defaults to boolean true
      • On implementation, if set to false, will not unpack a single indexed multi dimensional array into a single level array
        • This is necessary for framework-level operations that break if they don't get an array of rows
        • On the front, however, it's convenient not to have to unpack two levels when just retrieving a single row. Things like page content, SEO, etc.

• public function close_Prep()
  • Closes $prep_Statement using close()
    
    
  • Returns: None.

• public function debug_Prep()
  • Outputs $prep_Script
    
    
  • Returns: String. Specifically, $prep_Script.