PHP PDO - Select query, fetch

Php-mysql Course

• The fetch() method

To select data in a MySQL table, use the SELECT query, and the PDO query() method. Then, you can get rows data with the fetch() methods and their FETCH constants.

Select data in a MySQL table

Once you have created and added some data in a MYSQL table, you can use a SELECT query, with the PDO query() method to get those data.
The query() method returns a result set with data returned by MySQL, or FALSE in case of error.
In the examples in this lesson it is used the "sites" table, created in the previous lesson, having these 3 rows:

| id | | name               | | category          | | link                         |
------------------------------------------------------------------------------------
| 1 | | Courses - Tutorials | | education         | | https://coursesweb.net           |
| 2 | | PHP-MySQL Course    | | programming       | | https://coursesweb.net/php-mysql |
| 3 | | English Courses     | | foreign languages | | marplo.net/engleza       |

- The following example selects the rows in which the 'id' is 1 or 3.

<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'tests';
$userdb = 'username';
$passdb = 'password';

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Define and perform the SQL SELECT query
  $sql = "SELECT * FROM `sites` WHERE `id` IN(1, 3)";
  $result = $conn->query($sql);

  // If the SQL query is succesfully performed ($result not false)
  if($result !== false) {
    $cols = $result->columnCount();           // Number of returned columns

    echo 'Number of returned columns: '. $cols. '<br />';

    // Parse the result set
    foreach($result as $row) {
      echo $row['id']. ' - '. $row['name']. ' - '. $row['category']. ' - '. $row['link']. '<br />';
    }
  }

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

- The "columnCount()" method returns the number of columns in the result set. Notice that this method is applied at the instance which performed the query (in this case, the $result variable).
• The result set can also be parsed with the WHILE statement, but in this case you should apply the fetch method:

while($row = $result->fetch()) {
  // ... PHP code
}

- The script above will output this result:

The fetch() method

fetch() returns the each row in the result set, one after another, or FALSE if there no more row. This method contains various constants that determine the mode to return the rows: Array, Object, String, .... This argument is applied with the following syntax:

fetch(PDO::FETCH_MODE)

Here are some of these constants, the most common:

FETCH_ASSOC

- Returns an array indexed by column name as returned in the result set (similar to mysql_fetch_assoc).

<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'tests';
$userdb = 'username';
$passdb = 'password';

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Define and perform the SQL SELECT query
  $sql = "SELECT * FROM `sites`";
  $result = $conn->query($sql);

  // Parse returned data, and displays them
  while($row = $result->fetch(PDO::FETCH_ASSOC)) {
      echo $row['id']. ' - '. $row['name']. ' - '. $row['category']. ' - '. $row['link']. '<br />';
  }

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

- This code will output:

FETCH_NUM

- Returns an array indexed by column number as returned in your result set, starting at column 0 (similar to mysql_fetch_row).

<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'tests';
$userdb = 'username';
$passdb = 'password';

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Define and perform the SQL SELECT query
  $sql = "SELECT * FROM `sites`";
  $result = $conn->query($sql);

  // Gets and displays data of each row
  while($row = $result->fetch(PDO::FETCH_NUM)) {
    echo $row[0]. '-'. $row[1]. '<br />';      // Output data from the first and second column
  }

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

- Result:

FETCH_BOTH

- Returns an array indexed by both column name and 0-indexed column number as returned in your result set.

<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'tests';
$userdb = 'username';
$passdb = 'password';

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Selects the rows in which "id" is 2
  $sql = "SELECT `id`, `name` FROM `sites` WHERE `id`=2";
  $result = $conn->query($sql);

  // Parse the result set
  while($row = $result->fetch(PDO::FETCH_BOTH)) {
    echo $row['id']. '-'. $row['name']. '<br />';      // Display the 'id', and 'name' columns
    echo $row[0]. '-'. $row[1]. '<br />';      // Display the first, and the second column
  }

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

- Result:

FETCH_OBJ

- Returns an anonymous object with property names that correspond to the column names returned in your result set.

<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'tests';
$userdb = 'username';
$passdb = 'password';

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Selects the rows in which "id" lower than 3
  $sql = "SELECT `id`, `name` FROM `sites` WHERE `id`<3";
  $result = $conn->query($sql);

  // Parse data
  while($row = $result->fetch(PDO::FETCH_OBJ)) {
    echo $row->id. '-'. $row->name. '<br />';      // Output data from the columns 'id', and 'name'
  }

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

- Output:

FETCH_CLASS

