php mysqli tutorial for beginners

Now a days  to develop complex web sites you must need database interaction. With PHP mostly we use MySQL. To access mysql database we can use mysqli or PDO libraries available in php. In this tutorial we are going to explore php mysqli library functions.

Mysqli extension can be used either in procedural or object oriented way. In procedural way functions are called, while in object oriented way a class object is used to perform operations on database. Mysqli extension was introduced in php version 5.PHP mysqli extension provides methods to perform different operations on database. In newer versions of PHP mysqli functions are recommended to connect, retrieve or save data to database. php mysqliextension supports object-oriented interface, prepared statements, multiple statements etc.

Connect to database using php mysqli

In order to connect to MySQL database using mysqli, mysqli_connect function is used, you need to provide host name,  username,  password and database name.

Connect to database using php mysqli – procedural way

In procedural way mysqli_connect function is used. mysql_connect function opens a connection to database server and connection object is returned. If connection to database fails  mysqli_connect_errno()throws an error.

<?php

$host     = "localhost";

$user     = "root";

$password = "";

$database = "dbusers";

$mysqli   = mysqli_connect($host, $user, $password, $database);

if (mysqli_connect_errno($mysqli)) {
   
   echo "Failed to connect to MySQL: " . mysqli_connect_error();

}

?>

Connect to database using php mysqli – object oriented way

Object oriented way to connect to database, mysqli function is called and an object is returned. In case database connection is failed error is thrown.

<?php 

$host     = "localhost";

$user     = "root";

$password = "";

$database = "dbusers";

$mysqli = new mysqli($host, $user, $password, $database);

if ($mysqli->connect_errno) {

    echo "Failed to connect to MySQL: " . $mysqli->connect_error;

}

?>

Selecting records from database using php mysqli

Records from database can be selected using procedural way or object oriented way. mysqli_queryfunction is used to perform query on database.

First connect to database, connection object and a query is passed to mysqli_query and it returns a result set. Result set is passed to mysqli_fetch_assoc function and it returns a data row as an associative array.

Select records using mysqli_fetch_assoc – procedural way

mysql_fetch_assoc returns a result set as an associative array and names of column in result set represents the keys of array. Null is returned if result set is empty.

<?php


$dblink = mysqli_connect("localhost", "root", "", "dbstudents");


/* If connection fails throw an error */

if (mysqli_connect_errno()) {

    echo "Could  not connect to database: Error: ".mysqli_connect_error();

    exit();
}

$sqlquery = "SELECT id, name, class, roll_no FROM students ORDER by id DESC LIMIT 50";


if ($result = mysqli_query($dblink, $sqlquery)) {


    /* fetch associative array */

    while ($row = mysqli_fetch_assoc($result)) {

        echo $row["name"]." ".$row["class"]." ".$row['roll_no']."<br />";
    }


    /* free result set */

    mysqli_free_result($result);
}

/* close connection */

mysqli_close($dblink);

Fetch records using  fetch_assoc – object oriented way

After database connection using new mysqli method,  database connection object is returned.  A query is passed to connection object‘s query method. This function returns a result set. Likewise procedural way a row from result set is fetched using fetch_assoc() method.

This method returns a single row of result, so we use a while loop to fetch all rows in result set.  Column names are used as array indexes to access result like $row[‘first_name’].

<?php

$mysqli = new mysqli("localhost", "root", "", "studentsdb");

/* check connection */
if ($mysqli->connect_errno) {
 
   echo "Connect failed ".$mysqli->connect_error;

   exit();
}

$query = "SELECT name, class, roll_no FROM students ORDER by id DESC LIMIT 50";

if ($result = $mysqli->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {

        echo $row["name"]." ".$row["class"]." ".$row["roll_no"]."<br />";

    }

    /* free result set */
    $result->free();

}

/* close connection */
$mysqli->close();

?>

Fetch records using mysqli_fetch_array – procedural way

mysqli_fetch_array function is used to fetch records from database. It fetches a single row from result set as numeric array using mysql_num,  associative array using mysqli_assoc or both using mysqli-both. To display records from data row,  array index or column name is used.

<?php

$link = mysqli_connect("localhost", "root", "", "dbbookstore");

/* check connection */
if (mysqli_connect_errno()) {

    echo "Database connection failed ".mysqli_connect_error();

    exit();
}

$query = "SELECT name, author_name, price FROM books ORDER by id LIMIT 5";

$result = mysqli_query($link, $query);

/* numeric array */

$row = mysqli_fetch_array($result, MYSQLI_NUM);

echo $row[0]." ".$row[1]." ".$row[2];

/* associative array */
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);

echo $row["name"]." ".$row["author_name"]." ".$row['price'];

/* associative and numeric array */
$row = mysqli_fetch_array($result, MYSQLI_BOTH);

echo $row[0]." ".$row["author_name"]." ".$row[2];


/* free result set */
mysqli_free_result($result);

/* close connection */
mysqli_close($link);

?>

Fetch records using mysqli_result::fetch_array – object oriented way

fetch_array function can fetch result row as associative, numeric array or both. It is recommended way and works same as procedural way.

<?php

