PHP Select Data In MySQL

Submitted by: 
Language: 
Visitors have accessed this post 2777 times.

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 3In 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 TutorialRelated 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.


Comments

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

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.