mysql database backup

This is a function that backs up the structure and data of a MySQL database and represents them as a large text file of SQL statements. Doesn't require special disk access permissions, it pulls everything through the connection handle itself. Output can be sent transfered via FTP (i.e. by transfering the text file) or by HTTP (i.e. by direct output to the invoking browser).

MySQL Connection Update

DO NOT CONNECT TO MYSQL using mysql_* functions any longer as it will be or is deprecated. Use the mysqli class, see http://php.net/manual/en/mysql.php AND please use PREPARED statements.

For example classes of using mysqli and prepared statements, see codes below.

MySQLi and Prepared Statement John Moris

From John Moris: http://www.johnmorrisonline.com/simple-php-class-prepared-statements-mysqli/

<?php
if ( !class_exists( 'DB' ) ) {
	class DB {
		public function __construct($user, $password, $database, $host = 'localhost') {
			$this->user = $user;
			$this->password = $password;
			$this->database = $database;
			$this->host = $host;
		}
		protected function connect() {
			return new mysqli($this->host, $this->user, $this->password, $this->database);
		}
		public function query($query) {
			$db = $this->connect();
			$result = $db->query($query);
 
			while ( $row = $result->fetch_object() ) {
				$results[] = $row;
			}
 
			return $results;
		}
		public function insert($table, $data, $format) {
			// Check for $table or $data not set
			if ( empty( $table ) || empty( $data ) ) {
				return false;
			}
 
			// Connect to the database
			$db = $this->connect();
 
			// Cast $data and $format to arrays
			$data = (array) $data;
			$format = (array) $format;
 
			// Build format string
			$format = implode('', $format); 
			$format = str_replace('%', '', $format);
 
			list( $fields, $placeholders, $values ) = $this->prep_query($data);
 
			// Prepend $format onto $values
			array_unshift($values, $format); 
 
			// Prepary our query for binding
			$stmt = $db->prepare("INSERT INTO {$table} ({$fields}) VALUES ({$placeholders})");
 
			// Dynamically bind values
			call_user_func_array( array( $stmt, 'bind_param'), $this->ref_values($values));
 
			// Execute the query
			$stmt->execute();
 
			// Check for successful insertion
			if ( $stmt->affected_rows ) {
				return true;
			}
 
			return false;
		}
		public function update($table, $data, $format, $where, $where_format) {
			// Check for $table or $data not set
			if ( empty( $table ) || empty( $data ) ) {
				return false;
			}
 
			// Connect to the database
			$db = $this->connect();
 
			// Cast $data and $format to arrays
			$data = (array) $data;
			$format = (array) $format;
 
			// Build format array
			$format = implode('', $format); 
			$format = str_replace('%', '', $format);
			$where_format = implode('', $where_format); 
			$where_format = str_replace('%', '', $where_format);
			$format .= $where_format;
 
			list( $fields, $placeholders, $values ) = $this->prep_query($data, 'update');
 
			//Format where clause
			$where_clause = '';
			$where_values = '';
			$count = 0;
 
			foreach ( $where as $field => $value ) {
				if ( $count > 0 ) {
					$where_clause .= ' AND ';
				}
 
				$where_clause .= $field . '=?';
				$where_values[] = $value;
 
				$count++;
			}
 
			// Prepend $format onto $values
			array_unshift($values, $format);
			$values = array_merge($values, $where_values);
 
			// Prepary our query for binding
			$stmt = $db->prepare("UPDATE {$table} SET {$placeholders} WHERE {$where_clause}");
 
			// Dynamically bind values
			call_user_func_array( array( $stmt, 'bind_param'), $this->ref_values($values));
 
			// Execute the query
			$stmt->execute();
 
			// Check for successful insertion
			if ( $stmt->affected_rows ) {
				return true;
			}
 
			return false;
		}
		public function select($query, $data, $format) {
			// Connect to the database
			$db = $this->connect();
 
			//Prepare our query for binding
			$stmt = $db->prepare($query);
 
			//Normalize format
			$format = implode('', $format); 
			$format = str_replace('%', '', $format);
 
			// Prepend $format onto $values
			array_unshift($data, $format);
 
			//Dynamically bind values
			call_user_func_array( array( $stmt, 'bind_param'), $this->ref_values($data));
 
			//Execute the query
			$stmt->execute();
 
			//Fetch results
			$result = $stmt->get_result();
 
			//Create results object
			while ($row = $result->fetch_object()) {
				$results[] = $row;
			}
 
			return $results;
		}
		public function delete($table, $id) {
			// Connect to the database
			$db = $this->connect();
 
			// Prepary our query for binding
			$stmt = $db->prepare("DELETE FROM {$table} WHERE ID = ?");
 
			// Dynamically bind values
			$stmt->bind_param('d', $id);
 
			// Execute the query
			$stmt->execute();
 
			// Check for successful insertion
			if ( $stmt->affected_rows ) {
				return true;
			}
		}
		private function prep_query($data, $type='insert') {
			// Instantiate $fields and $placeholders for looping
			$fields = '';
			$placeholders = '';
			$values = array();
 
			// Loop through $data and build $fields, $placeholders, and $values			
			foreach ( $data as $field => $value ) {
				$fields .= "{$field},";
				$values[] = $value;
 
				if ( $type == 'update') {
					$placeholders .= $field . '=?,';
				} else {
					$placeholders .= '?,';
				}
 
			}
 
			// Normalize $fields and $placeholders for inserting
			$fields = substr($fields, 0, -1);
			$placeholders = substr($placeholders, 0, -1);
 
			return array( $fields, $placeholders, $values );
		}
		private function ref_values($array) {
			$refs = array();
 
			foreach ($array as $key => $value) {
				$refs[$key] = &$array[$key]; 
			}
 
			return $refs; 
		}
	}
}
 
