<?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();
?>
|
Download
