PHP Classes

SQL class for PHP: Access different databases with the same interface

Recommend this page to a friend!
  Info   View files Example   View files View files (10)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
Not enough user ratingsTotal: 768 This week: 1All time: 4,400 This week: 560Up
Version License PHP version Categories
sql-class-php 1.0.0GNU General Publi...5Databases
Description 

Author

This package can access different databases with the same interface.

It provides different classes with the same functions to access several types of databases like establishing connections, executing queries, retrieving the query results, retrieve the number result rows, or the number of affected rows, or the last inserted identifier, and managing transactions.

Currently it provides classes to access Microsoft SQL server, MySQL
(with MySQL and MySQLi extensions), SQLite 2 and 3.

Picture of Fanache A. Remus
  Performance   Level  
Name: Fanache A. Remus <contact>
Classes: 1 package by
Country: Romania Romania
Age: 49
All time rank: 271154 in Romania Romania
Week rank: 416 Up11 in Romania Romania Up

Example

<?php
// example page for mysqli

/**
 * 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);

define('FAR_ANTIHACK', true); // required - to access the class should set this constant
// you can skip this constant and include class you need
define('FAR_SQL_TYPE', 'mysqli'); // required - variants allowed: mysql, mysqli, sqlite2, sqlite3, mssql

// sql settings
$db_info = array(
   
'server' => '127.0.0.1', // required - for "localhost" port 3306 is used, for other port use ip address
   
'port' => 3306, // optional - if use other port, dont put "localhost" on server name (see coments on http://ro1.php.net/manual/en/mysqli.construct.php )
   
'username' => 'db_user', // required - sql user for mysql (for sqlite is empty string '')
   
'password' => 'db_pass', // required - sql password for mysql (for sqlite is empty string '')
   
'db_name' => 'test', // required - database name (for sqlite is optional)
   
'db_encoding' => 'utf8', // optional - for mysql is used utf8 default
   
'db_persist' => false, // optional - is not implemented on mysqli (read http://www.php.net/manual/en/mysqli.persistconns.php if you want to use persistent connection)
);

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

// here you can include multiple classes if you need
if ( file_exists( FAR_SQL_TYPE.'.inc.php') )
    include_once
FAR_SQL_TYPE.'.inc.php';
else
    die(
'no file '.FAR_SQL_TYPE.'.inc.php - check if file exists and is put on correct path');

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

// start sql connection
// @Note: From revision 6 of the sql class you need to initialize the class with specific name
// example:
// for mysql class use new MYSQL_BD()
// for sqlite3 class use new SQLITE3_BD()
// for mssql class use new MSSQL_BD()
// This is require if you want to use multiple database and i need to include 2 or more classes to initialize properly
// before revizion 6 of the class you can not initialize 2 different classes (my bad)
$db = new MYSQLI_DB($db_info['server'], $db_info['port'], $db_info['username'], $db_info['password'], $db_info['db_name'], $db_info['db_encoding']);
if( !
is_object($db->conn) || $db->sql_tracer[0]['error_nr'] == 1045 ) {
    echo
'<br>Error initializing the database connection.<br>';
    echo
'<pre>'.var_export($db->sql_tracer,1).'</pre>';
    exit;
} else {
    echo
'<br>Specified database connection was made successfully.';
   
//echo '<pre>'.var_export($db->sql_tracer,1).'</pre>';
   
echo '<hr>';
}
// end sql connection

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

// start creating new table
$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)
)'
;
$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
$query = "INSERT INTO test
    (camp1, valoare, data_ins) VALUES
    ('a', 'b', 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
'<hr>';
}
// end delete table

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

// 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();

//unset($db);
// done
?>


  Files folder image Files  
File Role Description
Accessible without login Plain text file example.php Example Example of using sql classes.
Accessible without login Plain text file example_2_db.php Example Example script
Accessible without login Plain text file example_from_mysqli_to_sqlite3.php Example Example script
Accessible without login Plain text file example_mysqli_php5x.php Example Example script
Accessible without login Plain text file example_mysql_php4x-5x.php Example Example script
Accessible without login Plain text file mssql.inc.php Class MSSQL class
Accessible without login Plain text file mysql.inc.php Class MySQL class
Accessible without login Plain text file mysqli.inc.php Class Class source
Accessible without login Plain text file sqlite2.inc.php Class SQLite ver. 2 class
Accessible without login Plain text file sqlite3.inc.php Class SQLite ver. 3 class

 Version Control Unique User Downloads Download Rankings  
 100%
Total:768
This week:1
All time:4,400
This week:560Up