Seize the opportunity to gain new skills and reshape your career!

Choose a free learning path and get valuable insights from first-rate courses

Code has been added to clipboard!

Using MySQL: Select Database Entries

Reading time 2 min
Published Aug 8, 2017
Updated Oct 15, 2019

PHP MySQL SELECT is used to access records from MySQL database.

Selecting Database Entries in MySQL

Example
<?php
  echo "<table>";
  echo "<tr><th>Id</th><th>Name</th><th>Surname</th></tr>";
  class table_rows extends recursive_array_iterator {
    function __construct($it) {
      parent::__construct($it, self::LEAVES_ONLY);
    }  	    
    function current_row() {
      return "<td>" . parent::current(). "</td>";
    }
    function begin_children() {
      echo "<tr>";
    }
    function end_children() {
      echo "</tr>" . "\n";
    }
  } 
  $server = 'server';
  $user = 'user';
  $pass = 'pass';
  $db = 'db';  	
  try {
    $con = new PDO("mysql:host=$server;dbname=$db", $user, $pass);
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    //prepare the statement
    $statement = $con->prepare("SELECT user_id, name, surname FROM users");
    // make the query array associative
    $statement->execute();
    $result = $statement->setFetchMode(PDO::FETCH_ASSOC);
    //iterate through the queried data
    foreach(newtable_rows(newrecursive_array_iterator($stmt->fetchAll())) as $k => $v) {
      echo $v;
      }
  } catch(PDOException $e) {
    //set up an error message
    echo "Error message: " . $e->getMessage();
  }
  $con = null;
  echo "</table>";
?>

The query example above selects specific entries from the database. Alternatively, you can use an asterisk (*) to select every column in the table.

Note: Storing all SQL statements in one line of code ensures they function properly.

Make MySQL Select Database Entries Using MySQLi

In the example below, we select the user_id, name and surname columns in the users table and display it on our web page:

Example
<?php
  $server = 'server'; 
  $user = 'user'; 
  $pass = 'pass'; 
  $db = 'db';
  // establish connection
  $con = new mysqli($server, $user, $pass, $db);
  // see if connection has been established properly
  if ($conn->connect_error) { 
    die("Failed to connect: " . $con->connect_error);
  } 
  $sql = "SELECT user_id, name, surname FROM users";
  $result = $con->query($sql);
  if ($result->num_rows > 0) {
    // display data from the query
    while($row = $result->fetch_assoc()) {
      echo "User Id: " . $row["user_id"]. " - Name: " . $row["name"]. " - Surname: " . $row["surname"]. "<br/>";
    }
  } else {
    echo "0 results";
  }
  $con->close();  
?>

First, we had to write the PHP SQL query selecting the user_id, name and surname columns in the users table. The next code line executed an PHP SQL query and placed the requested data into an array we call $result.

Once that is done, num_rows() was run through our array of results to see if any variables were returned. If any rows are present in the result set, the fetch_assoc() function places all of them in an associative array that can easily be looped. A while() loop is used to go through the array of results. It displays the results from the user_id, name and surname columns.

The example below shows how you can perform a PHP MySQL query using the procedural method in MySQLi:

Example
<?php
  server = 'server'; 
  $user = 'user'; 
  $pass = 'pass'; 
  $db = 'db';
  // establish connection
  $con = mysqli_connect($server, $user, $pass, $db);
  // see if the connection has been established properly
  if (!$con) {
    die("Failed to connect: " . mysqli_connect_error());
  }
  $sql = "SELECT user_id, name, surname FROM users";
  $result = mysqli_query($con, $sql);
  if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
      // display data from the query
      echo "User Id: " . $row["user_id"]. " - Name: " . $row["name"]. " - Surname: " . $row["surname"]. "<br>"; 
      }
  } elseecho "0 results";
  }
  mysqli_close($con);  
?>

The next example shows how to display database entries as a table.

Example
<?php
  $server = 'server';
  $user = 'user';
  $pass = 'pass';
  $db = 'db';
  // establish connection
  $con = new mysqli($server, $user, $pass, $db);
  // see if the connection has been established properly
  if ($con->connect_error) {
    die("Failed to connect: " . $conn->connect_error);
  }
  $sql = "SELECT user_id, name, surname FROM users";
  $result = $con->query($sql);
  if ($result->num_rows > 0) {
    echo "<table><tr><th>User Id</th><th>Name</th></tr>";
    while ($row = $result->fetch_assoc()) {
      // display data from the query
      echo "<tr><td>".$row["user_id"]."</td><td>".$row["name"]." ".$row["surname"]."</td></tr>";
    }
    echo "</table>";
  } else {
    echo "0 results";
  }
  $con->close();  
?>

PDO and Prepared Statements for PHP MySQL Selection

We are going to use prepared statements to make MySQL select database entries.

Select the user_id, name and surname columns in the users table and display it in a table:

Example
<?php
  echo "<table>";
  echo "<tr><th>Id</th><th>Name</th><th>Surname</th></tr>";
  class table_rows extends recursive_array_iterator {
    function __construct($it) {
      parent::__construct($it, self::LEAVES_ONLY);
    }  	    
    function current_row() {
      return "<td>" . parent::current(). "</td>";
    }
    function begin_children() {
      echo "<tr>";
    }
    function end_children() {
      echo "</tr>" . "\n";
    }
  } 
  $server = 'server';
  $user = 'user';
  $pass = 'pass';
  $db = 'db';  	
  try {
    $con = new PDO("mysql:host=$server;dbname=$db", $user, $pass);
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    //prepare the statement
    $statement = $con->prepare("SELECT user_id, name, surname FROM users");
    // make the query array associative
    $statement->execute();
    $result = $statement->setFetchMode(PDO::FETCH_ASSOC);
    //iterate through the queried data
    foreach(newtable_rows(newrecursive_array_iterator($stmt->fetchAll())) as $k => $v) {
      echo $v;
      }
  } catch(PDOException $e) {
    //set up an error message
    echo "Error message: " . $e->getMessage();
  }
  $con = null;
  echo "</table>";
?>