$db = new DB('root', '', 'test');
print_r($db->select('SELECT * FROM objects WHERE ID = ?', array(10), array('%d')));

MySQLi and Prepared Statement by Josh Cam

Or see code from PHP-MySQLi-Database-Class https://github.com/joshcam/PHP-MySQLi-Database-Class/blob/master/MysqliDb.php

<?php
/**
 * MysqliDb Class
 *
 * @category  Database Access
 * @package   MysqliDb
 * @author    Jeffery Way <jeffrey@jeffrey-way.com>
 * @author    Josh Campbell <jcampbell@ajillion.com>
 * @author    Alexander V. Butenko <a.butenka@gmail.com>
 * @copyright Copyright (c) 2010
 * @license   http://opensource.org/licenses/gpl-3.0.html GNU Public License
 * @version   2.0
 **/
class MysqliDb
{
    /**
     * Static instance of self
     *
     * @var MysqliDb
     */
    protected static $_instance;
    /**
     * Table prefix
     * 
     * @var string
     */
    protected static $_prefix;
    /**
     * MySQLi instance
     *
     * @var mysqli
     */
    protected $_mysqli;
    /**
     * The SQL query to be prepared and executed
     *
     * @var string
     */
    protected $_query;
    /**
     * The previously executed SQL query
     *
     * @var string
     */
    protected $_lastQuery;
    /**
     * An array that holds where joins
     *
     * @var array
     */
    protected $_join = array(); 
    /**
     * An array that holds where conditions 'fieldname' => 'value'
     *
     * @var array
     */
    protected $_where = array();
    /**
     * Dynamic type list for order by condition value
     */
    protected $_orderBy = array(); 
    /**
     * Dynamic type list for group by condition value
     */
    protected $_groupBy = array(); 
    /**
     * Dynamic array that holds a combination of where condition/table data value types and parameter referances
     *
     * @var array
     */
    protected $_bindParams = array(''); // Create the empty 0 index
    /**
     * Variable which holds an amount of returned rows during get/getOne/select queries
     *
     * @var string
     */ 
    public $count = 0;
    /**
     * Variable which holds last statement error
     *
     * @var string
     */
    protected $_stmtError;
 
    /**
     * Database credentials
     *
     * @var string
     */
    protected $host;
    protected $username;
    protected $password;
    protected $db;
    protected $port;
    protected $charset;
 
    /**
     * Is Subquery object
     *
     */
    protected $isSubQuery = false;
 
    /**
     * @param string $host
     * @param string $username
     * @param string $password
     * @param string $db
     * @param int $port
     */
    public function __construct($host = NULL, $username = NULL, $password = NULL, $db = NULL, $port = NULL, $charset = 'utf8')
    {
        $isSubQuery = false;
 
        // if params were passed as array
        if (is_array ($host)) {
            foreach ($host as $key => $val)
                $$key = $val;
        }
        // if host were set as mysqli socket
        if (is_object ($host))
            $this->_mysqli = $host;
        else
            $this->host = $host;
 
        $this->username = $username;
        $this->password = $password;
        $this->db = $db;
        $this->port = $port;
        $this->charset = $charset;
 
        if ($isSubQuery) {
            $this->isSubQuery = true;
            return;
        }
 
        // for subqueries we do not need database connection and redefine root instance
        if (!is_object ($host))
            $this->connect();
 
        $this->setPrefix();
        self::$_instance = $this;
    }
 
    /**
     * A method to connect to the database
     *
     */
    public function connect()
    {
        if ($this->isSubQuery)
            return;
 
        if (empty ($this->host))
            die ('Mysql host is not set');
 
        $this->_mysqli = new mysqli ($this->host, $this->username, $this->password, $this->db, $this->port)
            or die('There was a problem connecting to the database');
 
        if ($this->charset)
            $this->_mysqli->set_charset ($this->charset);
    }
    /**
     * A method of returning the static instance to allow access to the
     * instantiated object from within another class.
     * Inheriting this class would require reloading connection info.
     *
     * @uses $db = MySqliDb::getInstance();
     *
     * @return object Returns the current instance.
     */
    public static function getInstance()
    {
        return self::$_instance;
    }
 
    /**
     * Reset states after an execution
     *
     * @return object Returns the current instance.
     */
    protected function reset()
    {
        $this->_where = array();
        $this->_join = array();
        $this->_orderBy = array();
        $this->_groupBy = array(); 
        $this->_bindParams = array(''); // Create the empty 0 index
        $this->_query = null;
        $this->count = 0;
    }
 
    /**
     * Method to set a prefix
     * 
     * @param string $prefix     Contains a tableprefix
     */
    public function setPrefix($prefix = '')
    {
        self::$_prefix = $prefix;
        return $this;
    }
 
