Connecting and Manipulating MySQL with PHP-DB Class:
================================================== ======
This tutorial focuses on Advanced Techniques of Connecting and Manipulating data ( Select, Update )
using object oriented PHP ( PHP DB-Class ). As an extra requirement, a brief description of
"How to create table and insert data into database by PHPMyAdmin with screenshots" is also covered here
Intended Users:
================================================== ======
- Intermediate and Advanced level programmers
- Knowledge about basic HTML
- Knowledge about basic PHP tags
- Knowledge about basic PHP-MySQL functions (mysql_connect, mysql_select_db etc)
- Knowledge about basic SQL Query ( select, update etc )
Requirements:
================================================== ======
- PHP 3.0 or better
- A remote server that runs on Apache or local server
- PHPMyAdmin needs to be installed
What it covers
================================================== ======
- Creating Databases, Tables, Fields in PHPMyAdmin
- Creating PHP Class Constructor
- db_class.php
- connect() function
- select_db() function
- select() function
- get_row() function
- print_query() function
- Sample.php
- Download Complete tutorial
Creating Databases, Tables, Fields in PHPMyAdmin
================================================== =======
This is a preliminery tutorial of the main tutorial here. I will discuss here about "
Creating Databases, Tables, Fields in PHPMyAdmin" in brief. This section is for beginner users. If you already know this, you can skip this section.
The database created here will be used through the whole tutorial.
To implement this, you just need PHP, MySQL and a Apache server with PHPMyAdmin installed. You can achive this by installing PHP, MySQL, Apache individually, but, I would suggest you to use a bundle like
Apache2Triad,
XAMPP,
PHP TRIAD or
PHPEasy. I personally use Apache2Triad.
Anyway, Lets start.
1. Go to
http://localhost/phpmyadmin/ . Where you will find a
Create new database window like the following one. Type database name as 'test' there and press the
'Create' button.
2.
Database test has been created message will be displayed. Find
Create new table on database test box and type the table name as
'student_info' and number of fields as
'3'
3. Table 'student_info' will be created and a table for inputting name, type, size of fields will be displayed. Fill those fields according to the following image and press the save button
4. Now you have created the database
test, table
student_info and three fields
id, name, address very much successfully
5. Its now the time to insert some data here. Press the
'Insert' tab and a senario like the following one will come Insert data according to the sceenshot ( Its a dummy, you can change your data ) and press the
'Go' button to continue
6. After inserting data press the
'Browse' tab to show all data in a tabular format
Above six steps describes procedures for creating database, tables and inserting data to table in a visual format. But, you can do
step 2 to step 6 in a single step by
running queries in phpMyAdmin. Check following steps for this.
8. After craeting database ( step 1 ), just click on
'SQL' link and you will be brought to a page like the following screenshot. Input the following SQL query the press
'Go' button.
Quote:
DROP TABLE IF EXISTS student_info;
CREATE TABLE student_info (
id VARCHAR( 15 ) NOT NULL ,
name VARCHAR( 30 ) NOT NULL ,
address VARCHAR( 50 ) NOT NULL
) TYPE = MYISAM ;
INSERT INTO student_info ( id ,name ,address )
VALUES (
'CSE 00305220', 'Al - Zabir', 'Dhaka, Bangladesh'
), (
'CSE 00356323', 'Ahmed', 'Chittagong, Bangladesh'
);
|
9. Now you have completed and a success massege like the following will be shown.
Ok, now we have a databse
'test', a table
'student_info' and some fields. Now, we will establish a class that will connect with this database, run queries, update queries, select queries and amazingly print queries!
First of all we will construct a class file named
db_class.php. Then we will call this file from a another file named
sample.php. That's will be all. Through the whole procedure, we will use database name as
test as we created in
phpMyadmin and for localhost,
if you use Apache2triad, then
-------------------------------------
host='localhost'
user='root'
password='your apache2triad password'
if you use PHP Triad, then
-------------------------------------
host='localhost'
user=' '
password=' '
if you use XAMPP, then
-------------------------------------
host='localhost'
user='root'
password=' '
The db_class.php file
================================================== =================================
here is the db_class.php file
PHP Code:
<?php
// constants used by class
define('MYSQL_TYPES_NUMERIC', 'int real ');
define('MYSQL_TYPES_DATE', 'datetime timestamp year date time ');
define('MYSQL_TYPES_STRING', 'string blob ');
class db_class {
var $last_error; // holds the last error. Usually mysql_error()
var $row_count; // holds the last number of rows from a select
var $host; // address of mySQL host to connect to
var $user; // mySQL user name
var $pw; // mySQL password
var $db; // mySQL database to select
var $db_link; // current/last database link identifier
var $auto_slashes; // the class will add/strip slashes when it can
function db_class() {
// class constructor. Initializations here.
// Setup your own default values for connecting to the database here. You
// can also set these values in the connect() function and using
// the select_database() function.
$this->host = 'localhost';
$this->user = '';
$this->pw = '';
$this->db = '';
$this->auto_slashes = true;
}
function connect($host='', $username='', $password='', $database='', $persistant=true) {
// Opens a connection to MySQL and selects the database. If any of the
// function's parameter's are set, we want to update the class variables.
// If they are NOT set, then we're giong to use the currently existing
// class variables.
// Returns true if successful, false if there is failure.
if (!empty($host)) $this->host = $host;
if (!empty($user)) $this->user = $username;
if (!empty($pw)) $this->pw = $password;
// Establish the connection.
if ($persistant)
$this->db_link = mysql_pconnect($this->host, $this->user, $this->pw);
else
$this->db_link = mysql_connect($this->host, $this->user, $this->pw);
// Check for an error establishing a connection
if (!$this->db_link) {
$this->last_error = mysql_error();
return false;
}
// Select the database
if (!$this->select_db($database)) return false;
return $this->db_link; // success
}
function select_db($db='') {
// Selects the database for use. If the function's $db parameter is
// passed to the function then the class variable will be updated.
if (!empty($db)) $this->db = $db;
if (!mysql_select_db($this->db)) {
$this->last_error = mysql_error();
return false;
}
return true;
}
function select($sql) {
// Performs an SQL query and returns the result pointer or false
// if there is an error.
$this->last_query = $sql;
$r = mysql_query($sql);
if (!$r) {
$this->last_error = mysql_error();
return false;
}
$this->row_count = mysql_num_rows($r);
return $r;
}
function get_row($result, $type='MYSQL_BOTH') {
// Returns a row of data from the query result. You would use this
// function in place of something like while($row=mysql_fetch_array($r)).
// Instead you would have while($row = $db->get_row($r)) The
// main reason you would want to use this instead is to utilize the
// auto_slashes feature.
if (!$result) {
$this->last_error = "Invalid resource identifier passed to get_row() function.";
return false;
}
if ($type == 'MYSQL_ASSOC') $row = mysql_fetch_array($result, MYSQL_ASSOC);
if ($type == 'MYSQL_NUM') $row = mysql_fetch_array($result, MYSQL_NUM);
if ($type == 'MYSQL_BOTH') $row = mysql_fetch_array($result, MYSQL_BOTH);
if (!$row) return false;
if ($this->auto_slashes) {
// strip all slashes out of row...
foreach ($row as $key => $value) {
$row[$key] = stripslashes($value);
}
}
return $row;
}
function print_query($sql) {
// Useful during development for debugging purposes. Simple dumps a
// query to the screen in a table.
$r = $this->select($sql);
if (!$r) return false;
echo "<div style=\"border: 1px solid blue; font-family: sans-serif; margin: 8px;\">\n";
echo "<table cellpadding=\"3\" cellspacing=\"1\" border=\"0\" width=\"100%\">\n";
$i = 0;
while ($row = mysql_fetch_assoc($r)) {
if ($i == 0) {
echo "<tr><td colspan=\"".sizeof($row)."\"><span style=\"font-face: monospace; font-size: 9pt;\">$sql</span></td></tr>\n";
echo "<tr>\n";
foreach ($row as $col => $value) {
echo "<td bgcolor=\"#E6E5FF\"><span style=\"font-face: sans-serif; font-size: 9pt; font-weight: bold;\">$col</span></td>\n";
}
echo "</tr>\n";
}
$i++;
if ($i % 2 == 0) $bg = '#E3E3E3';
else $bg = '#F3F3F3';
echo "<tr>\n";
foreach ($row as $value) {
echo "<td bgcolor=\"$bg\"><span style=\"font-face: sans-serif; font-size: 9pt;\">$value</span></td>\n";
}
echo "</tr>\n";
}
echo "</table></div>\n";
}
function insert_sql($sql) {
// Inserts data in the database via SQL query.
// Returns the id of the insert or true if there is not auto_increment
// column in the table. Returns false if there is an error.
$this->last_query = $sql;
$r = mysql_query($sql);
if (!$r) {
$this->last_error = mysql_error();
return false;
}
$id = mysql_insert_id();
if ($id == 0) return true;
else return $id;
}
function update_sql($sql) {
// Updates data in the database via SQL query.
// Returns the number or row affected or true if no rows needed the update.
// Returns false if there is an error.
$this->last_query = $sql;
$r = mysql_query($sql);
if (!$r) {
$this->last_error = mysql_error();
return false;
}
$rows = mysql_affected_rows();
if ($rows == 0) return true; // no rows were updated
else return $rows;
}
function insert_array($table, $data) {
// Inserts a row into the database from key->value pairs in an array. The
// array passed in $data must have keys for the table's columns. You can
// not use any MySQL functions with string and date types with this
// function. You must use insert_sql for that purpose.
// Returns the id of the insert or true if there is not auto_increment
// column in the table. Returns false if there is an error.
if (empty($data)) {
$this->last_error = "You must pass an array to the insert_array() function.";
return false;
}
$cols = '(';
$values = '(';
foreach ($data as $key=>$value) { // iterate values to input
$cols .= "$key,";
$col_type = $this->get_column_type($table, $key); // get column type
if (!$col_type) return false; // error!
// determine if we need to encase the value in single quotes
if (is_null($value)) {
$values .= "NULL,";
}
elseif (substr_count(MYSQL_TYPES_NUMERIC, "$col_type ")) {
$values .= "$value,";
}
elseif (substr_count(MYSQL_TYPES_DATE, "$col_type ")) {
$value = $this->sql_date_format($value, $col_type); // format date
$values .= "'$value',";
}
elseif (substr_count(MYSQL_TYPES_STRING, "$col_type ")) {
if ($this->auto_slashes) $value = addslashes($value);
$values .= "'$value',";
}
}
$cols = rtrim($cols, ',').')';
$values = rtrim($values, ',').')';
// insert values
$sql = "INSERT INTO $table $cols VALUES $values";
return $this->insert_sql($sql);
}
function update_array($table, $data, $condition) {
// Updates a row into the database from key->value pairs in an array. The
// array passed in $data must have keys for the table's columns. You can
// not use any MySQL functions with string and date types with this
// function. You must use insert_sql for that purpose.
// $condition is basically a WHERE claus (without the WHERE). For example,
// "column=value AND column2='another value'" would be a condition.
// Returns the number or row affected or true if no rows needed the update.
// Returns false if there is an error.
if (empty($data)) {
$this->last_error = "You must pass an array to the update_array() function.";
return false;
}
$sql = "UPDATE $table SET";
foreach ($data as $key=>$value) { // iterate values to input
$sql .= " $key=";
$col_type = $this->get_column_type($table, $key); // get column type
if (!$col_type) return false; // error!
// determine if we need to encase the value in single quotes
if (is_null($value)) {
$sql .= "NULL,";
}
elseif (substr_count(MYSQL_TYPES_NUMERIC, "$col_type ")) {
$sql .= "$value,";
}
elseif (substr_count(MYSQL_TYPES_DATE, "$col_type ")) {
$value = $this->sql_date_format($value, $col_type); // format date
$sql .= "'$value',";
}
elseif (substr_count(MYSQL_TYPES_STRING, "$col_type ")) {
if ($this->auto_slashes) $value = addslashes($value);
$sql .= "'$value',";
}
}
$sql = rtrim($sql, ','); // strip off last "extra" comma
if (!empty($condition)) $sql .= " WHERE $condition";
// insert values
return $this->update_sql($sql);
}
function execute_file ($file) {
// executes the SQL commands from an external file.
if (!file_exists($file)) {
$this->last_error = "The file $file does not exist.";
return false;
}
$str = file_get_contents($file);
if (!$str) {
$this->last_error = "Unable to read the contents of $file.";
return false;
}
$this->last_query = $str;
// split all the query's into an array
$sql = explode(';', $str);
foreach ($sql as $query) {
if (!empty($query)) {
$r = mysql_query($query);
if (!$r) {
$this->last_error = mysql_error();
return false;
}
}
}
return true;
}
function print_last_error($show_query=true) {
// Prints the last error to the screen in a nicely formatted error message.
// If $show_query is true, then the last query that was executed will
// be displayed aswell.
?>
<div style="border: 1px solid red; font-size: 9pt; font-family: monospace; color: red; padding: .5em; margin: 8px; background-color: #FFE2E2">
<span style="font-weight: bold">db.class.php Error:</span><br><?= $this->last_error ?>
</div>
<?
if ($show_query && (!empty($this->last_query))) {
$this->print_last_query();
}
}
}
?>
but before proceding with
db_class.php, we will discuss about some important part of
db_class.php.
Creating PHP Class Constructor
================================================== =================================
PHP Code:
function db_class() {
$this->host = 'localhost';
$this->user = '';
$this->pw = '';
$this->db = '';
$this->auto_slashes = true;
}
Features
=========
- It acts as class constructor. All Initializations are here.
- Setup your own default values for connecting to the database here.
- You can also set these values in the connect() function and using the select_db() function.
Connect() function
================================================== =================================
PHP Code:
function connect($host='', $username='', $password='', $database='', $persistant=true) {
if (!empty($host)) $this->host = $host;
if (!empty($user)) $this->user = $username;
if (!empty($pw)) $this->pw = $password;
// Establish the connection.
if ($persistant)
$this->db_link = mysql_pconnect($this->host, $this->user, $this->pw);
else
$this->db_link = mysql_connect($this->host, $this->user, $this->pw);
// Check for an error establishing a connection
if (!$this->db_link) {
$this->last_error = mysql_error();
return false;
}
// Select the database
if (!$this->select_db($database)) return false;
return $this->db_link; // success
}
Features
=========
- Opens a connection to MySQL and selects the database
- If any of the function's parameter's are set, we want to update the class variables
- If they are NOT set, then we're giong to use the currently existing class variables
- Returns true if successful, false if there is failure
select_db() function
================================================== =================================
PHP Code:
function select_db($db='') {
if (!empty($db)) $this->db = $db;
if (!mysql_select_db($this->db)) {
$this->last_error = mysql_error();
return false;
}
return true;
}
Features
=========
- Selects the database for use.
- If the function's $db parameter is passed to the function then the class variable will be updated
select() function
================================================== =================================
PHP Code:
function select($sql) {
$this->last_query = $sql;
$r = mysql_query($sql);
if (!$r) {
$this->last_error = mysql_error();
return false;
}
$this->row_count = mysql_num_rows($r);
return $r;
}
Features
=========
- Performs an SQL query and returns the result pointer or false if there is an error
get_row() function
================================================== =================================
PHP Code:
function get_row($result, $type='MYSQL_BOTH') {
if (!$result) {
$this->last_error = "Invalid resource identifier passed to get_row() function.";
return false;
}
if ($type == 'MYSQL_ASSOC') $row = mysql_fetch_array($result, MYSQL_ASSOC);
if ($type == 'MYSQL_NUM') $row = mysql_fetch_array($result, MYSQL_NUM);
if ($type == 'MYSQL_BOTH') $row = mysql_fetch_array($result, MYSQL_BOTH);
if (!$row) return false;
if ($this->auto_slashes) {
// strip all slashes out of row...
foreach ($row as $key => $value) {
$row[$key] = stripslashes($value);
}
}
return $row;
}
Features
=========
- Returns a row of data from the query result.
- You would use this function in place of something like while($row=mysql_fetch_array($r)).
- Instead you would have while($row = $db->get_row($r)) The main reason you would want to use this instead is to utilize the auto_slashes feature
print_query() function
================================================== =================================
PHP Code:
function print_query($sql) {
$r = $this->select($sql);
if (!$r) return false;
echo "<div style=\"border: 1px solid blue; font-family: sans-serif; margin: 8px;\">\n";
echo "<table cellpadding=\"3\" cellspacing=\"1\" border=\"0\" width=\"100%\">\n";
$i = 0;
while ($row = mysql_fetch_assoc($r)) {
if ($i == 0) {
echo "<tr><td colspan=\"".sizeof($row)."\"><span style=\"font-face: monospace; font-size: 9pt;\">$sql</span></td></tr>\n";
echo "<tr>\n";
foreach ($row as $col => $value) {
echo "<td bgcolor=\"#E6E5FF\"><span style=\"font-face: sans-serif; font-size: 9pt; font-weight: bold;\">$col</span></td>\n";
}
echo "</tr>\n";
}
$i++;
if ($i % 2 == 0) $bg = '#E3E3E3';
else $bg = '#F3F3F3';
echo "<tr>\n";
foreach ($row as $value) {
echo "<td bgcolor=\"$bg\"><span style=\"font-face: sans-serif; font-size: 9pt;\">$value</span></td>\n";
}
echo "</tr>\n";
}
echo "</table></div>\n";
}
Features
=========
- Useful during development for debugging purposes
- Simple dumps a query to the screen in a table
The sample.php file
================================================== =================================
here is the sample.php file
PHP Code:
<?php
require_once('db.class.php');
$db = new db_class;
if (!$db->connect('localhost', 'user_of_database_here', 'password_of_database_here', 'name_of_database_here', true))
$db->print_last_error(false);
// Create the table (if it doesn't exist) by executing the external sql
// file with the create table SQL statement.
echo "Executing SQL commands in external file test_data.sql...<br>";
if (!$db->execute_file('test_data.sql')) $db->print_last_error(false);
echo "<br>Adding data to the table from an array...<br>";
$data = array(
'id' => 'CSE00305345',
'name' => 'Mahmud',
'address' => 'Dhaka'
);
$id = $db->insert_array('student_info', $data);
if (!$user_id) $db->print_last_error(false);
$db->print_query("SELECT * FROM student_info WHERE id=$id");
echo "<br>Updating the data in the table by changing the date_added... ";
$data = array('address' => 'sylhet');
$rows = $db->update_array('student_info', $data, "id=$id");
if (!$rows) $db->print_last_error(false);
if ($rows > 0) echo "$rows rows updated.";
$db->print_query("SELECT * FROM student_info WH
Connecting and Manipulating MySQL with PHP-DB Class