Do you want to be a good Web Developer?

Enter Your Email To Get Our Exclusive Tutorials
And Offer Right In Your Email

Join TalkersCode Community And Recieve Exclusive Tutorials
On Web Development Right Via Email.

Basic Usage Of MySQLi With Prepared Statements For Beginners.

Friday, Jan 6, 2017

Tags:- PHP MySQLi


MySQLi is an upgraded version of MySQL.The MySQLi Extension (MySQL Improved) is a relational database driver used in the PHP programming language to provide an interface with MySQL databases.You can use MySQLi with MySQL server versions 4.1.3 and newer.In MySQLi there is considerable increase in Speed and it is more Secure than MySQL almost preventing SQL injections.

MySQLi

In this tutorial we will descibe MySQLi and MySQLi Prepared Statements and show all methods for connecting and dealing with Database.



MySQLi:-

  1. Database Connection
  2. Database connection is very easy you just have to made an object and connect this is called object oriented style and the other way is to connect with procedural style which is much similar to old MySql and it may be helpful to users who are just switching to MySqli, but it is not a recommended way.

    $db = new mysqli('host', 'UserName', 'Password', 'DatabaseName');
    
    if($db->connect_errno > 0)
    {
        die('Unable to connect to database [' . $db->connect_error . ']');
    }
    

  3. Quering the Database
  4. You can query the database with the help of database object you made while connection.

    $results = $db->query("SELECT id,name from students");
    

  5. Displaying data from Database
  6. You can display all the results you want from database just like you did in MySQL but now in this with query object.

    while($row = $result->fetch_assoc())
    {
        echo $row['id'];
    	echo $row['name'];
    }
    

  7. Number of Rows Fetched From Database
  8. You can find how many rows returned from your query with num_rows function with your query object.

    <?php
    echo 'Total results: ' . $result->num_rows;
    ?>
    

  9. Close the Database Connection
  10. You must close every database connection you made after its use with database object and same MySQL close() function.

    $db->close();
    


MySQLi Prepared Statements:-

Prepared Statements is more secure way to query the database than MySQL and MySQLi.But use of Prepared Statements is little bit of difficult because of its new concept and style of quering the database.

  1. Database Connection
  2. Database Connection is same as we did in MySQLi Object Oriented Way.

    $db = new mysqli('host', 'UserName', 'Password', 'DatabaseName');
    
    if($db->connect_errno > 0)
    {
        die('Unable to connect to database [' . $db->connect_error . ']');
    }
    


  3. Querying Database
  4. Querying is just like MySQLi way but with prepare keyword in place of query.

    $statement = $db->prepare("SELECT name FROM students WHERE id = ?");
    

    Question mark (?) is used to assign the value.In Prepared Statements we assign in the values in bind parameter function so that our query is processed in secure way and prevent from SQL injections.



  5. Bind Parameters
  6. In Prepared Statements we pass or attach the values to database query with the help of Bind Parameter function.

    $id = '2';
    $statement->bind_param('s', $id);
    

    You have to attach all the variables whose value you want in your query with thier appropriate Data Types just like we pass the 's' means the variable contains a string Data Type.
    For ex.

    $id = 2;
    $name = 'XYZ';
    $statement->bind_param('is', $id,$name);
    


  7. Executing the Query
  8. To execute the query in Prepared Statements you have to use execute() function with query object.

    $statement->execute();
    


  9. Display the results from database
  10. To display the results from database you have to first use Prepared Statements bind_result(); function the display the results in usual way.

    $statement->bind_result($name);
    
    while($statement->fetch())
    {
        echo $name;
    }
    


  11. To Free the results
  12. It is recommended to free the result to reduce the load on server.

    $statement->free_result();
    

Thats all, this is a basic usage of MySQLi and Prepared Statements for Beginners.You can customize this code further as per your requirement. And please feel free to give comments on this tutorial.

Join Us With Our 21000+ Subscribers And Get Our Latest Tutorials Update Via Email