    /**
     * Pass in a raw query and an array containing the parameters to bind to the prepaird statement.
     *
     * @param string $query      Contains a user-provided query.
     * @param array  $bindParams All variables to bind to the SQL statment.
     * @param bool   $sanitize   If query should be filtered before execution
     *
     * @return array Contains the returned rows from the query.
     */
    public function rawQuery ($query, $bindParams = null, $sanitize = true)
    {
        $this->_query = $query;
        if ($sanitize)
            $this->_query = filter_var ($query, FILTER_SANITIZE_STRING,
                                    FILTER_FLAG_NO_ENCODE_QUOTES);
        $stmt = $this->_prepareQuery();
 
        if (is_array($bindParams) === true) {
            $params = array(''); // Create the empty 0 index
            foreach ($bindParams as $prop => $val) {
                $params[0] .= $this->_determineType($val);
                array_push($params, $bindParams[$prop]);
            }
 
            call_user_func_array(array($stmt, 'bind_param'), $this->refValues($params));
 
        }
 
        $stmt->execute();
        $this->_stmtError = $stmt->error;
        $this->reset();
 
        return $this->_dynamicBindResults($stmt);
    }
 
    /**
     *
     * @param string $query   Contains a user-provided select query.
     * @param int    $numRows The number of rows total to return.
     *
     * @return array Contains the returned rows from the query.
     */
    public function query($query, $numRows = null)
    {
        $this->_query = filter_var($query, FILTER_SANITIZE_STRING);
        $stmt = $this->_buildQuery($numRows);
        $stmt->execute();
        $this->_stmtError = $stmt->error;
        $this->reset();
 
        return $this->_dynamicBindResults($stmt);
    }
 
    /**
     * A convenient SELECT * function.
     *
     * @param string  $tableName The name of the database table to work with.
     * @param integer $numRows   The number of rows total to return.
     *
     * @return array Contains the returned rows from the select query.
     */
    public function get($tableName, $numRows = null, $columns = '*')
    {
        if (empty ($columns))
            $columns = '*';
 
        $column = is_array($columns) ? implode(', ', $columns) : $columns; 
        $this->_query = "SELECT $column FROM " . self::$_prefix . $tableName;
        $stmt = $this->_buildQuery($numRows);
 
        if ($this->isSubQuery)
            return $this;
 
        $stmt->execute();
        $this->_stmtError = $stmt->error;
        $this->reset();
 
        return $this->_dynamicBindResults($stmt);
    }
 
    /**
     * A convenient SELECT * function to get one record.
     *
     * @param string  $tableName The name of the database table to work with.
     *
     * @return array Contains the returned rows from the select query.
     */
    public function getOne($tableName, $columns = '*') 
    {
        $res = $this->get ($tableName, 1, $columns);
 
        if (is_object($res))
            return $res;
 
        if (isset($res[0]))
            return $res[0];
 
        return null;
    }
 
    /**
     * A convenient SELECT * function to get one value.
     *
     * @param string  $tableName The name of the database table to work with.
     *
     * @return array Contains the returned column from the select query.
     */
    public function getValue($tableName, $column) 
    {
        $res = $this->get ($tableName, 1, "{$column} as retval");
 
        if (isset($res[0]["retval"]))
            return $res[0]["retval"];
 
        return null;
    }
 
    /**
     *
     * @param <string $tableName The name of the table.
     * @param array $insertData Data containing information for inserting into the DB.
     *
     * @return boolean Boolean indicating whether the insert query was completed succesfully.
     */
    public function insert($tableName, $insertData)
    {
        if ($this->isSubQuery)
            return;
 
        $this->_query = "INSERT into " .self::$_prefix . $tableName;
        $stmt = $this->_buildQuery(null, $insertData);
        $stmt->execute();
        $this->_stmtError = $stmt->error;
        $this->reset();
        $this->count = $stmt->affected_rows;
 
        if ($stmt->affected_rows < 1)
            return false;
 
        if ($stmt->insert_id > 0)
            return $stmt->insert_id;
 
        return true;
    }
 
    /**
     * A convenient function that returns TRUE if exists at least an element that
     * satisfy the where condition specified calling the "where" method before this one.
     *
     * @param string  $tableName The name of the database table to work with.
     *
     * @return array Contains the returned rows from the select query.
     */
    public function has($tableName)
    {
        $this->getOne($tableName, '1');
        return $this->count >= 1;
    }
 
    /**
     * Update query. Be sure to first call the "where" method.
     *
     * @param string $tableName The name of the database table to work with.
     * @param array  $tableData Array of data to update the desired row.
     *
     * @return boolean
     */
    public function update($tableName, $tableData)
    {
        if ($this->isSubQuery)
            return;
 
        $this->_query = "UPDATE " . self::$_prefix . $tableName ." SET ";
 
        $stmt = $this->_buildQuery (null, $tableData);
        $status = $stmt->execute();
        $this->reset();
        $this->_stmtError = $stmt->error;
        $this->count = $stmt->affected_rows;
 
        return $status;
    }
 
    /**
     * Delete query. Call the "where" method first.
     *
     * @param string  $tableName The name of the database table to work with.
     * @param integer $numRows   The number of rows to delete.
     *
     * @return boolean Indicates success. 0 or 1.
     */
    public function delete($tableName, $numRows = null)
    {
        if ($this->isSubQuery)
            return;
 
        $this->_query = "DELETE FROM " . self::$_prefix . $tableName;
 
        $stmt = $this->_buildQuery($numRows);
        $stmt->execute();
        $this->_stmtError = $stmt->error;
        $this->reset();
 
        return ($stmt->affected_rows > 0);
    }
 
