Displaying table data from PHP
PHP mysqli
fetch_row() Function
mysqli_fetch_row() function fetches one row from a result-set
and returns it as an enumerated array.
Syntax
Object oriented style: $mysqli_result -> fetch_row()
Procedural style: mysqli_fetch_row(result)
Example - Procedural style
Fetch rows from a result-set:
<?php
$con =
mysqli_connect("localhost","my_user","my_password","my_db");
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " .
mysqli_connect_error();
exit();
}
$sql = "SELECT Lastname, Age FROM Persons ORDER BY Lastname";
if ($result = mysqli_query($con, $sql)) {
// Fetch one and one row
while ($row = mysqli_fetch_row($result)) {
printf ("%s (%s)\n", $row[0], $row[1]);
}
mysqli_free_result($result);
}
mysqli_close($con);
?>
Output:-
Fetch data from the database and using the UPDATE query you can edit a particular row of data.
example:
use the following steps
Step 1:
Connection with Database
The dbConn.php file
is used to connect with the database.
it is
a common file which is connected with MySQL database.
Use
only dbConn.php file
name on that file where you want to perform a task with the database, don't
need to make a connection every time in every file.
dbConn.php
<?php
$db = mysqli_connect("localhost","root","","testDB");
if(!$db)
{
die("Connection failed: " . mysqli_connect_error());
}
?>Step 2: Fetch data from the Database
The all_records.php file
is used for displaying records from the database.
This
file uses a table for displaying records in the proper format.
Using mysqli_fetch_array() function,
fetching records from the database and also adding an edit button on every data
of row.
mysqli_fetch_array() function
mysqli_fetch_row() function fetches one row from
a result-set and returns it as an enumerated array.
Syntax
Object oriented style: $mysqli_result -> fetch_row()
Procedural style: mysqli_fetch_row(result)
When clicking on the edit button, it will redirect to edit.php file and also fetch those data from the database through id and display those data into the textbox.
After
correction of those data clicks on the Update button, now those data are updated
successfully into the database and display the correct records.
all_records.php
<html>
<head>
<title>Display all records from Database</title>
</head>
<body>
<h2>Employee Details</h2>
<table border="2">
<tr>
<td>Sr.No.</td>
<td>Full Name</td>
<td>Age</td>
<td>Edit</td>
<td>Delete</td>
</tr>
<?php
include "dbConn.php"; // Using database connection file here
$records = mysqli_query($db,"select * from tblemp"); // fetch data from database
while($data = mysqli_fetch_array($records))
{
?>
<tr>
<td><?php echo $data['id']; ?></td>
<td><?php echo $data['fullname']; ?></td>
<td><?php echo $data['age']; ?></td>
<td><a href="edit.php?id=<?php echo $data['id']; ?>">Edit</a></td>
<td><a href="delete.php?id=<?php echo $data['id']; ?>">Delete</a></td>
</tr>
<?php
}
?>
</table>
</body>
</html>
Step 3: Edit
Code
The edit.php file
is used for executing an update query.
The id of
that particular row comes through query string and fetch those data from that
id and displays data into the textbox.
After executing the update query it redirects to the all_records.php file.
So the
update query updates the row.
edit.php
<?php
include "dbConn.php"; // Using database connection file here
$id = $_GET['id']; // get id through query string
$qry = mysqli_query($db,"select * from tblemp where id='$id'"); // select query
$data = mysqli_fetch_array($qry); // fetch data
if(isset($_POST['update'])) // when click on Update button
{
$fullname = $_POST['fullname'];
$age = $_POST['age'];
$edit = mysqli_query($db,"update tblemp set fullname='$fullname', age='$age' where id='$id'");
if($edit)
{
mysqli_close($db); // Close connection
header("location:all_records.php"); // redirects to all records page
exit;
}
else
{
echo mysqli_error();
}
}
?>
<h3>Update Data</h3>
<form method="POST">
<input type="text" name="fullname" value="<?php echo $data['fullname'] ?>" placeholder="Enter Full Name" Required>
<input type="text" name="age" value="<?php echo $data['age'] ?>" placeholder="Enter Age" Required>
<input type="submit" name="update" value="Update">
</form>
Example (MySQLi Procedural)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT id, firstname, lastname FROM MyGuests WHERE
lastname='Doe'";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "id: " .
$row["id"]. " - Name: " .
$row["firstname"]. " " .
$row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
Output
:-
id: 1 - Name: John Doe
Show the result in an HTML table: (MySQLi
Object-oriented)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " .
$conn->connect_error);
}
$sql = "SELECT id, firstname, lastname FROM MyGuests WHERE
lastname='Doe'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "<table><tr><th>ID</th><th>Name</th></tr>";
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr><td>".$row["id"]."</td><td>".$row["firstname"]."
".$row["lastname"]."</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}
$conn->close();
?>
Output
:-
|
ID |
Name |
|
1 |
John Doe |
Select and Order
Data From a MySQL Database
The ORDER BY clause is used to sort the
result-set in ascending or descending order (ascending order by default ).
To sort the records in descending order, use
the DESC keyword.
SELECT column_name(s) FROM table_name ORDER
BY column_name(s) ASC|DESC
Example (MySQLi
Procedural)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT id, firstname, lastname FROM MyGuests ORDER BY
lastname";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "id: " .
$row["id"]. " - Name: " .
$row["firstname"]. " " .
$row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
Output:-
id: 1 - Name: John Doe
id: 3 - Name: Julie Dooley
id: 2 - Name: Mary Moe
Show the result in an HTML table:
Example (MySQLi
Object-oriented)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " .
$conn->connect_error);
}
$sql = "SELECT id, firstname, lastname FROM MyGuests ORDER BY
lastname";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "<table><tr><th>ID</th><th>Name</th></tr>";
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr><td>".$row["id"]."</td><td>".$row["firstname"]."
".$row["lastname"]."</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}
$conn->close();
?>
Output:-
|
ID |
Name |
|
1 |
John Doe |
|
3 |
Julie Dooley |
|
2 |
Mary Moe |
0 Comments