- It allows the result set to be taken in a class, creating an instance of that class. The field names are used as properties of the class. This constant is applied with the fetchALL() method.

<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'tests';
$userdb = 'username';
$passdb = 'password';

// Define a class
class Sites {
  // Sets properties
  public $id;
  public $category;

  // Method of the class
  function makeString() {
    // Returns a string with the properties value. Uppercase the first character of each word
    return ucwords($this->id. ' - '. $this->category). '<br />';
  }
}

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Selects the "id", and "category" columns
  $sql = "SELECT `id`, `category` FROM `sites`";
  $result = $conn->query($sql);
  $obj = $result->fetchALL(PDO::FETCH_CLASS, 'Sites');      // Apply FETCH_CLASS with Sites class

  // Traverse the returned data, creating $insSites as instance of the class
  foreach($obj as $insSites) {
    echo $insSites->makeString();        // Calls the makeString() method
  }

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

- Notice how the FETCH_CLASS constant is applied to the "fetchALL()" method, with a second argument as a string that represents the name of the class (in this example, "Sites").
- This technique is useful in larger projects, defining in a class the instructions that must be applied to data in the returned columns.
- This code will output:
• An alternative to FETCH_CLASS is the fetchObject() method. It returns an instance of the required class with property names that correspond to the column names, or FALSE on failure
Example:

<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'tests';
$userdb = 'username';
$passdb = 'password';

// Define a class
class Sites {
  // Sets properties
  public $id;
  public $category;

  // Sets a method
  function makeString() {
    // Returns a string with the properties value. Uppercase the first character of each word
    return ucwords($this->id. ' - '. $this->category). '<br />';
  }
}

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Selects the columns "id", and "category"
  $sql = "SELECT `id`, `category` FROM `sites`";
  $result = $conn->query($sql);

  // Parse the object instance (of the Sites class) created with fetchObject()
  while($obj = $result->fetchObject('Sites')) {
    echo $obj->makeString();        // Output the result returned by makeString() method
  }

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

- The name of the columns in the result set are used as properties of the required class (Sites)
- Output:

FETCH_FUNC

- This constant is applied to the fetchALL() method. Returns an 0-indexed Array with the result returned by a function passed in argument. The name of the columns in the result set are used as arguments for the required function.

<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'tests';
$userdb = 'username';
$passdb = 'password';

// Define a function
function test($id, $name) {
  // Returns a string with the parameter values, in uppercase
  return strtoupper($id.'-'.$name);
}

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Define and perform the SQL SELECT query
  $sql = "SELECT `id`, `name` FROM `sites`";
  $result = $conn->query($sql);
  $ar_row = $result->fetchALL(PDO::FETCH_FUNC, 'test');       // Apply FETCH_FUNC with test() function

  var_export($ar_row);        // Output the structure of the returned Array

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

- This technique is useful when you want to apply the same instruction to multiple result sets obtained from SELECT queries, so you can define the instructions only once, in function.
- Output:
• The query() method accepts an optional argument that represents the fetch mode.
Example:

<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'tests';
$userdb = 'username';
$passdb = 'password';

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Select the rows in which "id" is 3
  $sql = "SELECT `id`, `link` FROM `sites` WHERE `id`=3";
  $result = $conn->query($sql, PDO::FETCH_OBJ);        // Apply query() with a fetch-mode

  // Parse the result set
  while($row = $result->fetch()) {
    echo $row->id. '-'. $row->link. '<br />';      // Display the columns "id", and "link"
  }

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

- Output:

If the fetch() method is applied without argument, and the "query()" without a FETCH_ mode, the default mode is PDO::FETCH_BOTH.
- To set another default FETCH_ mode, use the setFetchMode() method.
    Ex.:
            $result = $conn->query($sql);
            $result->setFetchMode (PDO::FETCH_OBJ);
            while($row = $result->fetch()) { // ... }
- More details at official site setFetchMode

In the next tutorial you can learn how to use the prepare(), and execute() methods.

<ul>
 <li>http://coursesweb.net/html/</li>
 <li>http://coursesweb.net/css/</li>
</ul>

.some_class {
  display: list-item;
}

var obj = {
 "courses": ["php", "javascript", "ajax"]
};
var jsonstr = JSON.stringify(obj);
alert(jsonstr);    // {"courses":["php","javascript","ajax"]}

$strhtml = '<body><div id="dv1">CoursesWeb.net</div></body>';
$dochtml = new DOMDocument();
$dochtml->loadHTML($strhtml);
$elm = $dochtml->getElementById("dv1");
echo $elm->nodeValue;    // CoursesWeb.net