MySQLi is improved extension of MySQL extension. It suits well for InnoDB MySQL database because MySQLi provides transaction processing functions to keep your database accurate and reliable. In this article, we will show you how to utilize most of this MySQLi extension, such as: connecting and disconnecting from database server, perform transaction processing, executing sql queries, retrieving database records, counting selected records.
<?php
//By : Xhanch Studio
//URL : http://xhanch.com/
//Specify your MySQL database server (Can be the server name or IP Address)
$db_server = 'localhost';
//Specify your database name
$db_name = 'database_name';
//Specify your database user
$db_user = 'username';
$db_pass = 'password';
//Set up a connection to MySQL database server
$conn = @mysqli_connect(
$db_server,
$db_user,
$db_pass,
$db_name
);
//Checking connection state
if(!$conn){
//Failed to connect to MySQL database server. May be caused by incorrect server name,
//database name, database user or password
die('Cannot connect to database server !');
}
//Executing an SQL query
$sql = 'write your query here';
$res = @mysqli_query($conn,$sql);
if(!$res){
//Failed to execute query, may be caused by false query, incorrect query syntax
die('Query error');
}
//If you want to know how many records were affected beacuse of the execution
//of a query
echo mysqli_affected_rows($conn);
//If you are inserting a new record to a table that have an auto number field
//you can get the value for that field after a record has been inserted
echo mysqli_insert_id($conn);
//Retrieving database records
$sql = 'write your select query here';
$res = @mysqli_query($conn,$sql);
if(!$res){
//Failed to execute query, may be caused by false query, incorrect query syntax
die('Query error');
}
//Query successfully executed
//If you want to know the result count
echo mysqli_num_rows($res);
//Start reading results
while($row = mysqli_fetch_array($res)){
//Dumping/displaying current record/row
var_dump($row);
//To display a particular field, you may use this syntax
echo $row['field_name'];
}
//Free memory from storing query result
mysqli_free_result($res);
//Perform a transaction processing
//Disable auto commit
mysqli_autocommit($conn, false);
try{
$sql = 'write query 1 here';
$res = @mysqli_query($conn,$sql);
if(!$res){
//Failed to execute query, may be caused by false query, incorrect query syntax
throw new Exception(mysqli_error($conn));
}
$sql = 'write query 2 here';
$res = @mysqli_query($conn,$sql);
if(!$res){
//Failed to execute query, may be caused by false query, incorrect query syntax
throw new Exception(mysqli_error($conn));
}
$sql = 'write a query 3 here';
$res = @mysqli_query($conn,$sql);
if(!$res){
//Failed to execute query, may be caused by false query, incorrect query syntax
throw new Exception(mysqli_error($conn));
}
//All queries have been successfully executed
//Commit all queries/operations
mysqli_commit($conn);
}catch(Exception $e){
//Error occurred in one of your queries
//Roll back all executed queries/operations
mysqli_rollback($conn);
//Displaying error
die($e->getMessage());
}
//Enable auto commit
mysqli_autocommit($conn, true);
//Closing MySQL connection
mysqli_close($conn);
?>
Related posts:
You may donate
any amount
if you want to thank for
our free services