    /**
     * This method allows you to specify multiple (method chaining optional) AND WHERE statements for SQL queries.
     *
     * @uses $MySqliDb->where('id', 7)->where('title', 'MyTitle');
     *
     * @param string $whereProp  The name of the database field.
     * @param mixed  $whereValue The value of the database field.
     *
     * @return MysqliDb
     */
    public function where($whereProp, $whereValue = null, $operator = null)
    {
        if ($operator)
            $whereValue = Array ($operator => $whereValue);
 
        $this->_where[] = Array ("AND", $whereValue, $whereProp);
        return $this;
    }
 
    /**
     * This method allows you to specify multiple (method chaining optional) OR WHERE statements for SQL queries.
     *
     * @uses $MySqliDb->orWhere('id', 7)->orWhere('title', 'MyTitle');
     *
     * @param string $whereProp  The name of the database field.
     * @param mixed  $whereValue The value of the database field.
     *
     * @return MysqliDb
     */
    public function orWhere($whereProp, $whereValue = null, $operator = null)
    {
        if ($operator)
            $whereValue = Array ($operator => $whereValue);
 
        $this->_where[] = Array ("OR", $whereValue, $whereProp);
        return $this;
    }
    /**
     * This method allows you to concatenate joins for the final SQL statement.
     *
     * @uses $MySqliDb->join('table1', 'field1 <> field2', 'LEFT')
     *
     * @param string $joinTable The name of the table.
     * @param string $joinCondition the condition.
     * @param string $joinType 'LEFT', 'INNER' etc.
     *
     * @return MysqliDb
     */
     public function join($joinTable, $joinCondition, $joinType = '')
     {
        $allowedTypes = array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER');
        $joinType = strtoupper (trim ($joinType));
 
        if ($joinType && !in_array ($joinType, $allowedTypes))
            die ('Wrong JOIN type: '.$joinType);
 
        if (!is_object ($joinTable))
            $joinTable = self::$_prefix . filter_var($joinTable, FILTER_SANITIZE_STRING);
 
        $this->_join[] = Array ($joinType,  $joinTable, $joinCondition);
 
        return $this;
    }
    /**
     * This method allows you to specify multiple (method chaining optional) ORDER BY statements for SQL queries.
     *
     * @uses $MySqliDb->orderBy('id', 'desc')->orderBy('name', 'desc');
     *
     * @param string $orderByField The name of the database field.
     * @param string $orderByDirection Order direction.
     *
     * @return MysqliDb
     */
    public function orderBy($orderByField, $orderbyDirection = "DESC", $customFields = null)
    {
        $allowedDirection = Array ("ASC", "DESC");
        $orderbyDirection = strtoupper (trim ($orderbyDirection));
        $orderByField = preg_replace ("/[^-a-z0-9\.\(\),_]+/i",'', $orderByField);
 
        if (empty($orderbyDirection) || !in_array ($orderbyDirection, $allowedDirection))
            die ('Wrong order direction: '.$orderbyDirection);
 
        if (is_array ($customFields)) {
            foreach ($customFields as $key => $value)
                $customFields[$key] = preg_replace ("/[^-a-z0-9\.\(\),_]+/i",'', $value);
 
            $orderByField = 'FIELD (' . $orderByField . ', "' . implode('","', $customFields) . '")';
        }
 
        $this->_orderBy[$orderByField] = $orderbyDirection;
        return $this;
    } 
 
    /**
     * This method allows you to specify multiple (method chaining optional) GROUP BY statements for SQL queries.
     *
     * @uses $MySqliDb->groupBy('name');
     *
     * @param string $groupByField The name of the database field.
     *
     * @return MysqliDb
     */
    public function groupBy($groupByField)
    {
        $groupByField = preg_replace ("/[^-a-z0-9\.\(\),_]+/i",'', $groupByField);
 
        $this->_groupBy[] = $groupByField;
        return $this;
    } 
 
    /**
     * This methods returns the ID of the last inserted item
     *
     * @return integer The last inserted item ID.
     */
    public function getInsertId()
    {
        return $this->_mysqli->insert_id;
    }
 
    /**
     * Escape harmful characters which might affect a query.
     *
     * @param string $str The string to escape.
     *
     * @return string The escaped string.
     */
    public function escape($str)
    {
        return $this->_mysqli->real_escape_string($str);
    }
 
    /**
     * Method to call mysqli->ping() to keep unused connections open on
     * long-running scripts, or to reconnect timed out connections (if php.ini has
     * global mysqli.reconnect set to true). Can't do this directly using object
     * since _mysqli is protected.
     *
     * @return bool True if connection is up
     */
    public function ping() {
        return $this->_mysqli->ping();
    }
 
    /**
     * This method is needed for prepared statements. They require
     * the data type of the field to be bound with "i" s", etc.
     * This function takes the input, determines what type it is,
     * and then updates the param_type.
     *
     * @param mixed $item Input to determine the type.
     *
     * @return string The joined parameter types.
     */
    protected function _determineType($item)
    {
        switch (gettype($item)) {
            case 'NULL':
            case 'string':
                return 's';
                break;
 
            case 'boolean':
            case 'integer':
                return 'i';
                break;
 
            case 'blob':
                return 'b';
                break;
 
            case 'double':
                return 'd';
                break;
        }
        return '';
    }
 
