MySQLi Basics

Say goodbye to MySQL

PHP team announced to deprecate MySQL extension in 2011. Old MySQL extension officially deprecated since PHP 5.5.0 in late 2012 and it will be removed in the future. One of the alternatives since PHP 5 and later is MySQLi (“i” stands from “improved”). Millions of projects have been written using PHP and MySQL. It is not easy to immediately migrate old projects, however you must learn to use MySQLi in your new projects.

MySQLi has a few new features such as:

  • Dual object-oriented (OO) and procedural interfaces
  • Support for prepared statements
  • Ability to process multiple statements

How to use MySQLi?

We are here to discuss the basic usage of MySqli. So without further ado, I’d like to show you how we can connect, select, insert, update and delete records using PHP MySqli. I am sure this list will come in handy in times of need.

Connect to Database

<?php
    $connection = mysqli_connect($DB_SERVER, $DB_USER, $DB_PASS, $DB_NAME);

    //check connection
    if (mysqli_connect_errno()) {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
?>

The procedural style is similar to MySQL is very similar to MySQL and it may be helpful to the people who are just switching to MySQLi. However, there is an object oriented style also. I will stick to the procedural style in this article so that it looks a bit more familiar to you.

SELECT query

<?php
    $sql  = "SELECT id, row1, row2 from table1";
    $result = mysqli_query($connection, $sql);

    //result is an associative array containing data
    while($row  = mysqli_fetch_array($result) {
        echo $row['id'].'<br/>';
        echo $row['row1'].'<br/>';
        echo $row['row2'].'<br/>';
    }
?>

INSERT query

<?php
    $sql = "INSERT into table1(row1, row2) VALUES('{$row1}', '{$row2}')";

    if (mysqli_query($connection, $sql) {
        echo "Success";
    } else {
        echo mysqli_error($connection);
    }
?>

DELETE query

<?php
        $sql = "DELETE from table1 where id=1";

        if(mysqli_query($connection, $sql) {
            echo mysqli_affected_rows($connection)."Rows Deleted";
        } else {
            echo mysqli_error($connection);
        }
?>

This content has been helpful to you?

Thanks for contributing!

Yes No