Skip to main content

PHP Select Data In MySQL


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)

  1. <?php
  2. $servername = "localhost";
  3. $username = "root";
  4. $password = "";
  5. $dbname = "add_query_pdo";
  6.  
  7. // Create connection
  8. $conn = new mysqli($servername, $username, $password, $dbname);
  9. // Check connection
  10. if ($conn->connect_error) {
  11. die("Connection failed: " . $conn->connect_error);
  12. }
  13.  
  14. $sql = "SELECT tbl_registration_id, first_name, middle_name, last_name FROM tbl_registration";
  15. $result1 = $conn->query($sql);
  16.  
  17. if ($result1->num_rows > 0) {
  18. // output data of each row
  19. while($row1 = $result1->fetch_assoc()) {
  20. echo "id: " . $row1["tbl_registration_id"]. " - Name: " . $row1["first_name"]. " " . $row1["middle_name"]. " " . $row1["last_name"]. "<br>";
  21. }
  22. } else {
  23. echo "0 results";
  24. }
  25. $conn->close();
  26. ?>

This is the output:

Result 1

Using MySQLi (Procedural)

  1. <?php
  2. $servername = "localhost";
  3. $username = "root";
  4. $password = "";
  5. $dbname = "add_query_pdo";
  6.  
  7. // Create connection
  8. $conn = mysqli_connect($servername, $username, $password, $dbname);
  9. // Check connection
  10. if (!$conn) {
  11. die("Connection failed: " . mysqli_connect_error());
  12. }
  13.  
  14. $sql = "SELECT tbl_registration_id, first_name, middle_name, last_name FROM tbl_registration";
  15. $result = mysqli_query($conn, $sql);
  16.  
  17. if (mysqli_num_rows($result) > 0) {
  18. // output data of each row
  19. while($row = mysqli_fetch_assoc($result)) {
  20. echo "id: " . $row["tbl_registration_id"]. " - Name: " . $row["first_name"]. " " . $row["middle_name"]. " " . $row["last_name"]. "<br>";
  21. }
  22. } else {
  23. echo "0 results";
  24. }
  25.  
  26. mysqli_close($conn);
  27. ?>

This is the output:

Result 2

Using PDO (PHP Data Objects)

  1. <?php
  2. echo "<table style='border: solid 1px black;'>";
  3. echo "<tr><th>Id</th><th>First Name</th><th>Middle Name</th><th>Last Name</th></tr>";
  4.  
  5. class TableRows extends RecursiveIteratorIterator {
  6. function __construct($it) {
  7. parent::__construct($it, self::LEAVES_ONLY);
  8. }
  9.  
  10. function current() {
  11. return "<td style='width:150px;border:1px solid black;'>" . parent::current(). "</td>";
  12. }
  13.  
  14. function beginChildren() {
  15. echo "<tr>";
  16. }
  17.  
  18. function endChildren() {
  19. echo "</tr>" . "\n";
  20. }
  21. }
  22.  
  23. $servername = "localhost";
  24. $username = "root";
  25. $password = "";
  26. $dbname = "add_query_pdo";
  27.  
  28. try {
  29. $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  30. $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  31. $stmt = $conn->prepare("SELECT tbl_registration_id, first_name, middle_name, last_name FROM tbl_registration");
  32. $stmt->execute();
  33.  
  34. // set the resulting array to associative
  35. $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
  36. foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
  37. echo $v;
  38. }
  39. }
  40. catch(PDOException $e) {
  41. echo "Error: " . $e->getMessage();
  42. }
  43. $conn = null;
  44. echo "</table>";
  45. ?>

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.

  1. <table border="1" cellspacing="5" cellpadding="5" width="100%">
  2. <thead>
  3. <tr>
  4. <th>No.</th>
  5. <th>First Name</th>
  6. <th>Middle Name</th>
  7. <th>Last Name</th>
  8. <th>Email</th>
  9. <th>Contact Number</th>
  10. </tr>
  11. </thead>
  12. <tbody>
  13. <?php
  14. require_once('connection.php');
  15. $result = $conn->prepare("SELECT * FROM tbl_registration ORDER BY tbl_registration_id ASC");
  16. $result->execute();
  17. for($i=0; $row = $result->fetch(); $i++){
  18. ?>
  19. <tr>
  20. <td><label><?php echo $row['tbl_registration_id']; ?></label></td>
  21. <td><label><?php echo $row['first_name']; ?></label></td>
  22. <td><label><?php echo $row['middle_name']; ?></label></td>
  23. <td><label><?php echo $row['last_name']; ?></label></td>
  24. <td><label><?php echo $row['email']; ?></label></td>
  25. <td><label><?php echo $row['contact_number']; ?></label></td>
  26. </tr>
  27. <?php } ?>
  28. </tbody>
  29. </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.

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Submitted byyogendra kumar (not verified)on Fri, 05/13/2016 - 18:36

respected sir,
can you plz provide the search in php pdo

Add new comment

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.