PHP Select Data In MySQL

Submitted by alpha_luna on Thursday, May 12, 2016 - 12:22.

Related Code: Display Data From Database Table In PHP/MySQL Using PDO Query Our previous tutorial, PHP Inserting Data To MySQL. For this follow-up tutorial, we are going to create PHP Select Data In MySQL to view or to show the data from MySQL Database Table.

Select Data Using MySQLi and PDO

The following example that we select data in the table "tbl_registration" the "tbl_registration_id", "first_name", "middle_name", and "last_name". And it will show on our created page.

Using MySQLi (Object-Oriented)

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "add_query_pdo";
 
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
 
$sql = "SELECT tbl_registration_id, first_name, middle_name, last_name FROM tbl_registration";
$result1 = $conn->query($sql);
 
if ($result1->num_rows > 0) {
    // output data of each row
    while($row1 = $result1->fetch_assoc()) {
        echo "id: " . $row1["tbl_registration_id"]. " - Name: " . $row1["first_name"]. " " . $row1["middle_name"]. " " . $row1["last_name"]. "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

This is the output: Result 1

Using MySQLi (Procedural)

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "add_query_pdo";
 
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
 
$sql = "SELECT tbl_registration_id, first_name, middle_name, last_name FROM tbl_registration";
$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["tbl_registration_id"]. " - Name: " . $row["first_name"]. " " . $row["middle_name"]. " " . $row["last_name"]. "<br>";
    }
} else {
    echo "0 results";
}
 
mysqli_close($conn);
?>

This is the output: Result 2

Using PDO (PHP Data Objects)

<?php
echo "<table style='border: solid 1px black;'>";
echo "<tr><th>Id</th><th>First Name</th><th>Middle Name</th><th>Last Name</th></tr>";
 
class TableRows extends RecursiveIteratorIterator { 
    function __construct($it) { 
        parent::__construct($it, self::LEAVES_ONLY); 
    }
 
    function current() {
        return "<td style='width:150px;border:1px solid black;'>" . parent::current(). "</td>";
    }
 
    function beginChildren() { 
        echo "<tr>"; 
    } 
 
    function endChildren() { 
        echo "</tr>" . "\n";
    } 
} 
 
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "add_query_pdo";
 
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare("SELECT tbl_registration_id, first_name, middle_name, last_name FROM tbl_registration"); 
    $stmt->execute();
 
    // set the resulting array to associative
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 
    foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) { 
        echo $v;
    }
}
catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;
echo "</table>";
?>

This is the output: Result 3

In this tutorial, I used (*) character to Select All columns from the database table. And, this is the codes that I used.

<table border="1" cellspacing="5" cellpadding="5" width="100%">
	<thead>
		<tr>
			<th>No.</th>
			<th>First Name</th>
			<th>Middle Name</th>
			<th>Last Name</th>
			<th>Email</th>
			<th>Contact Number</th>
		</tr>
	</thead>
	<tbody>
	<?php
		require_once('connection.php');
		$result = $conn->prepare("SELECT * FROM tbl_registration ORDER BY tbl_registration_id ASC");
		$result->execute();
		for($i=0; $row = $result->fetch(); $i++){
	?>
		<tr>
			<td><label><?php echo $row['tbl_registration_id']; ?></label></td>
			<td><label><?php echo $row['first_name']; ?></label></td>
			<td><label><?php echo $row['middle_name']; ?></label></td>
			<td><label><?php echo $row['last_name']; ?></label></td>
			<td><label><?php echo $row['email']; ?></label></td>
			<td><label><?php echo $row['contact_number']; ?></label></td>
		</tr>
		<?php } ?>
	</tbody>
</table>

And, this is the result of this tutorial. Result Tutorial

Related Code: Display Data From Database Table In PHP/MySQL Using PDO Query That's all, you can merge this tutorial on my previous work. Enjoy coding. Thank you very much. Share us your thoughts and comments below. Thank you so much for dropping by and reading this tutorial post. For more updates, don’t hesitate and feel free to visit this website more often and please share this with your friends or email me at [email protected]. Practice Coding. Thank you very much.

PHP Select Data In MySQL

Select Data Using MySQLi and PDO

Using MySQLi (Object-Oriented)

Using MySQLi (Procedural)

Using PDO (PHP Data Objects)

Tags

Comments

search in php pdo

Add new comment