Open In App

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

Last Updated : 18 Apr, 2022
Improve
Improve
Like Article
Like
Save
Share
Report

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


Similar Reads

Create a chart from JSON data using Fetch GET request (Fetch API) in JavaScript
In this article, we are going to create a simple chart by fetching JSON Data using Fetch() method of Fetch API. The Fetch API allows us to conduct HTTP requests in a simpler way. The fetch() method: The fetch method fetches a resource thereby returning a promise which is fulfilled once the response is available. Syntax: const response = fetch(resou
3 min read
What is the difference between react-fetch and whatwg-fetch in React.js ?
React-fetch and Whatwg-fetch are both libraries for making HTTP requests in a React application, but they are used for different purposes and have different features. Table of Content React-fetchWhatwg-fetchDifference between React-fetch and Whatwg-fetchReact-fetch: `react-fetch` is a library offering a higher-order component (HOC) for streamlined
2 min read
How to Limit Query Results in SQL?
In this article, we will learn how to limit query results in SQL using different examples. A MySQL supports the LIMIT clause to select a limited number of records. If we want to LIMIT the number of results that will return us a number of rows then we simply use the LIMIT command. Step 1: Creating the Database Use the below SQL statement to create a
3 min read
SQL Query to Compare Results With Today's Date
In this article, we will see SQL Query to Compare results with Today's date by comparing the data with today's date using GETDATE() function of SQL. For comparison of the dates, we can use the CASE() function GETDATE() function: This function is used to return the present date and time of the database system. Features: It returns the current date a
2 min read
How to Fetch XML with Fetch API in JavaScript ?
The JavaScript fetch() method retrieves resources from a server and produces a Promise. We will see how to fetch XML data with JavaScript's Fetch API, parse responses into XML documents, and utilize DOM manipulation for streamlined data extraction with different methods. These are the following methods: Table of Content Using response.text() Method
3 min read
How to Execute SQL Server Stored Procedure in SQL Developer?
A stored procedure is a set of (T-SQL ) statements needed in times when we are having the repetitive usage of the same query. When there is a need to use a large query multiple times we can create a stored procedure once and execute the same wherever needed instead of writing the whole query again. In this article let us see how to execute SQL Serv
2 min read
Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
Structured Query Language (SQL): Structured Query Language (SQL) has a specific design motive for defining, accessing and changement of data. It is considered as non-procedural, In that case the important elements and its results are first specified without taking care of the how they are computed. It is implemented over the database which is drive
2 min read
Difference Between Execute(), query() and Update() Methods in Java
Before getting started, let us have prior knowledge of parameters that makes use of the following three queries parameters, which are as follows: boolean execute(String SQL): Returns a boolean value of true if a ResultSet object can be retrieved; otherwise, it returns false. Use this method to execute SQL DDL statements or when you need to use trul
4 min read
SQL Query to Add Email Validation Using Only One Query
In this article let us see how can we check for the validation of mails in the student database using MSSQL as the database server. For example, if the email is like abcdedfABCDEF...0123456@gmail.com this is the valid form of an email if it is other than this then that is said to Invalid. So, now we will discuss this concept in detail step-by-step:
2 min read
How to Execute SQL File with Java using File and IO Streams?
In many cases, we often find the need to execute SQL commands on a database using JDBC to load raw data. While there are command-line or GUI interfaces provided by the database vendor, sometimes we may need to manage the database command execution using external software. In this article, we will learn how to execute an SQL file containing thousand
5 min read