    /**
     * Helper function to add variables into bind parameters array
     *
     * @param string Variable value
     */
    protected function _bindParam($value) {
        $this->_bindParams[0] .= $this->_determineType ($value);
        array_push ($this->_bindParams, $value);
    }
 
    /**
     * Helper function to add variables into bind parameters array in bulk
     *
     * @param Array Variable with values
     */
    protected function _bindParams ($values) {
        foreach ($values as $value)
            $this->_bindParam ($value);
    }
 
    /**
     * Helper function to add variables into bind parameters array and will return
     * its SQL part of the query according to operator in ' $operator ?' or
     * ' $operator ($subquery) ' formats
     *
     * @param Array Variable with values
     */
    protected function _buildPair ($operator, $value) {
        if (!is_object($value)) {
            $this->_bindParam ($value);
            return ' ' . $operator. ' ? ';
        }
 
        $subQuery = $value->getSubQuery ();
        $this->_bindParams ($subQuery['params']);
 
        return " " . $operator . " (" . $subQuery['query'] . ") " . $subQuery['alias'];
    }
 
    /**
     * Abstraction method that will compile the WHERE statement,
     * any passed update data, and the desired rows.
     * It then builds the SQL query.
     *
     * @param int   $numRows   The number of rows total to return.
     * @param array $tableData Should contain an array of data for updating the database.
     *
     * @return mysqli_stmt Returns the $stmt object.
     */
    protected function _buildQuery($numRows = null, $tableData = null)
    {
        $this->_buildJoin();
        $this->_buildTableData ($tableData);
        $this->_buildWhere();
        $this->_buildGroupBy();
        $this->_buildOrderBy();
        $this->_buildLimit ($numRows);
 
        $this->_lastQuery = $this->replacePlaceHolders ($this->_query, $this->_bindParams);
 
        if ($this->isSubQuery)
            return;
 
        // Prepare query
        $stmt = $this->_prepareQuery();
 
        // Bind parameters to statement if any
        if (count ($this->_bindParams) > 1)
            call_user_func_array(array($stmt, 'bind_param'), $this->refValues($this->_bindParams));
 
        return $stmt;
    }
 
    /**
     * This helper method takes care of prepared statements' "bind_result method
     * , when the number of variables to pass is unknown.
     *
     * @param mysqli_stmt $stmt Equal to the prepared statement object.
     *
     * @return array The results of the SQL fetch.
     */
    protected function _dynamicBindResults(mysqli_stmt $stmt)
    {
        $parameters = array();
        $results = array();
 
        $meta = $stmt->result_metadata();
 
        // if $meta is false yet sqlstate is true, there's no sql error but the query is
        // most likely an update/insert/delete which doesn't produce any results
        if(!$meta && $stmt->sqlstate) { 
            return array();
        }
 
        $row = array();
        while ($field = $meta->fetch_field()) {
            $row[$field->name] = null;
            $parameters[] = & $row[$field->name];
        }
 
        // avoid out of memory bug in php 5.2 and 5.3
        // https://github.com/joshcam/PHP-MySQLi-Database-Class/pull/119
        if (version_compare (phpversion(), '5.4', '<'))
             $stmt->store_result();
 
        call_user_func_array(array($stmt, 'bind_result'), $parameters);
 
        $this->count = 0;
        while ($stmt->fetch()) {
            $x = array();
            foreach ($row as $key => $val) {
                $x[$key] = $val;
            }
            $this->count++;
            array_push($results, $x);
        }
 
        return $results;
    }
 
 
    /**
     * Abstraction method that will build an JOIN part of the query
     */
    protected function _buildJoin () {
        if (empty ($this->_join))
            return;
 
        foreach ($this->_join as $data) {
            list ($joinType,  $joinTable, $joinCondition) = $data;
 
            if (is_object ($joinTable))
                $joinStr = $this->_buildPair ("", $joinTable);
            else
                $joinStr = $joinTable;
 
            $this->_query .= " " . $joinType. " JOIN " . $joinStr ." on " . $joinCondition;
        }
    }
 
    /**
     * Abstraction method that will build an INSERT or UPDATE part of the query
     */
    protected function _buildTableData ($tableData) {
        if (!is_array ($tableData))
            return;
 
        $isInsert = strpos ($this->_query, 'INSERT');
        $isUpdate = strpos ($this->_query, 'UPDATE');
 
        if ($isInsert !== false) {
            $this->_query .= '(`' . implode(array_keys($tableData), '`, `') . '`)';
            $this->_query .= ' VALUES(';
        }
 
        foreach ($tableData as $column => $value) {
            if ($isUpdate !== false)
                $this->_query .= "`" . $column . "` = ";
 
            // Subquery value
            if (is_object ($value)) {
                $this->_query .= $this->_buildPair ("", $value) . ", ";
                continue;
            }
 
            // Simple value
            if (!is_array ($value)) {
                $this->_bindParam ($value);
                $this->_query .= '?, ';
                continue;
            }
 
            // Function value
            $key = key ($value);
            $val = $value[$key];
            switch ($key) {
                case '[I]':
                    $this->_query .= $column . $val . ", ";
                    break;
                case '[F]':
                    $this->_query .= $val[0] . ", ";
                    if (!empty ($val[1]))
                        $this->_bindParams ($val[1]);
                    break;
                case '[N]':
                    if ($val == null)
                        $this->_query .= "!" . $column . ", ";
                    else
                        $this->_query .= "!" . $val . ", ";
                    break;
                default:
                    die ("Wrong operation");
            }
        }
        $this->_query = rtrim($this->_query, ', ');
        if ($isInsert !== false)
            $this->_query .= ')';
    }
 