$mysqli = new mysqli("localhost", "root", "", "dbbookstore");

/* check connection */
if ($mysqli->connect_errno) {

    echo "Connectin to database failed: ".$mysqli->connect_error;
    
   exit();
}


$query = "SELECT name, author_name, price FROM books ORDER by id LIMIT 10";

$result = $mysqli->query($query);

/* Fetch result as numeric array */
$row = $result->fetch_array(MYSQLI_NUM);

echo $row[0]." ".$row[1]." ".$row[2]."<br />";

/* associative array */
$row = $result->fetch_array(MYSQLI_ASSOC);

echo $row["name"]." ".$row["author_name"]." ".$row['price']."<br />";

/* Both associative and numeric array */
$row = $result->fetch_array(MYSQLI_BOTH);

echo $row[0]." ".$row["author_name"]." ".$row[2];

/* free result set */
$result->free();

/* close connection */
$mysqli->close();

?>

Fetch all records using mysqli_fetch_all

If you need to fetch all records from database, mysqli_fetch_all function is used. First connection to database is created using mysqli_connect. Next we pass connection object and SQL query to mysqli_queryfunction.

mysqli_query function returns result set. mysqli_fetch_all function returns all result rows as an associative, numeric or both.

<?php

$conn = mysqli_connect("localhost","root","","dbusers");

//Check connection
if (mysqli_connect_errno()){

  echo "Failed to connect to MySQL: " . mysqli_connect_error();

}

$result = mysqli_query($conn, "SELECT name, email FROM tbl_users ORDER BY id desc");

//Fetch all records
mysqli_fetch_all($result,MYSQLI_ASSOC);

//Free result set
mysqli_free_result($result);

mysqli_close($con);

?>

Prepared statements in php mysqli library

php mysqli library supports prepared or parameterized statements. A prepared statement works in two steps, step 1 is prepare a statement while step 2 involves execution of a prepared statement.

In prepare stage a statement template is sent to server, syntax is validated and internal resources are allocated while in execution step client binds the parameter values and sends it to server.

A statement is created by server and values are bind and using assigned internal resources statement is executed. It can execute repeated statement repeatedly with efficiency.

Insert records to database using php mysqli prepared statements

First we make a connection to database. Then prepare method is used to prepares SQL statement. Parameter markers ” ? ” are used in prepared statements. Then prepare function returns a statement handle that is used for further processing.

bind_param method binds variables to prepared statement. In this function we pass data types of variables. Data types of variable can be i as integer,  d as double, s as string and b as blob. Second parameters are the variables. The number of parameters and string type length should be same.

Prepared statement are executed by execute method. When this function is called ? placeholders or parameters markers are replaced by variable values.

<?php

$conn = new mysqli("localhost", "root", "", "dbonlinestore");

if ($conn->connect_errno) {

    echo "Database connection failed. ".$conn->connect_error;

}

$product  = "EOS 7D Mark II Canon";

$price    = "$800";

$category = "Cameras";

$sql      = "INSERT INTO tbl_products (product_name, price, category) VALUES (?, ?, ?)";

/* Prepare statement */
$stmt     = $conn->prepare($sql);

if(!$stmt) {

   echo 'Error: '.$conn->error;
}
 
/* Bind parameters */
$stmt->bind_param('sss',$product,$price, $category);
 
/* Execute statement */
$stmt->execute();
 
echo $stmt->insert_id;

$stmt->close();

?>

Update records using php mysqli prepared statements

To update records first create a connection to database. Next SQL query is prepared using preparefunction for execution. Finally records are updated by execute function.

<?php 

$conn = new mysqli("localhost", "root", "", "dbonlinestore");

if ($conn->connect_errno) {

    echo "Database connection failed (" . $conn->connect_errno . ") " . $conn->connect_error;

}

$product  = "Samsung Galaxy S7 Edge";

$price    = "$1000";

$category = "Mobile Phones";

$id       = 1;
 
/* Prepare statement */
$stmt = $conn->prepare("UPDATE tbl_products set product_name = ?, price = ?, category = ? where id = ?");

if(!$stmt) {
  trigger_error('Error: ' .$conn->error, E_USER_ERROR);
}
 
/* Bind parameters */
$stmt->bind_param('sssi',$product,$price, $category, $id);
 
/* Execute statement */
$stmt->execute();

$stmt->close();

Delete records using prepared statement php mysqli

To delete records from database using prepared statements first connect to database. Next SQL deletestatement is prepared for execution. In where clause the ? parameter marker is used.

bind_param function binds value of $id parameter. Next prepared statement is executed by executemethod and record is deleted.

<?php

$conn = new mysqli("localhost", "root", "", "dbonlinestore");

if ($conn->connect_errno) {   

  echo "Database connection failed. ".$conn->connect_errno;
  $conn->connect_error;
}

$sql = 'DELETE FROM tbl_products WHERE id = ?';

$id  = 2;
 
/*Prepare statement */
$stmt = $conn->prepare($sql);

if(!$stmt) {

  echo 'SQL error:'.$conn->error;

}
 
/* Bind parameters */
$stmt->bind_param('i',$id);
 
/* Execute statement */
$stmt->execute();
 
$stmt->close();