How to execute an SQL query and fetch results using PHP ?

In this article, we will discuss how to execute an SQL query and how to fetch its result?

We can perform a query against the database using the PHP mysqli_query() method. 

Syntax: We can use the mysqli_query( ) method in two ways:

• Object-oriented style
• Procedural style

Parameters:

• connection: It is required that specifies the connection to use.
• query: It is also required that specifies the database query.
• result mode: It is optional to use.

Return value: For SELECT, SHOW, DESCRIBE, or EXPLAIN it returns a mysqli_result object. For other successful queries, it returns true. Otherwise, it returns false on failure.

Let’s understand how we can execute an SQL query.

Executing an SQL query: We will understand how we can execute an SQL query with an example. We will create a database, table and then insert some values into it.

Example: Create a database and insert some values into it.

PHP

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";

    $conn = new mysqli($servername, $username, $password);
    if ($conn->connect_error) {
          die("Connection failed: " . $conn->connect_error);
    }

    $sql = "CREATE DATABASE gfgDB";
    if ($conn->query($sql) === TRUE) {
          echo "Database has been created successfully";
    } else {
          echo "Error creating database: " . $conn->error;
    }
    $conn->close();
?>

Output:

Database has been created successfully

Creating the table:

PHP

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "gfgDB";

    $conn = new mysqli($servername, $username, $password, $dbname);
        if ($conn->connect_error) {
              die("Connection failed: " . $conn->connect_error);
        }

    $sql = "CREATE TABLE Emp (
        id INT(6) AUTO_INCREMENT PRIMARY KEY,
        firstname VARCHAR(30) NOT NULL,
        lastname VARCHAR(30) NOT NULL
    )";

    if ($conn->query($sql) === TRUE) {
          echo "Table has been created successfully";
    } else {
          echo "Error creating table: " . $conn->error;
    }

    $conn->close();
?>

Output:

Table has been created successfully

Inserting some values into the table “Emp”:

PHP

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "gfgDB";

    $conn = new mysqli($servername, $username, $password, $dbname);
    if ($conn->connect_error){
          die("Connection failed: " . $conn->connect_error);
    }
    $sql = "INSERT INTO Emp (firstname, lastname)
            VALUES ('XYZ', 'ABC')";

    if ($conn->query($sql) === TRUE) {
          echo "New record created successfully";
    }else{
          echo "Error: " . $sql . "<br>" . $conn->error;
    }
    $conn->close();
?>

Output:

New record created successfully

Note: Since we have used AUTO_INCREMENT, it will automatically insert the record with “id=1” and for each newly inserted record, it will increase the “id” by one.

Fetching results from the database:

PHP

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "gfgDB";

    $conn = new mysqli($servername, $username, $password, $dbname);
    if ($conn->connect_error) {
          die("Connection failed: " . $conn->connect_error);
    }

    $sql = "SELECT id, firstname, lastname FROM Emp";
    $result = $conn->query($sql);
    if ($result->num_rows > 0) {
          while($row = $result->fetch_assoc()) {
            echo "id: " . $row["id"]. " - Name: "
                . $row["firstname"]. " " . $row["lastname"]. "<br>";
          }
    }
    else {
          echo "No records has been found";
    }
    $conn->close();
?>

Output:

id: 1 - Name: XYZ ABC