    /**
     * Abstraction method that will build the part of the WHERE conditions
     */
    protected function _buildWhere () {
        if (empty ($this->_where))
            return;
 
        //Prepair the where portion of the query
        $this->_query .= ' WHERE ';
 
        // Remove first AND/OR concatenator
        $this->_where[0][0] = '';
        foreach ($this->_where as $cond) {
            list ($concat, $wValue, $wKey) = $cond;
 
            $this->_query .= " " . $concat ." " . $wKey;
 
            // Empty value (raw where condition in wKey)
            if ($wValue === null)
                continue;
 
            // Simple = comparison
            if (!is_array ($wValue))
                $wValue = Array ('=' => $wValue);
 
            $key = key ($wValue);
            $val = $wValue[$key];
            switch (strtolower ($key)) {
                case '0':
                    $this->_bindParams ($wValue);
                    break;
                case 'not in':
                case 'in':
                    $comparison = ' ' . $key . ' (';
                    if (is_object ($val)) {
                        $comparison .= $this->_buildPair ("", $val);
                    } else {
                        foreach ($val as $v) {
                            $comparison .= ' ?,';
                            $this->_bindParam ($v);
                        }
                    }
                    $this->_query .= rtrim($comparison, ',').' ) ';
                    break;
                case 'not between':
                case 'between':
                    $this->_query .= " $key ? AND ? ";
                    $this->_bindParams ($val);
                    break;
                case 'not exists':
                case 'exists':
                    $this->_query.= $key . $this->_buildPair ("", $val);
                    break;
                default:
                    $this->_query .= $this->_buildPair ($key, $val);
            }
        }
    }
 
    /**
     * Abstraction method that will build the GROUP BY part of the WHERE statement
     *
     */
    protected function _buildGroupBy () {
        if (empty ($this->_groupBy))
            return;
 
        $this->_query .= " GROUP BY ";
        foreach ($this->_groupBy as $key => $value)
            $this->_query .= $value . ", ";
 
        $this->_query = rtrim($this->_query, ', ') . " ";
    }
 
    /**
     * Abstraction method that will build the LIMIT part of the WHERE statement
     *
     * @param int   $numRows   The number of rows total to return.
     */
    protected function _buildOrderBy () {
        if (empty ($this->_orderBy))
            return;
 
        $this->_query .= " ORDER BY ";
        foreach ($this->_orderBy as $prop => $value) {
            if (strtolower (str_replace (" ", "", $prop)) == 'rand()')
                $this->_query .= "rand(), ";
            else
                $this->_query .= $prop . " " . $value . ", ";
        }
 
        $this->_query = rtrim ($this->_query, ', ') . " ";
    }
 
    /**
     * Abstraction method that will build the LIMIT part of the WHERE statement
     *
     * @param int   $numRows   The number of rows total to return.
     */
    protected function _buildLimit ($numRows) {
        if (!isset ($numRows))
            return;
 
        if (is_array ($numRows))
            $this->_query .= ' LIMIT ' . (int)$numRows[0] . ', ' . (int)$numRows[1];
        else
            $this->_query .= ' LIMIT ' . (int)$numRows;
    }
 
    /**
     * Method attempts to prepare the SQL query
     * and throws an error if there was a problem.
     *
     * @return mysqli_stmt
     */
    protected function _prepareQuery()
    {
        if (!$stmt = $this->_mysqli->prepare($this->_query)) {
            trigger_error("Problem preparing query ($this->_query) " . $this->_mysqli->error, E_USER_ERROR);
        }
        return $stmt;
    }
 
    /**
     * Close connection
     */
    public function __destruct()
    {
        if (!$this->isSubQuery)
            return;
        if ($this->_mysqli)
            $this->_mysqli->close();
    }
 
    /**
     * @param array $arr
     *
     * @return array
     */
    protected function refValues($arr)
    {
        //Reference is required for PHP 5.3+
        if (strnatcmp(phpversion(), '5.3') >= 0) {
            $refs = array();
            foreach ($arr as $key => $value) {
                $refs[$key] = & $arr[$key];
            }
            return $refs;
        }
        return $arr;
    }
 
    /**
     * Function to replace ? with variables from bind variable
     * @param string $str
     * @param Array $vals
     *
     * @return string
     */
    protected function replacePlaceHolders ($str, $vals) {
        $i = 1;
        $newStr = "";
 
        while ($pos = strpos ($str, "?")) {
            $val = $vals[$i++];
            if (is_object ($val))
                $val = '[object]';
            $newStr .= substr ($str, 0, $pos) . $val;
            $str = substr ($str, $pos + 1);
        }
        $newStr .= $str;
        return $newStr;
    }
 
    /**
     * Method returns last executed query
     *
     * @return string
     */
    public function getLastQuery () {
        return $this->_lastQuery;
    }
 
    /**
     * Method returns mysql error
     * 
     * @return string
     */
    public function getLastError () {
        return trim ($this->_stmtError . " " . $this->_mysqli->error);
    }
 
