Table of Contents
Introduction
Whatever we see on our website, it is either saved within HTML or being fetched from a table. The HTML has a script file attached with it which calls backend, where SQL codes are written. These SQL codes interact with database. In this article we will learn how to implement read data call using SQL in PHP.
Syntax
SELECT * FROM <table name> WHERE <field name> = '{<local variable>}' ORDER BY <required field name> ASC|DESC
How to read Data from SQL using PHP
In PHP, we need to follow given mandatory steps before we start with any SQL operation:
In PHP, we need to follow given mandatory steps before we start with any SQL operation:
- Define database details
We need to initialize a database connection and for that we will need its detail. The prerequisite for this will include setup of a database in myphpadmin (if we are using local server). There we will have to create a database and tables.
In case you have done setup of database and tables in local server: https://localhost/phpmyadmin/
Then, following will be the database details written in PHP:
$servername = "localhost"; $username = "root"; $dbname = "<your_database_name>";
- Create connection
Once the required connection details are mentioned, we will try to create a connection to database using below query:$conn = new mysqli($servername, $username,"", $dbname);
- Check connection
Now the connection setup is done, we need to perform a check to confirm the same using below query:
if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); }
- Perform Query
This is the part where we write SQL query to read data in PHP. In the below query, we pass the query via connection and get the response in a variable called $result. If the result variable is not empty then we echo (i.e. send back to frontend) the data, else we return a message “No Data”:
if ($result = $conn -> query("SELECT * FROM transactions WHERE ReceivedDate = '{$date}' ORDER BY timestamp DESC")) { if($result->num_rows > 0) { $i = 0; while($row = $result->fetch_assoc()){ $dataArray[$i] = $row; $i = $i + 1; } echo json_encode($dataArray); } } else{ $dataArray[0] = 'No data'; echo json_encode($dataArray); }
- Close Connection
Once we are done with our operation, then it is a good practise to close the database connection using this simple query:
$conn->close();
Full code:
//get all transaction with parameter function onGetAllTransactions(){ // define connection variables $servername = "localhost"; $username = "root"; $dbname = "<your_database_name>"; // Create connection $conn = new mysqli($servername, $username,"", $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $obj = json_decode($_POST["data"]); $date = $obj->date; // Perform query if ($result = $conn -> query("SELECT * FROM transactions WHERE ReceivedDate = '{$date}' ORDER BY timestamp DESC")) { if($result->num_rows > 0) { $i = 0; while($row = $result->fetch_assoc()){ $dataArray[$i] = $row; $i = $i + 1; } echo json_encode($dataArray); } } else{ $dataArray[0] = 'No data'; echo json_encode($dataArray); } $conn->close(); }
Calling PHP to read all data from frontend
In above steps we have connected to SQL, fetched data and sent in response. The above function is only triggered once the UI calls it. In UI, we write given code to perform fetch operation:
var http = "http://"; var uri = http + "localhost/<location_of_your_php_file/name_of_your_php_file>"; $.ajax({ url: that.uri, type: "POST", data: { method: "onGetAllTransactions", data: JSON.stringify({ date: today }) }, dataType: "json", success: function (data) { if(data === "No data"){ // Raise Error MessageBox.error("Unable to fetch Transaction Data, Selected Date has no data"); } else{ // perform UI query with your data } }, error: function (request, error) { // Raise Error MessageBox.error("Unable to fetch Transaction Data, Either selected Date has no data or your server connection has issues!"); } });
0 Comments