PHP provides three api to connect mysql Database.
1. MySql
2. MySqli
3. PDO
Out of these three MySql extension is deprecated as of PHP 5.5.0, and will be removed in the future.
MYSQLI is an improved object oriented extension introduced in PHP 5.
Advantages of MySqli over MySql
1. Support Object Oriented interface.
2. Concept of prepared statements is introduced which prevents sql injection.
3. Improved over all performance as compared to mysql extension.
MySqli Tutorial: PHP MySqli Extension
Let’s Connect MySql Database with MySqli extension.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<?php $server = 'localhost'; $user = 'root'; $password = ''; $dbname = 'example'; // For demostration purpose i use dbname example. // Connect with mysqli. Creating Object. $connection = new mysqli($server,$user,$password,$dbname); /* If connection is successful, <strong>connect_errno method returns 0</strong> otherwise specific errno.*/ if($connection->connect_errno){ die('Connection problem'); } ?> |
Select Data Using MySqli
Let’s Select some data from emp table. MySqli Provides following fetch Modes.
1. fetch_object().
2. fetch_assoc().
3. fetch_array().
Here i am using fetch_object() to fetch records as an object.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
<?php /* I left password field blank, if you have set any password for mysql then enter it.*/ $connection = new mysqli('localhost','root','','example'); if($connection->connect_errno){ die('some error occurred while connection to Database'); } $result = $connection->query("select * from emp"); /* Fetch record as an object */ while($data = $result->fetch_object()){ echo "Name:".$data->name."Designation:".$data->designation."\n"; } // Let's free the memory $result->free(); // Connection close $connection->close(); |
Insert Using MySQLi
NOTE: If you are taking any user input and inserting into database it is advisable that you always validate them. Mysqli also provides real_escape_string method which escape special character in a string.
Take an example from user registration system, where user choose his/her username and password.
How to Prevent Application From sql Injection
Tools to Check Sql Injection Vulnerability
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
<?php //values to be inserted in database table. Taking username and password from users. // real_escape_string escape special characters. $username = $connection->real_escape_string($_REQUEST['username']); $password = $connection->real_escape_string($_REQUEST['password']); $insert_row = $connection->query("insert into users (username,password) values ('$username','$password')"); if($insert_row){ // Return last insert id echo $connection->insert_id."\n"; // Returns how many rows is affected echo $connection->affected_rows."\n"; }else{ echo "error"; } ?> |
Recommended Books For PHP And MySql
PHP And MySql Best Books on Flipkart
In next MySqli Tutorial, i’ll show how to use prepared statement in MySqli.