    /**
     * Mostly internal method to get query and its params out of subquery object
     * after get() and getAll()
     * 
     * @return array
     */
    public function getSubQuery () {
        if (!$this->isSubQuery)
            return null;
 
        array_shift ($this->_bindParams);
        $val = Array ('query' => $this->_query,
                      'params' => $this->_bindParams,
                      'alias' => $this->host
                );
        $this->reset();
        return $val;
    }
 
    /* Helper functions */
    /**
     * Method returns generated interval function as a string
     *
     * @param string interval in the formats:
     *        "1", "-1d" or "- 1 day" -- For interval - 1 day
     *        Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear
     *        Default null;
     * @param string Initial date
     *
     * @return string
    */
    public function interval ($diff, $func = "NOW()") {
        $types = Array ("s" => "second", "m" => "minute", "h" => "hour", "d" => "day", "M" => "month", "Y" => "year");
        $incr = '+';
        $items = '';
        $type = 'd';
 
        if ($diff && preg_match('/([+-]?) ?([0-9]+) ?([a-zA-Z]?)/',$diff, $matches)) {
            if (!empty ($matches[1])) $incr = $matches[1];
            if (!empty ($matches[2])) $items = $matches[2];
            if (!empty ($matches[3])) $type = $matches[3];
            if (!in_array($type, array_keys($types)))
                trigger_error ("invalid interval type in '{$diff}'");
            $func .= " ".$incr ." interval ". $items ." ".$types[$type] . " ";
        }
        return $func;
 
    }
    /**
     * Method returns generated interval function as an insert/update function
     *
     * @param string interval in the formats:
     *        "1", "-1d" or "- 1 day" -- For interval - 1 day
     *        Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear
     *        Default null;
     * @param string Initial date
     *
     * @return array
    */
    public function now ($diff = null, $func = "NOW()") {
        return Array ("[F]" => Array($this->interval($diff, $func)));
    }
 
    /**
     * Method generates incremental function call
     * @param int increment amount. 1 by default
     */
    public function inc($num = 1) {
        return Array ("[I]" => "+" . (int)$num);
    }
 
    /**
     * Method generates decrimental function call
     * @param int increment amount. 1 by default
     */
    public function dec ($num = 1) {
        return Array ("[I]" => "-" . (int)$num);
    }
 
    /**
     * Method generates change boolean function call
     * @param string column name. null by default
     */
    public function not ($col = null) {
        return Array ("[N]" => (string)$col);
    }
 
    /**
     * Method generates user defined function call
     * @param string user function body
     */
    public function func ($expr, $bindParams = null) {
        return Array ("[F]" => Array($expr, $bindParams));
    }
 
    /**
     * Method creates new mysqlidb object for a subquery generation
     */
    public static function subQuery($subQueryAlias = "")
    {
        return new MysqliDb (Array('host' => $subQueryAlias, 'isSubQuery' => true));
    }
 
    /**
     * Method returns a copy of a mysqlidb subquery object
     *
     * @param object new mysqlidb object
     */
    public function copy ()
    {
        $copy = unserialize (serialize ($this));
        $copy->_mysqli = $this->_mysqli;
        return $copy;
    }
 
    /**
     * Begin a transaction
     *
     * @uses mysqli->autocommit(false)
     * @uses register_shutdown_function(array($this, "_transaction_shutdown_check"))
     */
    public function startTransaction () {
        $this->_mysqli->autocommit (false);
        $this->_transaction_in_progress = true;
        register_shutdown_function (array ($this, "_transaction_status_check"));
    }
 
    /**
     * Transaction commit
     *
     * @uses mysqli->commit();
     * @uses mysqli->autocommit(true);
     */
    public function commit () {
        $this->_mysqli->commit ();
        $this->_transaction_in_progress = false;
        $this->_mysqli->autocommit (true);
    }
 
    /**
     * Transaction rollback function
     *
     * @uses mysqli->rollback();
     * @uses mysqli->autocommit(true);
     */
    public function rollback () {
      $this->_mysqli->rollback ();
      $this->_transaction_in_progress = false;
      $this->_mysqli->autocommit (true);
    }
 
    /**
     * Shutdown handler to rollback uncommited operations in order to keep
     * atomic operations sane.
     *
     * @uses mysqli->rollback();
     */
    public function _transaction_status_check () {
        if (!$this->_transaction_in_progress)
            return;
        $this->rollback ();
    }
} // END class
?>

Code 1

Usage Example

Copy the below function into a file called "mysqlbackup.h" and invoke it with the following script:
 
<html>
<?php
    include("mysqlbackup.h");
    mysqlbackup("localhost","yerdatabase","yerusername","yerpassword","/home/sites/site90/web/backup/sqldata.txt", true);
?>
 
The database's structure has been saved to "/home/sites/site90/web/backup/sqldata.txt" FTP download it at your convenience.
 
</html>

Server Side Code

<?
 
