It’s Festive Season! Enjoy 30% Off

Use code FESTIVE30 at checkout

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.

Theory is great, but we recommend digging deeper!

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>";
?>