Display All Data from a MySql Table

After you have created the table and entered the data, you must enable their display and selection.
To display (fetch) data from a MySql table used to the SQL SELECT statement through PHP function mysql_query:
SELECT * FROM table_name
There are several options to fetch data from MySQL.
Most frequently used options is to use PHP methods: mysql_fetch_assoc()", "fetch_object()", and "fetch_all()".

How to fetch data from MySQL database and display in HTML table

In this example, we display the entire mysql table in HTML. In order to achieve this, it is necessary to list all the columns of the table, either in the original order or in the modified order.


<style>
table{
    border: 3px solid black;
      border-collapse: collapse;
       }
 th, td {
     border: 2px solid black;
     border-collapse: collapse;
       }
</style>
<?php
$dbhost = "localhost";
$dbuser = "yourusername";
$dbpass = "yourpassword";
$dbname = "yourdatabase";

$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
                          } 
 $result = mysqli_query($conn,"SELECT * FROM  `oscar_winners`");
     echo "<table>
     <tr><th>ID</th><th>MOVIE</th><th>YEAR</th><th>WINS</th></tr>";
     while($row = $result->fetch_assoc()) 
     echo "<tr><td>" . $row["id"]. "</td><td>" . $row["movie"]. " </td><td>" . $row["year"]. "</td><td>" . $row["wins"]. "</td></tr>";
     echo "</table>";
?>
  

result:
IDMOVIEYEARWINS
1Dances with Wolves 19907
4Forrest Gump 19946
6Gladiator 20005
3Schindler's List 19937
2The Silence of the Lambs 19915
5Titanic 199711



How to Retrieve Data from Specific Rows in MySQL Tables

If you do not want to display all records from the MySql table, you can use VHERE condition that you see only the data that matches a specific condition as shown below:


<?php
$dbhost = "localhost";
$dbuser = "yourusername";
$dbpass = "yourpassword";
$dbname = "yourdatabase";

$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
                          } 
 $result = mysqli_query($conn,"SELECT * FROM  `oscar_winners` where `id` IN (1, 3, 5)");
    
     while($row = $result->fetch_assoc()) 
     echo "ID: $row[id] "."-MOVIE: $row[movie] "."YEAR: $row[year] "." -WINS: $row[wins]<br> ";
?>

result:

ID: 1 -MOVIE: Dances with Wolves YEAR: 1990 -WINS: 7
ID: 3 -MOVIE: Schindler's List YEAR: 1993 -WINS: 7
ID: 5 -MOVIE: Titanic YEAR: 1997 -WINS: 11

This is an example of how it is possible to embed MySQL table data in HTML code.


<?php
$dbhost = "localhost";
$dbuser = "yourusername";
$dbpass = "yourpassword";
$dbname = "yourdatabase";

$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
                          } 
 $result = mysqli_query($conn,"SELECT * FROM  `oscar_winners` where `id`=2");
    
     while($row = $result->fetch_assoc()) {
?>

<p>This is oscar winner for  <?php echo $row["year"]; ?>  year.</p>

<p>Best movie is: <?php echo $row["movie"]; ?>.</p>

<p>The total number of awards is <?php echo $row["wins"]; ?>.</p>

<?php } ?>

result:

This is oscar winner for 1991 year.

Best movie is: The Silence of the Lambs.

The total number of awards is 5.





Select a Particular Row from a MySQL Table Based on Values Record

Use the IN operator to select rows where the value is in an array.
Also the IN operator allows you to specify multiple values in a WHERE clause.


<?php

$dbhost = "localhost";
$dbuser = "yourusername";
$dbpass = "yourpassword";
$dbname = "yourdatabase";

$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
                       } 

 $result = mysqli_query($conn,"SELECT * FROM  oscar_winners
 where  movie IN (\"The Silence of the Lambs\",\"Forrest Gump\")");
  
    while($row = $result->fetch_assoc()) 
     echo "ID: $row[id] "."-MOVIE: $row[movie] "."YEAR: $row[year] "." -WINS: $row[wins]<br> ";
?>

result:

ID: 2 -MOVIE: The Silence of the Lambs YEAR: 1991 -WINS: 5
ID: 4 -MOVIE: Forrest Gump YEAR: 1994 -WINS: 6





Privacy Policy