Database

PHP Setup Wizard comes with a database class to help you run your SQL queries without extra coding. You can use the database class in your callback methods. Below is a list of available database class methods and parameters.

  • db_init ($params = array())
    Initialize the database connection. Returns "true" on success and "false" on failure.
    db_host - database host name
    db_user - database username
    db_pass - database password
    db_name - database name
    db_encoding (optional) - database charset (ie: utf8)
    db_collation (optional) - database collation (ie: utf8_general_ci)
    db_type (optional) - database type (available values: mysql, mysqli)
  • db_close
    Close database connection.
  • db_query ($sql = '', $soft = false)
    Run SQL statement. Returns result set or "false" if query fails.
    sql - sql statement
    soft - set to "true" not to show errors if query fails
  • db_fetch ($result, $type = 'object')
    Fetch a result row as an object or array.
    result - result set
    type - available values: object, array
  • db_import_file ($filename, $replace = array())
    Import file containing SQL statements into the database. Returns "true" on success and "false" on failure.
    filename - path to the file
    replace - array of values to be replaced in the file
  • db_import_sql ($sql, $replace = array())
    Import multiple SQL statements into the database. Returns "true" on success and "false" on failure. If $replace is set to "true", function will return file content instead.
    sql - SQL statements to be imported
    replace - array of values to be replaced in the SQL statements
  • db_last_insert_id
    Return the ID generated in the last query.
  • db_engines
    Returns an array with available database engines, such as InnoDB, MyISAM, etc.
  • db_table_columns ($table)
    Returns an array with table column names.
    $table - table name
  • db_table_column ($table, $column)
    Checks if column name exists in a table.
    $table - table name
    $column - column name
  • db_escape ($value)
    Escapes special characters in a string for use in an SQL statement.

Below is an example of a sample database connection, file import and SQL query.

<?php
function install($params = array())
{
  
// database configuration array
  
$dbconf = array(
    
'db_host' => 'localhost',
    
'db_user' => 'root',
    
'db_pass' => 'mypassword',
    
'db_name' => 'mydb',
    
'db_encoding' => 'utf8',
  );

  
// connect to the database
  
if ( !$this->db_init($dbconf) ) {
    return 
false;
  }

  
// values to be replaced
  
$replace = array(
    
'{:db_prefix}' => 'vld_',
    
'{:db_engine}' => in_array('innodb'$this->db_engines) ? 'InnoDB' 'MyISAM',
    
'{:db_charset}' => $this->db_version >= '4.1' 'DEFAULT CHARSET=utf8' ''
  
);

  
// import file
  
if ( !$this->db_import_file(BASE_PATH '/sql/data.sql'$replace) ) {
    return 
false;
  }

  
// sample sql insert
  
$this->db_query("INSERT INTO `table` VALUES ('some value');");

  
// close connection
  
$this->db_close();
}
?>

We can also ask our users to type in the database details during one of the wizard steps, which we can then use to connect to the database. So lets create the necessary text fields and rules in this example.

<?php
$steps 
= array(
  array(
    
'name' => 'Database settings.',
    
'fields' => array(
      array(
        
'type' => 'info',
        
'value' => 'Specify your MySQL details.',
      ),
      array(
        
'label' => 'Database hostname',
        
'name' => 'db_hostname',
        
'type' => 'text',
        
'default' => 'localhost',
        
'validate' => array(
          array(
'rule' => 'required')
        ),
      ),
      array(
        
'label' => 'Database username',
        
'name' => 'db_username',
        
'type' => 'text',
        
'default' => '',
        
'validate' => array(
          array(
'rule' => 'required')
        ),
      ),
      array(
        
'label' => 'Database password',
        
'name' => 'db_password',
        
'type' => 'text',
        
'default' => '',
        
'validate' => array(
          array(
'rule' => 'required')
        ),
      ),
      array(
        
'label' => 'Database name',
        
'name' => 'db_name',
        
'type' => 'text',
        
'default' => '',
        
'highlight_on_error' => false,
        
'validate' => array(
          array(
'rule' => 'required'),
          array(
            
'rule' => 'database',
            
'params' => array(
              
// note that these values correspond to the field names above
              
'db_host' => 'db_hostname',
              
'db_user' => 'db_username',
              
'db_pass' => 'db_password',
              
'db_name' => 'db_name'
            
)
          )
        )
      )
    )
  )
);
?>

We can now use the above details in the "includes/callbacks.php" file for the database related stuff.

<?php
function install($params = array())
{
  
// database configuration array
  
$dbconf = array(
    
'db_host' => $_SESSION['params']['db_hostname'],
    
'db_user' => $_SESSION['params']['db_username'],
    
'db_pass' => $_SESSION['params']['db_password'],
    
'db_name' => $_SESSION['params']['db_name'],
  );

  
// connect to the database
  
if ( !$this->db_init($dbconf) ) {
    return 
false;
  }

  
// sample sql insert
  
$this->db_query("INSERT INTO `table` VALUES ('some value');");

  
// close connection
  
$this->db_close();
}
?>