.
PHP SQLite JSON Encode and PDO BindParam
This tutorial demonstrates the use of PDO BindParam and JSON Encode.
JSON Encode is used to convert the SQL query results into JSON data format.
PDO BindParam is used when the SQL statements require parameter values.
|
0) Starting Up
Continue from previous tutorial, http://php-steps.blogspot.my/2016/02/php-and-sqlite-form-validate-submit-and.html
1) Create PHP script containing JSON Encode
We are going to create a script that selects all buddy records and produces them in the form of JSON data format.
PDOStatement::fetchAll returns an array containing all of the result set rows.
The parameter PDO::FETCH_ASSOC tells PDO to return the result as an associative array.
Associative arrays are arrays that use named keys that you assign to them.
|
File: getbuddies.php
<?php
function getDB() {
$dbConnection = new PDO("sqlite:buddies.sdb");
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $dbConnection;
}
try {
$DB=getDB();
// prepare SQL command and execute
$query = "SELECT id, name, email, photo FROM tblbuddies ORDER BY id desc";
$stmt = $DB->prepare( $query );
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$output = array('buddies'=>$result);
echo json_encode($output);
// close the database connection
$db = NULL;
}
catch(PDOException $e){
echo 'Exception : '.$e->getMessage();
}
?>
|
OUTCOME.
The output is in the form of JSON data format.
To get a better view, use POSTMAN. It is offered as a free Chrome Apps (get it here for your Chrome Web Browser)
|
OUTCOME (in POSTMAN Chrome Apps)
2) Using PDO BindParam to select specific id
We want to provide options to the user; either selecting all records or a specific record.
In order to select a specific record, we need to create a parameter and bind the parameter to the input value via GET method.
|
<?php
function getDB() {
$dbConnection = new PDO("sqlite:buddies.sdb");
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $dbConnection;
}
try {
$DB=getDB();
// prepare SQL command and execute
$query = "SELECT id, name, email, photo FROM tblbuddies ";
if (isset($_GET['buddyid'])){
$buddyid=$_GET['buddyid'];
$query .=" WHERE id LIKE :id
ORDER BY id desc";
$stmt = $DB->prepare( $query );
$stmt->bindParam(':id', $buddyid, PDO::PARAM_STR);
}else{
$query .=" ORDER BY id desc";
$stmt = $DB->prepare( $query );
}
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$output = array('buddies'=>$result);
echo json_encode($output);
// close the database connection
$db = NULL;
}
catch(PDOException $e){
echo 'Exception : '.$e->getMessage();
}
?>
|
OUTCOME.
FURTHER READING
.
No comments:
Post a Comment