function mysqlbackup($host,$dbname, $uid, $pwd, $output, $structure_only)
{
 
    //this function creates a text file (or output to a HTTP connection), that when parsed through 
    // MYSQL's telnet client, will re-create the entire database
 
    //Parameters:
        //      $host: usually "localhost" but depends on where the MySQL database engine is mounted
    //    $dbname : The MySQL database name 
    //    $uid : the database's username (not your account's), leave blank if none is required
    //    $pwd : the database's password 
    //    $output : this is the complete filespec for the output text file, or if you want the result SQL to be sent back to the browser, leave blank.
    //    $structure_only : set this to true if you want just the schema of the database (not the actual data) to be output.
 
    // **************
    // IMPORTANT: If you use this function, for personal or commercial use, AND you feel an overwhelming sense of gratitude that someone actually took the time and wrote it,
    // immediately go to your paypal account and send me $10 with a small comment of how and how much it helped! Set the payment recipient to woodystanford@yahoo.com .
    // **************
 
    if (strval($output)!="") $fptr=fopen($output,"w"); else $fptr=false;
 
    //connect to MySQL database
    $con=mysql_connect("localhost",$uid, $pwd);
    $db=mysql_select_db($dbname,$con);
 
    //open back-up file ( or no file for browser output)
 
    //set up database
    out($fptr, "create database $dbname;nn");
 
    //enumerate tables
    $res=mysql_list_tables($dbname);
    $nt=mysql_num_rows($res);
 
    for ($a=0;$a<$nt;$a++)
    {
        $row=mysql_fetch_row($res);
        $tablename=$row[0];
 
        //start building the table creation query
        $sql="create table $tablenamen(n";
 
        $res2=mysql_query("select * from $tablename",$con);
        $nf=mysql_num_fields($res2);
        $nr=mysql_num_rows($res2);
 
        $fl="";
 
        //parse the field info first
        for ($b=0;$b<$nf;$b++)
        {
            $fn=mysql_field_name($res2,$b);
            $ft=mysql_fieldtype($res2,$b);
            $fs=mysql_field_len($res2,$b);
            $ff=mysql_field_flags($res2,$b);
 
            $sql.="    $fn ";
 
            $is_numeric=false;
            switch(strtolower($ft))
            {
                case "int":
                    $sql.="int";
                    $is_numeric=true;
                    break;
 
                case "blob":
                    $sql.="text";
                    $is_numeric=false;
                    break;
 
                case "real":
                    $sql.="real";
                    $is_numeric=true;
                    break;
 
                case "string":
                    $sql.="char($fs)";
                    $is_numeric=false;
                    break;
 
                case "unknown":
                    switch(intval($fs))
                    {
                        case 4:    //little weakness here...there is no way (thru the PHP/MySQL interface) 
                                   // to tell the difference between a tinyint and a year field type
                            $sql.="tinyint";
                            $is_numeric=true;
                            break;
 
                        default:    //we could get a little more optimzation here! (i.e. check for medium ints, etc.)
                            $sql.="int";
                            $is_numeric=true;
                            break; 
                    }
                    break;
 
                case "timestamp":
                    $sql.="timestamp"; 
                    $is_numeric=true;
                    break;
 
                case "date":
                    $sql.="date"; 
                    $is_numeric=false;
                    break;
 
                case "datetime":
                    $sql.="datetime"; 
                    $is_numeric=false;
                    break;
 
                case "time":
                    $sql.="time"; 
                    $is_numeric=false;
                    break;
 
                default: //future support for field types that are not recognized (hopefully this will 
                         // work without need for future modification)
                    $sql.=$ft;
                    $is_numeric=true; //I'm assuming new field types will follow SQL numeric syntax..this 
                                      //is where this support will breakdown 
                    break;
            }
 
            //VERY, VERY IMPORTANT!!! Don't forget to append the flags onto the end of the field creator
 
            if (strpos($ff,"unsigned")!=false)
            {
                //timestamps are a little screwy so we test for them
                if ($ft!="timestamp") $sql.=" unsigned";
            }
 
            if (strpos($ff,"zerofill")!=false)
            {
                //timestamps are a little screwy so we test for them
                if ($ft!="timestamp") $sql.=" zerofill";
            }
 
            if (strpos($ff,"auto_increment")!=false) $sql.=" auto_increment";
            if (strpos($ff,"not_null")!=false) $sql.=" not null";
            if (strpos($ff,"primary_key")!=false) $sql.=" primary key";
 
            //End of field flags
 
            if ($b<$nf-1)
            {
                $sql.=",n";
                $fl.=$fn.", ";
            }
            else
            {
                $sql.="n);nn";
                $fl.=$fn;
            }
 
            //we need some of the info generated in this loop later in the algorythm...save what we need to arrays
            $fna[$b]=$fn;
            $ina[$b]=$is_numeric;
 
        }
 
        out($fptr,$sql);
 
        if ($structure_only!=true)
        {
            //parse out the table's data and generate the SQL INSERT statements in order to replicate the data itself...
            for ($c=0;$c<$nr;$c++)
            {
                $sql="insert into $tablename ($fl) values (";
 
                $row=mysql_fetch_row($res2);
 
                for ($d=0;$d<$nf;$d++)
                {
                    $data=strval($row[$d]);
 
                    if ($ina[$d]==true)
                        $sql.= intval($data);
                    else
                        $sql.=""".mysql_escape_string($data).""";
 
                    if ($d<($nf-1)) $sql.=", ";
 
                }
 
                $sql.=");n";
 
                out($fptr,$sql);
 
            }
 
            out($fptr,"nn");
 
        }
 
        mysql_free_result($res2);    
 
    }
 
    if ($fptr!=false) fclose($fptr);
    return 0;
 
}
 
function out($fptr,$s)
{
    if ($fptr==false) echo("$s"); else fputs($fptr,$s);
}

Client Side Code

Client side code is inherently insecure and error prone. It should be used to reduce load on server, and input verified by server. Please include server verification code also in this section.

Description, change title as needed

code goes here

Candidate Code Suggestions

Add your suggestions and links here.