THECODEPROGRAM

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.

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();

I have created a table named students and below image you will see the columns of this 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";
}

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;
}

$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 );
}

$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;
}

//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");

//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