PHP MySQL Select Query

SELECT statement is used to select the records from MySQL database tables.

Selecting Data From Database Tables

So far you have learnt how to create database and table as well as inserting data. Now it's time to retrieve data what have inserted in the preceding tutorial.

The basic syntax of the SELECT query can be given with:

SELECT column_name(s) FROM table_name

Let's make a SQL query using the SELECT statement, after that we will execute this SQL query through passing it to the mysqli_query() function to retrieve the table data.

Consider the following "persons" table inside the "demo" database:

+-----------+------------+-----------+----------------------+
| person_id | first_name | last_name | email_address        |
+-----------+------------+-----------+----------------------+
|         1 | Peter      | Parker    | peterparker@mail.com |
|         2 | John       | Rambo     | johnrambo@mail.com   |
|         3 | Clark      | Kent      | clarkkent@mail.com   |
|         4 | John       | Carter    | johncarter@mail.com  |
|         5 | Harry      | Potter    | harrypotter@mail.com |
+-----------+------------+-----------+----------------------+

The PHP code in the following example selects all the data stored in the "persons" table (using the asterisk character (*) in place of column name selects all the data in the table).

Example

Download
  • <?php
  • /* Attempt MySQL server connection. Assuming you are running MySQL
  • server with default setting (user 'root' with no password) */
  • $link = mysqli_connect("localhost", "root", "", "demo");
  •  
  • // Check connection
  • if($link === false){
  •     die("ERROR: Could not connect. " . mysqli_connect_error());
  • }
  •  
  • // Attempt select query execution
  • $sql = "SELECT * FROM persons";
  • if($result = mysqli_query($link, $sql)){
  •     if(mysqli_num_rows($result) > 0){
  •         echo "<table>";
  •             echo "<tr>";
  •                 echo "<th>person_id</th>";
  •                 echo "<th>first_name</th>";
  •                 echo "<th>last_name</th>";
  •                 echo "<th>email_address</th>";
  •             echo "</tr>";
  •         while($row = mysqli_fetch_array($result)){
  •             echo "<tr>";
  •                 echo "<td>" . $row['person_id'] . "</td>";
  •                 echo "<td>" . $row['first_name'] . "</td>";
  •                 echo "<td>" . $row['last_name'] . "</td>";
  •                 echo "<td>" . $row['email_address'] . "</td>";
  •             echo "</tr>";
  •         }
  •         echo "</table>";
  •         // Close result set
  •         mysqli_free_result($result);
  •     } else{
  •         echo "No records matching your query were found.";
  •     }
  • } else{
  •     echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
  • }
  •  
  • // Close connection
  • mysqli_close($link);
  • ?>

In the example above, the data returned by the mysqli_query() function is stored in the $result variable. Each time mysqli_fetch_array() is invoked, it returns the next record from the result set as an array. The while loop is used to loops through all the records in the result set. Finally the value of individual fields can be accessed from the record either through passing the field index or the field name to the $row variable like $row['person_id'] or $row[0], $row['first_name'] or $row[1], $row['last_name'] or $row[2], and $row['email_address'] or $row[3].

If you want to use the for loop you can obtain the loop counter value or the number of rows returned by the query by passing the $result variable to the mysqli_num_rows() function. This loop counter value determines how many times the loop should run.

 
Close

Your Feedback:

 

We would love to hear from you! Please say something.