PHP Classes

File: example.php

Recommend this page to a friend!
  Classes of Fanache A. Remus   SQL class for PHP   example.php   Download  
File: example.php
Role: Example script
Content type: text/plain
Description: Example of using sql classes.
Class: SQL class for PHP
Access different databases with the same interface
Author: By
Last change: - rename sql class from SQL_DB to sqltype_DB (where sqltype is MYSQL or MYSQLI or SQLITE2 or SQLITE3 or MSSQL)
(this is required if you want to use 2 different sql databases)
(Note: after update to this revision you need to initialize class with specific name, see examples)
- corrected a bug on mysql/mysqli class (bug sql port if is different from 3306)
- add $this->sql_query_monitor (use to monitor query)
(Note: before this revision if you use $this->sql_tracer you get all log from class, include filters and other data and now with $this->sql_query_monitor you can see now only executed queries)
- corrected a bug on all classes on log
(before this update if is to many queries you get an memory error, and now you can avoid this problem by using $this-sql_debug = false;)
- add $this->query_log() (protected function - use $this->sql_query_log instead)
- corrected a bug on $this->real_escape() function
- corrected a bug on $this->free_result() function
- add example_2_db.php (example for using 2 databases of same type)
- add example_from_mysqli_to_sqlite3.php (example for 2 different databases)
- update example.php
- added mysqli class (for php 5)
- rewrite mysql.inc.php for php 5 standards
- added new mysql and mysqli examples (create table, select, insert, delete, drop)
- change function name from real_escape_string to real_escape
- change function name from sql_prepare_data to prepare_data
- corect bug on real_escape function
- corect bug on prepare_data function
Date: 9 years ago
Size: 7,902 bytes
 

Contents

Class file image Download
<?php
// test page for mysql

/**
 * 1) sql settings
 * 2) loading the class
 * 3) start sql connection
 * 4) creating new table
 * 5) inserting data into new table
 * 6) update data in new table
 * 7) reading data from new table (count)
 * 8) reading data from new table
 * 9) delete table data
 * 10) delete new table
 * 11) close database connection (optional delete sqlite file)
**/

/**
 * #1. sql settings
**/
ini_set('display_errors', 1);
error_reporting(E_ALL);
// to access the class should set this constant
define('FAR_ANTIHACK', true);
// sql settings
$db_info = array(
   
'type' => 'mysql', // variants allowed: mysql, mysqli, sqlite2, sqlite3, mssql
   
'server' => 'localhost', // for mysql - server address, for sqlite - path/db_name.ext
   
'port' => 3306, // optional (for sqlite is empty string '')
   
'username' => 'db_user', // sql user for mysql (for sqlite is empty string '')
   
'password' => 'db_pass', // sql password for mysql (for sqlite is empty string '')
   
'db_name' => 'test', // database name (for sqlite is optional)
   
);

/**
 * #2. class load
**/

// class load
switch($db_info['type']) {
    default:
   
// if specified mysql
   
case 'mysql':
        if (
file_exists('mysql.inc.php') ) {
            include_once
'mysql.inc.php';
           
// start sql connection
           
$db = new MYSQL_DB($db_info['server'], $db_info['port'], $db_info['username'], $db_info['password'], $db_info['db_name']);
        }
        else
            die(
'no file mysql.inc.php');
        break;
   
// if specified mysqli
   
case 'mysqli':
        if (
file_exists('mysqli.inc.php') ) {
            include_once
'mysqli.inc.php';
           
// start sql connection
           
$db = new MYSQLI_DB($db_info['server'], $db_info['port'], $db_info['username'], $db_info['password'], $db_info['db_name']);
        }
        else
            die(
'no file mysqli.inc.php');
        break;
   
// if specified sqlite2
   
case 'sqlite2':
        if (
file_exists('sqlite2.inc.php') ) {
            include_once
'sqlite2.inc.php';
           
// start sql connection
           
$db = new SQLITE2_DB($db_info['server'], $db_info['port'], $db_info['username'], $db_info['password'], $db_info['db_name']);
        }
        else
            die(
'no file sqlite2.inc.php');
        break;
   
// if specified sqlite3
   
case 'sqlite3':
        if (
file_exists('sqlite3.inc.php') ) {
            include_once
'sqlite3.inc.php';
           
// start sql connection
           
$db = new SQLITE3_DB($db_info['server'], $db_info['port'], $db_info['username'], $db_info['password'], $db_info['db_name']);
        }
        else
            die(
'no file sqlite3.inc.php');
        break;
}

/**
 * #3. start sql connection
**/


if ( $db->open() === false ) {
    echo
'<br>Error initializing the database connection.<br>';
    echo
$db->sql_error();
    exit;
}
else {
    echo
'<br>Specified database connection was made successfully.';
    echo
'<hr>';
}
// end sql connection

/**
 * #4. start creating new table
**/

// start creating new table
switch($db_info['type']) {
    default:
   
// if specified mysql
   
case 'mysql':
    case
'mysqli':
       
$query = 'CREATE TABLE IF NOT EXISTS test (
            id INT(11) NOT NULL AUTO_INCREMENT,
            camp1 VARCHAR(255),
            valoare TEXT,
            data_ins DATETIME,
            PRIMARY KEY (id)
            )'
;
        break;
   
