PHP MySQL Select Data

PHP SELECT Data From MySQL allows the user to select data from a database. MySQL SELECT statement allows a user to view and fetch zero or multiple rows from database tables. The SELECT statement is one of the widely used MySQL queries.

The SELECT statement returns a result that comprises rows and columns, also called a Resultset.

Read Also: PHP MySQL Multiple Insert

Select data from MySQL database in PHP

In a PHP web application, when we need to select data from a database, the first step is to establish a connection between the PHP web application and the MySQL database. After establishing a connection, use the SELECT statement to select data from the MySQL database.

SELECT Statement Syntax

The SELECT syntax consist of two parts; first, we retrieve all columns representing full table data; second, we retrieve some specific columns from a table.

Select all columns from the table

SELECT * FROM table_name

To Select some columns of data from a table

SELECT col1, col2, col3 FROM table_name

1. SELECT Query using Procedural Method

<?php 

$conn = mysqli_connect("localhost", "user_name", "password", "database"); 
if ($conn == false) { 
    die("ERROR: Could not connect. ".mysqli_connect_error()); 
} 

$query = "SELECT * FROM guest"; 
if ($result = mysqli_query($conn, $query)) { 
    if (mysqli_num_rows($result) > 0) { 
        echo "<table>"; 
        echo "<tr>"; 
        echo "<th>Firstname</th>"; 
        echo "<th>Lastname</th>"; 
        echo "<th>age</th>"; 
        echo "</tr>"; 
        while ($row = mysqli_fetch_array($result)) { 
            echo "<tr>"; 
            echo "<td>".$row['Firstname']."</td>"; 
            echo "<td>".$row['Lastname']."</td>"; 
            echo "<td>".$row['Age']."</td>"; 
            echo "</tr>"; 
        } 
        echo "</table>"; 
    } else { 
        echo "No matching records are found."; 
    } 
} else { 
    echo "ERROR: Could not able to execute $query. ".mysqli_error($conn); 
} 

mysqli_close($conn); 

?>

Output

Explanation

• The data returned by the mysqli_query() is stored in the $result variable.
• Each time mysqli_fetch_array() comes in the picture, it returns the next row from the result set as an array.
• The while loop is useful to loop through all the rows in the result set.
• The value of a field can be fetched from the row either bypassing the index or name to the $row variable like $row[‘id’] or $row[0], $row[‘firstname’] or $row[1], $row[‘lastname’] or $row[2], and $row[‘Age’] or $row[3].
• If we want to use the loop, we can obtain the loop counter value or rows returned by the SQL query bypassing the $result variable to the mysqli_num_rows() function.

2. SELECT Query using Object-Oriented Method

<?php

$conn = new mysqli("localhost", "user_name", "password", "database"); 

if ($conn == false) { 
    die("ERROR: Could not connect. ".$conn->connect_error); 
}   $query = "SELECT * FROM Data"; 

if ($result = $mysqli->query($query)) { 
    if ($result->num_rows > 0) { 
        echo "<table>"; 
        echo "<tr>"; 
        echo "<th>Firstname</th>"; 
        echo "<th>Lastname</th>"; 
        echo "<th>Age</th>"; 
        echo "</tr>"; 
        while ($row = $result->fetch_array()) { 
            echo "<tr>"; 
            echo "<td>".$row['Firstname']."</td>"; 
            echo "<td>".$row['Lastname']."</td>"; 
            echo "<td>".$row['Age']."</td>"; 
            echo "</tr>"; 
        } 
        echo "</table>"; 
        $result->free(); 
    } else { 
        echo "No matching records are found."; 
    } 
} else { 
    echo "ERROR: Could not able to execute $query. " .$conn->error; 
} 
$mysqli->close(); 
?> 

Output

3. SELECT Query using PDO Method

<?php

try { 
    $pdo = new PDO("mysql:host = localhost; dbname=database", "user_name", "password"); 
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
} catch (PDOException $e) { 
    die("ERROR: Could not connect. ".$e->getMessage()); 
} 
try { 
    $query = "SELECT * FROM Data"; 
    $result = $pdo->query($query); 
    if ($result->rowCount() > 0) { 
        echo "<table>"; 
        echo "<tr>"; 
        echo "<th>Firstname</th>"; 
        echo "<th>Lastname</th>"; 
        echo "<th>Age</th>"; 
        echo "</tr>";

        while ($row = $result->fetch()) { 
            echo "<tr>";
            echo "<td>".$row['Firstname']."</td>"; 
            echo "<td>".$row['Lastname']."</td>"; 
            echo "<td>".$row['Age']."</td>"; 
            echo "</tr>"; 
        } 

        echo "</table>"; 
        unset($result); 
    } else { 
        echo "No matching records are found."; 
    } 
} catch (PDOException $e) { 
    die("ERROR: Could not able to execute $query. ".$e->getMessage()); 
} 
unset($pdo); 

?> 

Output

Must Read: PHP MySQL WHERE Clause

Conclusion

The MySQL SELECT query is very helpful in working efficiently around databases. It is used to select data from one or more tables from a database. We hope you have been able to gain a clear understanding of how to work with the SELECT query. 

In case, you want to learn about how to connect to MySQL using PHP and creating databases, do check out our previous blogs. Happy Learning 🙂