MySqli is updated version and gives an object oriented and the number of enhancement over the regular MySql extension. These extensions are much faster, efficient and totally secure against SQL injections. In this post, we will understate the basic usage of MySqli, such as connect, insert, select, update and delete operations with many options with MySQL details.
Connect to Database
We can able to make a connection with Database using MySqli in two methods. It’s procedural and object-oriented method. For the secure purpose we preferred object-oriented method and also it is faster and efficient. The procedural method is similar to Mysql concept.
|
1 2 3 4 5 6 7 |
//object oriented method $mysqli = new mysqli('DB_host_name','DB_username','DB_password','DB_name'); //Display the connection error and exit the compilation if ($mysqli->connect_error) { die('Connection Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error); } |
Fetch the record details from Database
We want to show the details from Database means first execute the select query with the help of connection object. Select records with many methods like fetch_assoc(), fetch_array() and fetch_object().
fetch_assoc() : Following code, use to fetch the records with an associative array. The fetching database result array has a column name as key and value for the corresponding column.
|
1 2 3 4 5 6 |
$results = $mysqli->query("SELECT column1, column2, column3 FROM table_name"); while($row = $results->fetch_assoc()) { // list of proceeding codes echo $row["column_name"]; } |
fetch_array() : This method returns an array of both MySqli fetch_row() and MySqli fetch_assoc() joined mutually, it is an advance version of the mysqli_fetch_row() method. It will handle even number and string can be used as a key to access the result data.
|
1 2 3 4 5 6 |
$results = $mysqli->query("SELECT column1, column2, column3 FROM table_name"); while($row = $results->fetch_array()) { // list of proceeding codes echo $row["column_name"]; } |
fetch_object() : To get the database result set as an objects, just use MySqli fetch_object(). Here the data will be display by the object key. Object key as the column name in a database table.
|
1 2 3 4 5 6 |
$results = $mysqli->query("SELECT column1, column2, column3 FROM table_name"); while($row = $results->fetch_object()) { // list of proceeding codes echo $row->column_name; } |
Get the Total records count with fetching the help of select query result. fetch_row() method is used to get the count for result set data.
|
1 |
echo $row_count = $results->fetch_row(); // It will return integer value |
Insert one record set using MySqli:
Below code is use to save the particular details into a database using connection object in MySqli.
|
1 2 3 4 5 6 7 |
$insert_row = $mysqli->query("INSERT INTO products (column1, column2, column3, column4) VALUES('value1', 'value2', 'value3', 'value4')"); if($insert_row){ echo 'Details Inserted Successfully! <br/> Last inserted record Id is : ' .$mysqli->insert_id .'<br/>'; }else{ die('Query Error : ('. $mysqli->errno .') '. $mysqli->error); } |
Update the existing record details:
Below code is use to update or change the existing details into new details with the specific condition or key sets.
|
1 2 3 4 5 6 7 |
$results = $mysqli->query("UPDATE table_name SET column1='value1', column2='value2', column3='value3' WHERE condition_column='condition_value'"); if($results){ print 'Details updated Successfully!'; }else{ print 'Query Error : ('. $mysqli->errno .') '. $mysqli->error; } |
Remove or delete existing record details:
Below code is use to remove or delete particular records from a database.
|
1 2 3 4 5 6 7 |
$results = $mysqli->query("DELETE FROM table_name WHERE condition_column='condition_value'"); if($results){ print 'Record Deleted Successfully!'; }else{ print 'Query Error : ('. $mysqli->errno .') '. $mysqli->error; } |