// if specified sqlite2
   
case 'sqlite2':
       
$query = 'CREATE TABLE test (
            id INTEGER PRIMARY KEY NOT NULL UNIQUE,
            camp1 VARCHAR(255),
            valoare TEXT,
            data_ins DATETIME
            )'
;
        break;
   
// if specified sqlite3
   
case 'sqlite3':
       
$query = 'CREATE TABLE test (
            id INTEGER PRIMARY KEY NOT NULL UNIQUE,
            camp1 VARCHAR(255),
            valoare TEXT,
            data_ins DATETIME
            )'
;
        break;
}
$rezult = $db->query($query);
if ( !
$rezult ) {
    echo
'<br>Error creating table in the database.<br>';
    echo
$db->sql_error();
    exit;
}
else {
    echo
'<br>Specified table was successfully created.';
    echo
'<hr>';
}
// end creating new table

/**
 * #5. start inserting data into new table
**/

// start inserting data into new table
if ( $db_info['type'] == 'mysql' || $db_info['type'] == 'mysqli')
   
$query = "INSERT INTO test
        (camp1, valoare, data_ins) VALUES
        ('a', 'b', NOW())"
;
else
   
$query = "INSERT INTO test
        (camp1, valoare, data_ins) VALUES
        ('a', 'b', date('now') )"
;
$rezult = $db->query($query);
if ( !
$rezult ) {
    echo
'<br>An error occurred in data entry in the table.<br>';
    echo
$db->sql_error();
    exit;
}
else {
   
$id = $db->insert_id();
    echo
sprintf('<br>Data are stored in the database. Id returned is %s', $id);
    echo
'<hr>';
}
// end inserting data into new table

/**
 * #6. start update data in new table
**/

// start update data in new table
$query = "UPDATE test SET
    camp1 = 'aa',
    valoare = 'bb'
    WHERE camp1 = 'a' "
;
$rezult = $db->query($query);
if ( !
$rezult ) {
    echo
'<br>There was a error with the update data in the table.<br>';
    echo
$db->sql_error();
    exit;
}
else {
   
$total = $db->affected_rows();
    echo
sprintf('<br>%s rows have been updated.', $total);
    echo
'<hr>';
}
// end update data in new table

/**
 * #7. start reading data from new table (count)
**/

// start reading data from new table (count)
$query = "SELECT count(*) AS total FROM test";
$rezult = $db->query($query);
if ( !
$rezult ) {
    echo
'<br>An error occurred reading data (count) from the database.<br>';
    echo
$db->sql_error();
    exit;
}
else {
   
$count = $db->result();
    echo
sprintf('In total there are %s results returned from the table.', $count);
    echo
'<hr>';
}
// end reading data from new table (count)

/**
 * #8. start reading data
**/

// start reading data
$query = "SELECT * FROM test";
$rezult = $db->query($query);
if ( !
$rezult ) {
    echo
'<br>An error occurred reading data from the database.<br>';
    echo
$db->sql_error();
    exit;
}
else {
   
$total = $db->num_rows();
    if (
$total < 1 ) {
        echo
'<br>After selected not find any results.';
    }
    else {
        echo
sprintf('<br>Select returned %s rows from the database.<br>', $total);
       
$nr = 1;
        while(
$row=$db->fetch_array()) {
           
$timp = strtotime($row['data_ins']);
           
$data = date('d-m-Y H:i:s', $timp);
            echo
$nr.') '.$row['camp1'].' = '.$row['valoare'].' ('.$data.')<br>';
           
$nr++;
        }
       
// erasing the memory
       
$db->free_result();
    }
    echo
'<hr>';
}
// end reading data

/**
 * #9. start delete table data
**/

// start delete table data
$query = "DELETE FROM test";
$rezult = $db->query($query);
if ( !
$rezult ) {
    echo
'<br>An error occurred when deleting data in the table.<br>';
    echo
$db->sql_error();
    exit;
}
else {
   
$total = $db->affected_rows();
    echo
sprintf('%s rows have been deleted from the table.', $total);
    echo
'<hr>';
}
// end delete table data

/**
 * #10. start delete table
**/

// start delete table
$query = "DROP TABLE test";
$rezult = $db->query($query);
if ( !
$rezult ) {
    echo
'<br>An error occurred when deleting table from the database.<br>';
    echo
$db->sql_error();
   
//exit;
}
else {
    echo
'<br>Specified table was deleted from the database.<br>';
    echo
$db->sql_info();
    echo
'<hr>';
}
// end delete table

/**
 * #11. close database
**/

$db->close();
unset(
$db);
// optional delete file
if ( $db_info['type'] == 'sqlite2' || $db_info['type'] == 'sqlite3' ) {
   
$is_del = unlink($db_info['server']);
    if (
$is_del )
        echo
'<br>The file containing the database was deleted.';
    else
        echo
'An error occurred when deleting the file that contains the database.';
    echo
'<hr>';
}

// show log if is needed (for debug)
// echo '<pre>'.var_export($db->sql_tracer,1).'</pre>';
// show all query if is needed (for debug)
// echo '<pre>'.var_export($db->sql_query_log,1).'</pre>';
// show total query
echo '<br>Total query: '.count($db->sql_query_log);
// this is optional (destructor of the class close sql connection automaticaly)
//$db->close();
?>