<?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
?>
|
Download
