ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

12-MySQL Select Data

In Selecting one or more records in MySQL database, the SELECT statement is being used.

Syntax:

SELECT * from tableName;

or

SELECT column1,column2  from tableName;

 

The asterisks(*) symbol in our first example syntax represent all column in the database table.

In the second example, we specify the column name of a table.

 

Example using MySQLi (Object-Oriented):

  1. <?php
  2. $server = "localhost";
  3. $username = "root";
  4. $password = "";
  5. $database = "mysqltutorial";
  6.  
  7. // Create connection
  8. $conn = new mysqli($server, $username, $password, $database);
  9. // Check connection
  10. if ($conn->connect_error) {
  11. die("Connection failed: " . $conn->connect_error);
  12. }
  13.  
  14. // sql to Insert record table
  15. $sql = "SELECT `LNAME`, `FNAME`, `ADDRESS` from  `tblpeople`";
  16. $result = $conn->query($sql);
  17. echo "<TABLE border='1'>
  18.         <TR>
  19.             <TH>LAST NAME</TH><TH>FIRST NAME</TH><TH>ADDRESS</TH>
  20.         </TR>";
  21. if ($result->num_rows > 0) {
  22.    
  23.     // output data of each row
  24.     while($row = $result->fetch_assoc()) {
  25.         echo "<TR><TD>" . $row["LNAME"]. "</TD><TD>" . $row["FNAME"]. "</TD><TD>" . $row["ADDRESS"]. "</TD></TR>";
  26.     }
  27. } else {
  28.     echo "0 results";
  29. }
  30.  
  31. echo "</TABLE>";
  32. $conn->close();
  33. ?>

Code explanation:

We just simply Select the Last name, First Name and Address of a person from our table, then we execute the query and store the results to the “$result” variable.

Next, using the num_rows function check if the result is greater than zero. And if the result is greater than zero, the fetch_assoc() function will store the result into an associative array so that we will be able to loop through the records.

The While Loop Loops through the result set and display the result inside the table data because we use table to display the result.

 

Example using MySQLi(Procedural):

  1. <?php
  2. $server = "localhost";
  3. $username = "root";
  4. $password = "";
  5. $database = "mysqltutorial";
  6.  
  7. // Create connection
  8. $conn = mysqli_connect($server, $username, $password, $database);
  9. // Check connection
  10. if (!$conn) {
  11.     die("Connection failed: " . mysqli_connect_error());
  12. }
  13.  
  14. // sql to Insert record table
  15. $sql = "SELECT `LNAME`, `FNAME`, `ADDRESS` from  `tblpeople`";
  16. $results = mysqli_query($conn , $sql);
  17. echo "<TABLE border='1'>
  18.         <TR>
  19.             <TH>LAST NAME</TH><TH>FIRST NAME</TH><TH>ADDRESS</TH>
  20.         </TR>";
  21. if (mysqli_num_rows($results) > 0) {
  22.    
  23.     // output data of each row
  24.     while($row = mysqli_fetch_assoc($results)) {
  25.         echo "<TR><TD>" . $row["LNAME"]. "</TD><TD>" . $row["FNAME"]. "</TD><TD>" . $row["ADDRESS"]. "</TD></TR>";
  26.     }
  27. } else {
  28.     echo "0 results";
  29. }
  30.  
  31. echo "</TABLE>";
  32. mysqli_close($conn);
  33. ?>

Example using PDO:

  1. <?php
  2.  
  3. $server = "localhost";
  4. $username = "root";
  5. $password = "";
  6. $database = "mysqltutorial";
  7.  
  8. try {
  9.     $conn = new PDO("mysql:host=$server;dbname=$database", $username, $password);
  10.     // set the PDO error mode to exception
  11.     $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  12.  
  13.  
  14.     // sql to Select record from table
  15.     $sql = "SELECT `LNAME`, `FNAME`, `ADDRESS` from  `tblpeople`";
  16.     $query = $conn->prepare( $sql );
  17.     $query->execute();
  18.     $results = $query->fetchAll( PDO::FETCH_ASSOC );
  19.    
  20. ?>
  21.  <table border='1'>
  22.    <tr>
  23.    
  24.      <th>LAST NAME</th>
  25.      <th>FIRST NAME</th>
  26.      <th>ADDRESS</th>
  27.  
  28.    </tr>
  29.    <?php
  30.    foreach( $results as $row ){
  31.         echo "<TR><TD>" . $row["LNAME"]. "</TD><TD>" . $row["FNAME"]. "</TD><TD>" . $row["ADDRESS"]. "</TD></TR>";
  32.    }
  33. }
  34. catch(PDOException $e) {
  35.     echo "Error: " . $e->getMessage();
  36. }
  37. $conn = null;
  38. echo "</table>";
  39. ?>

The Output of Code above looks like as shown below.

select record

 

Facebook Comments
  • Login
    Login
  • select record
    select record

Check Your Domain Ranking

Leave a Reply

Your email address will not be published. Required fields are marked *

ITSourceCode.com © 2016 Frontier Theme

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!