Skip to main content

PHP Select Data In MySQL

Screenshot
Body

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

This is the output:

Result 1

Using MySQLi (Procedural)

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

This is the output:

Result 2

Using PDO (PHP Data Objects)

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

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

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