The examples on this page assumes that the following class and connection have been defined:
class dcrisExample {
const SETTINGS_FILE = 'settings.ini';
private $conn = null;
//------------------------------------------------------
public function __construct() {
$this->connect();
}
//------------------------------------------------------
private function connect() {
try {
if( $settings = parse_ini_file( dcrisExample::SETTINGS_FILE, TRUE ) ) {
$dsn = $settings['database']['dsn'];
$usr = $settings['database']['usr'];
$pwd = $settings['database']['pwd'];
$options = array();
$options[PDO::ATTR_PERSISTENT] = TRUE;
$options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
$this->conn = new PDO( $dsn, $usr, $pwd, $options );
} else {
throw new exception( 'Error: Unable to open settings file.' );
}
} catch( Exception $e ) {
print( $e->getMessage() );
}
//------------------------------------------------------
// ...
This code creates an instance of the dcrisExample class that
contains a private conn attribute that connects to the
dcris database using the DSN, user, and password information in
the settings.ini file.
SELECTA simple data SELECT consists of a four step process:
The following method selectAll selects and returns all of the
data in the keyword table:
//-------------------------------------------------------------------------
public function selectAll() {
$sql = 'SELECT * FROM keyword ORDER BY k_desc';
$stmt = $this->conn->prepare( $sql );
$stmt->setFetchMode( PDO::FETCH_ASSOC );
$stmt->execute();
return $stmt->fetchAll();
}
The first line of the method defines the SQL statement to be executed against the database:
$sql = 'SELECT * FROM keyword ORDER BY k_desc';
It is a simple SQL statement with no variables.
The next line of the method prepates the SQL statement for execution.
$stmt = $this->conn->prepare( $sql );
prepare is a PDO method and is called from the
PDO object conn. It accepts a SQL statement string
($sql) as a parameter and returns the PDOStatement
object $stmt that will later be executed.
The next two lines set the result set format and then executes the prepared statement:
$stmt->setFetchMode( PDO::FETCH_ASSOC );
$stmt->execute();
The method setFetchMode instructs $stmt to
return its result set as a two-dimensional associative array
(PDO::FETCH_ASSOC) where the rows are numbered and the columns
are identified by the field names of the database table the data is fetched
from. The method execute actually executes the SQL statement
against the database.
Although we strongly recommend the use of associative arrays for the fetch mode, you may see PDOStatement->setFetchMode for other fetch modes.
The last line returns the actual data selected by the SQL statement.
return $stmt->fetchAll();
The method fetchAll returns the data in the mode set by the
setFetchMode method. In this particular example, the
keyword table contains two fields, keyword_id and
k_desc. (Note that the SQL statement does not explicitly refer
to these field names, so the PDOStatement object extracts the
field names from the database metadata. The data returned is:
Array
(
[0] => Array
(
[keyword_id] => 7
[k_desc] => Arms Control and Non-Proliferation Studies
)
[1] => Array
(
[keyword_id] => 13
[k_desc] => Civil-Military Relations
)
...
[18] => Array
(
[keyword_id] => 16
[k_desc] => Weapons Systems
)
}
This data may then be manipulated or printed as appropriate.