Partnership ProgramCommunityContact us
Start FreeGet Pro

Lesson 3 – The MainWP Development Add-on – Database

Database

class-mainwp-development-db.php

What’s this Class for?

This class is where all the code for the Database management. We will need to add a few variables to this file & update the install() method to handle creating the needed DB tables for this add-on’s data to be stored. 

Namespaces

Each PHP file with a MainWP add-on will need a unique namespace added to the top of each file just under the opening.

<?php
namespace MainWP\Extensions\Development;

Class Declaration

Just like before you will want to rename your class declaration by replacing the word “Development” with your add-ons name – everything else may stay the same here:

<?php
class MainWP_Development_DB {
  /**
  * Static variable to hold the single instance of the class.
  *
  * @static
  *
  * @var mixed Default null
  */
  static $instance = null;
  public static $instance = null;
  /**
  * Get Instance
  *
  * Creates a public static instance of this class file.
  *
  * @static
  *
  * @return MainWP_Development_DB
  */
  public static function get_instance() {
    if ( null == self::$instance ) {
      self::$instance = new self();
    }
    return self::$instance;
	}
}

Variables

First, we add this variable to define the version of the DB. Basically, this will be used in the future for updating if needed.

<?php
private $db_version = '1.0';

Class Methods

__construct()

PHP allows developers to declare constructor methods for classes. Classes that have a constructor method call this method on each newly created object, so it is suitable for any initialization that the object may need before it is used.

This is where we will load all of our actions, and filters & Initiate the rest of our add-ons class files.

<?php
public function __construct() {
  global $wpdb;
  $this->table_prefix = $wpdb->prefix . 'mainwp_';
  $this->wpdb = &$wpdb;
}
?>

table_name()

This method is then added for the install() method to function as intended.

<?php
public function table_name( $suffix ) {
  return $this->table_prefix . $suffix;
}
?>

install()

This method installs the new Database table. We need to build the method that actually installs the needed DB tables for the Add-on to store its data. This method is only run during “Activation” of the add-on.

<?php
public function install() {
  global $wpdb;

  $currentVersion = get_option( 'mainwp_developer_db_version' );

  if ( version_compare( $currentVersion, $this->db_version, '>=' ) ) {
    return;
  }

  $charset_collate = $wpdb->get_charset_collate();

  $sql = array();

  $tbl = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
        name tinytext NOT NULL,
        text text NOT NULL,
        url varchar(55) DEFAULT '' NOT NULL,
        PRIMARY KEY  (id)
    ) $charset_collate;";
  
  $sql[] = $tbl;

  require_once ABSPATH . 'wp-admin/includes/upgrade.php';
  foreach ( $sql as $query ) {
    dbDelta( $query );
  }

  update_option( 'mainwp_pressable_db_version', $this->db_version );

  $this->check_update( $currentVersion );
}
?>

Database Actions & Utilities

use_mysqli()

This method grabs the mysqli_connect class instance.

<?php
public static function use_mysqli() {
  /** @var $wpdb wpdb */
  if ( ! function_exists( '\mysqli_connect' ) ) {
    return false;
  }

  global $wpdb;
  return ( $wpdb->dbh instanceof \mysqli );
}
?>

escape()

This method escapes sql.

<?php
protected function escape( $data ) {
  /** @var $wpdb wpdb */
  global $wpdb;
  if ( function_exists( '\esc_sql' ) ) {
    return \esc_sql( $data );
  } else {
    return $wpdb->escape( $data );
  }
}
?>

query()

This method fires off the _query() method and returns it’s data.

<?php
public function query( $sql ) {
  if ( null == $sql ) {
    return false; }
  /** @var $wpdb wpdb */
  global $wpdb;
  $result = @self::_query( $sql, $wpdb->dbh );

  if ( ! $result || ( @self::num_rows( $result ) == 0 ) ) {
    return false;
  }
  return $result;
}
?>

_query()

This method calls out to the mysqli_query class and returns data.

<?php
public static function _query( $query, $link ) {
  if ( self::use_mysqli() ) {
    return \mysqli_query( $link, $query );
  } else {
   return \mysql_query( $query, $link );
  }
}
?>

fetch_object()

This method calls out to the mysqli_fetch_object class and returns an object.

<?php
public static function fetch_object( $result ) {
  if ( self::use_mysqli() ) {
    return \mysqli_fetch_object( $result );
  } else {
    return \mysql_fetch_object( $result );
  }
}
?>

free_result()

This method fetches the rows from a result-set, then frees the memory associated with the result.

<?php
public static function free_result( $result ) {
  if ( self::use_mysqli() ) {
    return \mysqli_free_result( $result );
  } else {
    return \mysql_free_result( $result );
  }
}
?>

data_seek()

The mysqli_data_seek() function adjusts the result pointer to an arbitrary row in the result-set.

<?php
public static function data_seek( $result, $offset ) {
  if ( self::use_mysqli() ) {
    return \mysqli_data_seek( $result, $offset );
  } else {
    return \mysql_data_seek( $result, $offset );
  }
}
?>

fetch_array()

This method fetches a result row as a numeric array and as an associative array.

<?php
public static function fetch_array( $result, $result_type = null ) {
  if ( self::use_mysqli() ) {
    return \mysqli_fetch_array( $result, ( null == $result_type ? MYSQLI_BOTH : $result_type ) );
  } else {
    return \mysql_fetch_array( $result, ( null == $result_type ? MYSQL_BOTH : $result_type ) );
  }
}
?>

num_rows()

This method returns the number of rows in a result set.

<?php
public static function num_rows( $result ) {
  if ( self::use_mysqli() ) {
    return \mysqli_num_rows( $result );
  } else {
    return \mysql_num_rows( $result );
  }
}
?>

is_result()

This method returns the result set obtained from a query against the database.

<?php
public static function is_result( $result ) {
  if ( self::use_mysqli() ) {
    return ( $result instanceof \mysqli_result );
  } else {
    return \is_resource( $result );
  }
}
?>

get_results_result()

This method retrieves an entire SQL result set from the database.

<?php
public function get_results_result( $sql ) {
  if ( null == $sql ) {
   return null;
  }
  /** @var $wpdb wpdb */
  global $wpdb;
  return $wpdb->get_results( $sql, OBJECT_K );
}
?>