Well, we discussed a lot of ASP.Net related stories and lets see a little bit about PHP as well. No matter the language, logic is the main concern that we should focus on. If the way we practice is good, better the outcome will be.
We had discussed a DB Layer in C# that we can of course use it for Web or Windows application. And lets think the same way in PHP as well. Basically what we need is to pass some connection parameters and get a resultset from DB or do our CRUD transactions. So, the below code would explain a simple PHP class that we can use to query the DB across the application.
So we can use this class as per below in our data layer.
It is that easy :) so copy this code and try once.
We had discussed a DB Layer in C# that we can of course use it for Web or Windows application. And lets think the same way in PHP as well. Basically what we need is to pass some connection parameters and get a resultset from DB or do our CRUD transactions. So, the below code would explain a simple PHP class that we can use to query the DB across the application.
<?php
class database {
public $affected_rows = 0; // Use after calling query function
public $insert_id = null; // Use after calling an insert query
public $connection; // For use with mysql php functions
public $result; // For use with mysql php functions
private $crlf = "\r\n";
private $as_file = false;
private $tmp_buffer = "";
private $db_host = "Host";
private $db_user = "Username";
private $db_pass = "Password";
private $db_name = "Database";
const GET_ARRAY = 0;
const GET_ROW = 1;
const GET_FIELD = 2;
const VTYPE_BOTH = "BOTH";
const VTYPE_SESSION = "SESSION";
const VTYPE_GLOBAL = "GLOBAL";
function __construct() {
$this->db_name = "Host";
$this->db_user = "User";
$this->db_pass = "Password";
$this->db_name = "Database";
//connect to database
$connection = @mysql_connect("Host", "User", "Password");
if (!$connection) {
$this->show_error('Could not connect to database server.');
}
$this->connection = $connection;
//select database
if($this->db_name != '') {
$db = @mysql_select_db($this->db_name, $connection);
if (!$db) {
$this->show_error('Could not connect to database.');
}
}
}
// -----------------------------------------------
// select_db($db_name)
// -----------------------------------------------
// Description: Allows you to switch databases
//
// Parameters:
// $db_name = Name of the database to connect to
// -----------------------------------------------
public function select_db($db_name) {
$db = @mysql_select_db($db_name, $this->connection);
if(!$db) {
$this->show_error('Could not connect to database.');
return false;
}
return true;
}
// -----------------------------------------------
// disconnect()
// -----------------------------------------------
// Description: Disconnects the connection
//
// Parameters: none
// -----------------------------------------------
public function disconnect() {
return mysql_close($this->connection);
}
// -----------------------------------------------
// query($query, $return_type = self::GET_ARRAY)
// -----------------------------------------------
// Description: Runs a MySQL query
//
// Parameters:
// $query = MySQL query
// $return_type = database::GET_ARRAY (default)
// Returns results to a 2-dimensional array
// database::GET_ROW
// Returns result record to an array
// database::GET_FIELD
// Returns result to a variable
//
// Notes:
// 1. After a query is called if it affected any rows, then you can use
// the included $db->affected_rows variable to get the number of
// rows the query affected. ($db is just an example)
// 2. After calling an INSERT query, you can use $db->insert_id to get
// the value of the ID generated.
// 3. Will store the connection and result resource variables in public
// variables that may be used outside of this class with mysql_
// functions.
// -----------------------------------------------
public function query($query, $return_type = self::GET_ARRAY) {
// Determine query type
$q = trim($query);
$qarr = explode(' ', $q);
// Proceed based on query type
if (strtoupper($qarr[0]) == "SELECT" || strtoupper($qarr[0]) == "SHOW" ||
strtoupper($qarr[0]) == "EXPLAIN" || strtoupper($qarr[0]) == "DESCRIBE" ||
strtoupper($qarr[0]) == "HELP") {
// Send query
$this->result = @mysql_query($query);
if (!$this->result) {
$this->show_error(mysql_error());
@mysql_free_result($this->result);
return false;
} else {
// Output number of affected rows
$this->affected_rows = mysql_affected_rows();
// Get array of field names
$fields = array();
$num_fields = mysql_num_fields($this->result);
for($i=0;$i<$num_fields;$i++) {
$fields[] = mysql_field_name($this->result, $i);
}
// Run through data returned
$n = 0;
$data = array();
while ($row=mysql_fetch_array($this->result, MYSQL_ASSOC)) {
if($return_type == self::GET_ROW) return $row;
foreach($fields as $f) {
$field = trim($f);
if ($return_type == self::GET_FIELD) return $row[$field];
$data[$n] = $row;
}
$n++;
}
}
// Free result
@mysql_free_result($this->result);
// Show error
return $data;
} else {
// Send query
$this->result = @mysql_query($query);
if (!$this->result) {
$this->show_error(mysql_error());
return false;
} else {
// Output number of affected rows
$this->affected_rows = mysql_affected_rows();
}
// If the query was an INSERT query, then store the insert_id
if(strtoupper($qarr[0]) == "INSERT") $this->insert_id = mysql_insert_id();
// Free result
@mysql_free_result($this->result);
// Return result
return true;
}
}
// -----------------------------------------------
// get_table_fields($table_name, $exclude = array())
// -----------------------------------------------
// Description: Returns an array of fields in the chosen table
//
// Parameters:
// $table_name = Name of a table to retrieve the fields from
// $exclude = Array of fields to exclude from results (i.e. id fields)
//
// Notes:
// 1. It will return information about each field including Field, Type,
// Null, Key, Default, and Extra
// -----------------------------------------------
public function get_table_fields($table_name, $exclude = array()) {
$structure = $this->query("SHOW COLUMNS FROM `$table_name`;");
if (is_array($structure)) {
$fields = array();
foreach ($structure as $k => $v) {
if (!in_array($v["Field"], $exclude)) {
$fields[] = $v;
}
}
return $fields;
}
return false;
}
// -----------------------------------------------
// get_tables($exclude = array())
// -----------------------------------------------
// Description: Returns an array of tables in the database
//
// Parameters:
// $exclude = Array of tables to exclude from results
// -----------------------------------------------
public function get_tables($exclude = array()) {
$structure = $this->query("SHOW TABLES FROM `".$this->db_name."`;");
if (is_array($structure)) {
$tables = array();
foreach ($structure as $k => $v) {
if (!in_array($v["Tables_in_".$this->db_name], $exclude)) {
$tables[] = $v["Tables_in_".$this->db_name];
}
}
return $tables;
}
return false;
}
// -----------------------------------------------
// get_databases($exclude = array())
// -----------------------------------------------
// Description: Returns an array of databases excluding system databases
//
// Parameters:
// $exclude = Array of databases to exclude from results
// -----------------------------------------------
public function get_databases($exclude = array()) {
$exc = array('information_schema','mysql');
$exclude_all = array_merge($exclude, $exc);
$structure = $this->query("SHOW DATABASES;");
if (is_array($structure)) {
$databases = array();
foreach ($structure as $k => $v) {
if (!in_array($v["Database"], $exclude_all)) {
$databases[] = $v["Database"];
}
}
return $databases;
}
return false;
}
// -----------------------------------------------
// get_mysql_variables()
// -----------------------------------------------
// Description: Returns an array of the MySQL variables and their values
//
// Parameters:
// $variable_type = They type of variable
// database::VTYPE_BOTH (default)
// database::VTYPE_SESSION
// database::VTYPE_GLOBAL
// -----------------------------------------------
public function get_mysql_variables($variable_type = self::VTYPE_BOTH) {
return $this->query('SHOW ' . ($variable_type != self::BOTH ? $variable_type : '') . ' VARIABLES;');
}
// -----------------------------------------------
// sort_nonindexed_table($table_name, $columns = array())
// -----------------------------------------------
// Description: Sorts a table with no index column by the chosen column.
//
//
// Parameters:
// $table_name = Name of table being sorted
// $columns = Array of columns/fields being sorted by
// Example syntaxes:
// As an array: array('`column_1` ASC','`column_2` DESC')
// As a string: '`column_1` ASC, `column_2` ASC'
//
// Returns:
// Success = true
// Failure = false
//
// Notes:
// The table will not stay sorted after INSERTs and UPDATEs.
// -----------------------------------------------
public function sort_nonindexed_table($table_name, $columns = array()) {
if(is_array($columns)) $c = implode(", ",$columns);
else $c = $columns;
return $this->query("ALTER TABLE `$table_name` ORDER BY $c;");
}
// -----------------------------------------------
// write_backup_sql($generate_file = false, $all_dbs = false)
// -----------------------------------------------
// Description: Write MySQL backup code for your database
//
// Parameters:
// $generate_file = User can opt to have this generate an SQL file.
// If true, then this function must be called before
// anything is output to browser when headers are called.
// $all_dbs = User may also opt to have this function backup all the
// databases on their host. (Better when $generate_file = true)
//
// Returns:
// If $generate_file = true, then result is an SQL file.
// If $generate_file = false, then result is a string.
// -----------------------------------------------
public function write_backup_sql($generate_file = false, $all_dbs = true) {
// If the user wants to generate an SQL file, then convert the Content Type
if ($generate_file) {
$this->as_file = $generate_file;
header('Content-Type: application/octetstream');
header('Content-Disposition: filename="' . $this->db_name . '.sql"');
}
// Write header
$dump_buffer = "# MySQL Database Dump".$this->crlf;
$dump_buffer .= "# Backup made: " . date("F j, Y, g:i a") . $this->crlf;
$dump_buffer .= "# Database: " . ($all_dbs == true ? 'All Databases' : $this->db_name) . $this->crlf;
if(!$all_dbs) $dump_buffer .= "# Backed up tables: " . count($this->get_tables()) . $this->crlf;
else $dump_buffer .= "# Backed up databases: " . count($this->get_databases()) . $this->crlf;
$dump_buffer .= "# Generated by: class_database by Matt DeKok" .$this->crlf;
if($all_dbs) {
// Retrieve SQL dump for each database
$db_name = $this->db_name;
$dbs = $this->get_databases();
foreach($dbs as $db) {
$this->db_name = $db;
$dump_buffer .= $this->generate_sql($generate_file, $db);
}
$this->db_name = $db_name;
} else {
// Retrieve SQL dump for each table
$dump_buffer .= $this->generate_sql($generate_file, $this->db_name);
}
// Output SQL dump
if ($generate_file) {
echo $dump_buffer;
exit;
} else {
return $dump_buffer;
}
}
// -----------------------------------------------
// execute_file($file)
// -----------------------------------------------
// Description: Execute the MySQL commands inside a file
//
// Parameters:
// $file = The path of the file
//
// Returns:
// On success = true
// On failure = false
// -----------------------------------------------
public function execute_file($file) {
if(!file_exists($file)) {
$this->show_error("File '$file' does not exist.");
return false;
}
$content = file_get_contents($file);
if(!$content) {
$this->show_error("Unable to read '$file'.");
return false;
}
$sql = explode(";",$content);
foreach($sql as $command) {
if(!empty($command)) {
if(!$this->query($command)) return false;
}
}
return true;
}
// -----------------------------------------------
// sql_date_format($date)
// -----------------------------------------------
// Description: Converts a timestamp or string format date
// to MySQL format so it can be inserted into
// a table
//
// Parameters:
// $date = A numeric timestamp or a string date
// -----------------------------------------------
public function sql_date_format($date) {
if(gettype($date) == 'string') $value = strtotime($date);
return date('Y-m-d H:i:s', $value);
}
// -----------------------------------------------
// print_table($results,
// $row_color = null,
// $alternate_row_color = null,
// $row_style = null,
// $table_style = null,
// $header_row_style = null)
// -----------------------------------------------
// Description: Prints a table from a query.
//
// Parameters:
// $results = The associative array returned from the query function
// $row_color = The primary row color
// $alternate_row_color = The alternate row color
// $row_style = The style of every row
// $table_style = The table style
// $header_row_style = The header row style
// -----------------------------------------------
public function print_table($results, $row_color = null, $alternate_row_color = null, $row_style = null, $table_style = null, $header_row_style = null) {
if(!is_array($results)) return false;
if($row_color == null) $row_color = "#fff";
if($alternate_row_color == null) $alternate_row_color = "#ddd";
if($row_style == null) $row_style = "vertical-align: top;";
if($table_style == null) $table_style = "border: 2px solid #888; border-collapse: collapse;";
if($header_row_style == null) $header_row_style = "text-align: left; background-color: #ccc; border-bottom: 2px solid #888; color: #000;";
$headers = array_keys($results[0]);
echo "<div style=\"overflow: auto; overflow-y: hidden;\">\n";
echo "<table style=\"$table_style\">\n";
echo " <tr>\n";
foreach($headers as $header) {
echo " <th style=\"$header_row_style\">$header</th>\n";
}
echo " </tr>\n";
$i = 0;
foreach($results as $result) {
$i++;
if(floor($i / 2) == $i / 2) $style_row = "background-color: $row_color; $row_style";
else $style_row = "background-color: $alternate_row_color; $row_style";
echo " <tr style=\"$style_row\">\n";
foreach($headers as $header) {
$item = $result[$header];
echo " <td>$item</td>\n";
}
echo " </tr>\n";
}
echo "</table>\n";
echo "</div>\n";
}
// -----------------------------------------------
// prevent_injection($value)
// -----------------------------------------------
// Description: If you use this on all your values for UPDATE
// and INSERT queries.
//
// Note: It will be better to let this add the quotes to your
// strings for you.
// -----------------------------------------------
public function prevent_injection($value) {
// Stripslashes
if (get_magic_quotes_gpc()) {
$value = stripslashes($value);
}
// Quote if not a number or a numeric string
if (!is_numeric($value)) {
$value = "'" . mysql_real_escape_string($value) . "'";
}
return $value;
}
// -----------------------------------------------
// Pay no mind to the following functions
// -----------------------------------------------
private function show_error($error) {
//echo "<p><strong>Error:</strong> " . $error . "</p>";
header('../index.php');
}
private function get_table_def($table) {
$this->error = array();
$schema_create = "DROP TABLE IF EXISTS `" . $this->db_name . "`.`$table`;".$this->crlf;
$db = $table;
$structure = $this->query("SHOW CREATE TABLE `$table`;");
$schema_create = $structure[0]['Create Table'];
$schema_create = str_replace("`$table`","`" . $this->db_name . "`.`$table`",$schema_create);
if (get_magic_quotes_gpc()) {
return (stripslashes($schema_create));
} else {
return ($schema_create);
}
}
private function get_table_content($table, $limit_from = 0, $limit_to = 0) {
// Defines the offsets to use
if ($limit_from > 0) {
$limit_from--;
} else {
$limit_from = 0;
}
if ($limit_to > 0 && $limit_from >= 0) {
$add_query = " LIMIT $limit_from, $limit_to";
} else {
$add_query = '';
}
$result = mysql_query('SELECT * FROM ' . $this->db_name . '.' . $table . $add_query);
if (!$result) {
$this->show_error(mysql_error());
return false;
} else {
@set_time_limit(1200); // 20 Minutes
// Checks whether the field is an integer or not
for ($j = 0; $j < mysql_num_fields($result); $j++) {
$field_set[$j] = mysql_field_name($result, $j);
$type[$j] = mysql_field_type($result, $j);
$primary[$j] = substr_count(mysql_field_flags($result, $j),"primary_key");
$auto[$j] = substr_count(mysql_field_flags($result, $j),"auto_increment");
if ($type[$j] == 'tinyint' || $type[$j] == 'smallint' || $type[$j] == 'mediumint' || $type[$j] == 'int' || $type[$j] == 'bigint' || $type[$j] == 'timestamp') {
$field_num[$j] = true;
} else {
$field_num[$j] = false;
}
} // end for
// Get the scheme
if (isset($GLOBALS['showcolumns'])) {
$fields = implode('`, `', $field_set);
$schema_insert = "INSERT INTO `" . $this->db_name . "`.`$table` (`$fields`) VALUES (";
} else {
$schema_insert = "INSERT INTO `" . $this->db_name . "`.`$table` VALUES (";
}
$field_count = mysql_num_fields($result);
$search = array("\x0a", "\x0d", "\x1a", "'"); //\x08\\x09, not required
$replace = array("\\n", "\\r", "\Z", "\\'");
while ($row = mysql_fetch_row($result)) {
for ($j = 0; $j < $field_count; $j++) {
if (!isset($row[$j]) || ($primary[$j] && $auto[$j])) {
$values[] = 'NULL';
} elseif (!empty($row[$j])) {
// a number
if ($field_num[$j] && is_numeric($row[$j])) {
$values[] = $row[$j];
}
// a string
else {
$values[] = "'" . str_replace($search, $replace, $row[$j]) . "'";
}
} else {
$values[] = "''";
} // end if
} // end for
$insert_line = $schema_insert . implode(', ', $values) . ')';
unset($values);
// Call the handler
$this->handler($insert_line);
} // end while
}
return true;
}
private function handler($sql_insert) {
if (!$this->as_file)
$this->tmp_buffer .= htmlspecialchars("$sql_insert;" . $this->crlf);
else
$this->tmp_buffer .= "$sql_insert;" . $this->crlf;
}
private function faqe_db_error() {
return mysql_error();
}
private function faqe_db_insert_id($result) {
return mysql_insert_id($result);
}
private function generate_sql($generate_file, $db_name) {
$dump_buffer = "";
$dump_buffer .= $this->crlf."# --------------------------------------------------------".$this->crlf.$this->crlf;
$dump_buffer .= "#".$this->crlf;
$dump_buffer .= "# Database structure for database '$db_name'".$this->crlf;
$dump_buffer .= "#".$this->crlf;
$dump_buffer .= "DROP DATABASE IF EXISTS `$db_name`;".$this->crlf;
$cdb = $this->query("SHOW CREATE DATABASE `$db_name`;");
$dump_buffer .= str_replace(" /*!40100 DEFAULT CHARACTER SET latin1 */",";",$cdb[0]["Create Database"]).$this->crlf;
$dump_buffer .= $this->crlf;
$tables = $this->query("SHOW TABLES FROM `" . $db_name . "`;");
$num_tables = $this->affected_rows;
// Check if tables were found
if ($num_tables == 0) {
if ($generate_file) {
echo "# 0 tables found";
} else {
return false;
}
}
foreach($tables as $table) {
$table = $table["Tables_in_$db_name"];
// Write table structure
$dump_buffer .= "# --------------------------------------------------------".$this->crlf;
$dump_buffer .= $this->crlf."#".$this->crlf;
$dump_buffer .= "# Table structure for table '$table'".$this->crlf;
$dump_buffer .= "#".$this->crlf;
$dump_buffer .= $this->get_table_def($table) . ";".$this->crlf;
// Write table data
$dump_buffer .= $this->crlf."#".$this->crlf;
$dump_buffer .= "# Dumping data for table '$table'".$this->crlf;
$dump_buffer .= "#".$this->crlf;
$this->tmp_buffer = "";
$this->get_table_content($table, 0, 0);
$dump_buffer .= $this->tmp_buffer;
$dump_buffer .= $this->crlf;
}
return $dump_buffer;
}
private function get_column_type($table_name, $column_name) {
$result = $this->query("SELECT $column_name FROM $table_name;");
if(!$result) {
return false;
}
$type = mysql_field_type($result, 0);
if(!$type) {
$this->show_error(mysql_error());
$return = false;
}
@mysql_free_result($result);
return $type;
}
}
?>
So we can use this class as per below in our data layer.
$sql = "SELECT id,name from tabel_name";
$arrSelect = $db->query($sql);
for ($i = 0; $i < count($arrSelect); $i++)
{
$id = $arrSelect[$i]['id'];
$name = $arrSelect[$i]['name'];
}
It is that easy :) so copy this code and try once.
No comments:
Post a Comment