How to use MySqli in PHP

How to use MySqli in PHP

Hello everyone, in this article we are going to see how to use MySqli for our database operations and how to manage MySql Databases with MySqli in PHP on some examples...

Let's begin.

Firstly I want to make a description about What is MySqli?

We all people who work on PHP should have any idea about PDO and MySql in PHP. Under PHP 5 you can see the commands with prefix "mysql_" to make some operations on MySql database. After PHP5 Object Oriented Programming features has been added to the PHP and also Improved MySql structure has been added to PHP as MySqli.

So now we can say MySqli stands for Mysql Improved.

With mysqli you can use the commands with the methods which have prefix "mysqli_" or you can use them with OOP structure. In this article we are going to make example with both of them.

Let's First connect to the MySql Database:

Below Code block you will see the procedural code method of the MySqli database connection:


//Here we initialized a mysqli connection
$mysqli = mysqli_connect("localhost", "username", "password", "db_name");
//And we closed mysqli connection
mysqli_close($mysqli );

And below code block you will see the OOP structure of the MySqli database connection.


//Here we initialized a mysqli connection
$mysqli = new mysqli("localhost", "username", "password", "db_name");
//And we closed mysqli connection
$mysqli->close();
Now we need to make basic operations with MySqli. These operations are INSERT, DELETE, SELECT and UPDATE.

I have created a table named students and below image you will see the columns of this table.

Example MySql Table

Let's insert some data to our database first.

We will use below variables to add.


$name = "Burak Hamdi";
$surname = "TUFAN";
$grade = 33;

This code block will insert data with procedural code


$mysqli = mysqli_connect("localhost", "username", "password", "db_name");

if (!$mysqli ) {
    die("Connection failed: " . mysqli_connect_error());
}

$query_string= "INSERT INTO students (name,surname,grade)VALUES ('$name', '$surname', '$grade')";
if (mysqli_query($mysqli , $query_string)) {
      echo "New student added successfully";
} else {
      echo "Error : " . mysqli_error($mysqli );
}

And below code block will make it with OOP structure


// Create and check the connection is successfully connection
$mysqli = new mysqli("localhost", "username", "password", "db_name");
if ($mysqli ->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$query_string= "INSERT INTO students (name,surname,grade)VALUES ('$name', '$surname', '$grade')";

if ($mysqli->query($query_string)) {
    echo "New student added successfully";
} else {
    echo "Error:  " . $mysqli->error;
}

Now we can select some data from MySql Database. After this part I assume the database connection provided above and I will not re initialize the database connection.

Below code blocks you will see how to build a select query.

In here first we build a query and then we processed our query with MySqli.

Below code block you will see the procedural code block of the MySqli query


$query_string = "SELECT *  FROM students ";
$result = mysqli_query($mysqli, $query_string );

if (mysqli_num_rows($result ) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "ID : " . $row["id"]. 
                  " Name Surname : " . $row["name"]. " " . $row["surname"] . 
                  " Grade : " . $row["grade"] .  "<br>";
    }
} else {
    echo "No row found";
}

Below code block you will see the OOP codes of the MySqli query


$query_string = "SELECT * FROM students ";
$result = $mysqli->query($query_string );

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "ID : " . $row["id"]. 
                  " Name Surname : " . $row["name"]. " " . $row["surname"] . 
                  " Grade : " . $row["grade"] .  "<br>";
    }
} else {
    echo "No row found";
}
We need to be carefull to check the result count size. If we do not maybe we may try to do somethings with the empty array in page. This will cause of an exception.

Now we make DELETE and UPDATE queries.

Below code block you will see the procedural MySqli query.


$update_query_string = "UPDATE students  SET grade=44 WHERE id=1";
if (mysqli_query($mysqli, $update_query_string )) {
    echo "Student Information updated successfully";
} else {
    echo "Error : " . mysqli_error($mysqli);
}

$delete_query_string= "DELETE FROM students  WHERE id=1";
if (mysqli_query($mysqli, $delete_query_string)) {
    echo "Student deleted successfully";
} else {
    echo "Error : " . mysqli_error($mysqli);
}

Blow code block you will see the OOP MySql query


$update_query_string = "UPDATE students  SET grade=44 WHERE id=1";
if ($mysqli->query($update_query_string ) ) {
    echo "Student Information updated successfully";
} else {
    echo "Error : " . $mysqli->error;
}

$delete_query_string= "DELETE FROM students  WHERE id=1";
if ($mysqli->query($delete_query_string)) {
    echo "Student deleted successfully";
} else {
    echo "Error : " . $mysqli->error;
}
Also you can get the last inserted id value from mysqli right after inserting. Below you can see it:


$query_string= "INSERT INTO students (name,surname,grade)VALUES ('$name', '$surname', '$grade')";
if (mysqli_query($mysqli , $query_string)) {
      $last_inserted = mysqli_insert_id($mysqli);
    echo "New student added successfully. Last inserted id is " . $last_inserted;
} else {
      echo "Error : " . mysqli_error($mysqli );
}
This is the OOP structure to get last inserted datas id value.

$query_string= "INSERT INTO students (name,surname,grade)VALUES ('$name', '$surname', '$grade')";

if ($mysqli->query($query_string)) {
    $last_inserted = $mysqli->insert_id;
    echo "New student added successfully. Last inserted id is " . $last_inserted;
} else {
    echo "Error:  " . $mysqli->error;
}
Also you can prevent the sql injection characters inside a data with real_escape_string($string).

//OOP method
$name = $mysqli -> real_escape_string("some data may have sql injection characters");
//Procedural code
$name = mysqli_real_escape_string($mysqli, "some data may have sql injection characters");
Also you can see how many datas affected from your last query with affected_rows()

//OOP Code
$mysqli -> query("DELETE FROM students WHERE id<1000");
echo "Affected rows: " . $mysqli -> affected_rows;
//Procedural Code
mysqli_query($mysqli, "DELETE FROM students WHERE id<1000");
echo "Affected rows: " . mysqli_affected_rows($mysqli);

That is all in this article.

Have good MySqling on databases.

Burak Hamdi TUFAN


Tags


Share this Post

Send with Whatsapp

Post a Comment

Success! Your comment sent to post. It will be showed after confirmation.
Error! There was an error sending your comment.

Comments

  • There is no comment. Be the owner of first comment...