The first thing we'll need after the dashboard setup would be the form files to send data to the controllers, process it and save it in database but beore that let's create a database modal to hold all the database queries we'll need in our application development process. The queries we'll need in the database modal would be as follows.
INSERT QUERY
SELECT QUERY
COUNT QUERY
UPDATE QUERY
DELETE QUERY
We can prepare these modals but to execute these queries we'll need the database connection. So, let's add the database connection query in this file rather than keeping it somewhere else and calling it all the time.
Furthermore, we can define the static informations required to be added here in another file so that we don't need to come back and make ammendments here in case the name, username and password for database and host details needed to be changed. This way, database modal file Database.php will be completely reusable in other projects without even a single line of code needing a change. Let's hold the informations required here in a global variable in cofiguration file we created earlier.
$GLOBALS['configs'] = [ 'database' => [ 'host' => 'localhost', 'username' => 'root', 'password' => '', 'name' => 'pdo_project', ] ]
Now, we added those informations in a separate file, we'll need a controller to access it so we'll create a file named Config.php.
<?php
class Config
{
public static function get($getString){
if(empty($getString)) return false;
$getValue = explode('/',$getString);
$globals = $GLOBALS['configs'];
foreach($getValue as $glo){
if(isset($globals[$glo])){
$globals = $globals[$glo];
}
if(!is_array($globals)){
return $globals;
}
}
return false;
}
}
<?php
class Database{
private $_connect = null;
private function openConnection()
{
}
public function insert()
{
}
public function select()
{
}
public function count()
{
}
public function update()
{
}
public function delete()
{
}
The first thing we'll need to operate to the database is the database connection. So, it'll be the first method here to be declared. We'll add database connection queries there. This method will return the database object that we'll hold in some variables. So, we'll declare a private variable on top that won't be accesible from other pages.
We need to instantiate the class and call the specific function / method name to get the objects retrieved from that method as shown below.
$variableName = new Database(); $variablename->openConnection();
But, getting connection only won't be enough, we need to call another method either to insert, delete update or do anything related to database. It'd be tedious to call these two each time. A better option would be to create a magic method that'll instantiate the function openConnection() when the class is instantiated. That'll be much easier and cleaner too. So, let's add that magic method in the class.
public function __construct(){
$this->openConnection();
}
In some situations, we might need to use the database multiple times. Hence, instantiating the database class that's already instantiated. Opening connection multiple times doesn't affect much is smaller applications as it's all about sending to or retrieving data from database but in larger applications it will slow down the entire process. To prevent that, we'll create another method that checks if the database is already instantiated or not. If the database is not instantiated, it'll be instantiated but in another case, it'll use the same instance that was created earlier. For that we'll create another private static variable named $_instance with null value on top.
public static function instantiate(){
if(!isset(self::$_instance)){
return self::$_instance = new Database();
}else{
return self::$_instance;
}
}
Now, the code looks good and the best part is we can instantiate the database easily with a simple function Database::instantiate();
let's add the query to connect to database in openConnection() then.
private function openConnection(){
try{
$this->_connect = new PDO('mysql:host='.Config::get('database/host').';dbname='.Config::get('database/name'),Config::get('database/username'),Config::get('database/password'));
$this->_connect->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
die($e->getMessage());
}
}
PDO always throws multiple lines of exception in case of errors that we can catch and display to the users in a proper readable way, so, we're wrapping the connection query using try-catch block. The query in the try block will try to get executed when the method is called and if there comes any error that will be caught and stored in a variable $e and displays only the useful and readable information via inbuilt getMessage() function.
In the connection query, we passed the required database informations from Config.php we created recently so that this Database Modal becomes reusable where we don't need to make ammendments when using it on another project.
We know the SQL query that needs to executed in order to insert any data to the specified database tables. But, instead of declaring specific tablename, columnnames and values, we'll place variables in the modal where the actual data will be sent from some other methods in the latter part.
public function insert($table,$data=array()){
if(empty($table) || empty($data)) return false;
$fields = implode(',',array_keys($data));
$sql = "INSERT INTO " . $table ." (" .$fields .") VALUES (?";
for ($i=1; $i < count($data); $i++) {
$sql.= ',?';
}
$sql .= ')';
$stmt = $this->_connect->prepare($sql);
try{
$stmt->execute(array_values($data));
return $this->_connect->lastInsertId();
}catch(PDOException $e){
die($e->getMessage());
}
}
The first line rejects the operation call if the tablename or data array is empty. If there's values in both variables then the key values from the array will be imploded using , symbol and stored in a new variable and add the values retrieved so far in the variable that holds the SQL query.
In PDO, we prepare the SQL query to optimize application's performance and prevent SQL injection and then execute it to retrieve result and it requires ? symbol instead of values while preparing. These values will be replaced by original values during execution of the query.
PDO Query
INSERT INTO TABLENAME (columnName,columnName,columnName,columnName,) VALUES (?,?,?,?);
That's why, we'll count the data and generate the ,? equal to the data count. Since, we need one without , we added it to the base query earlier and started counting the array from 1 while we know the array count starts from 0.
Finally, we can close the bracket opened earlier to group data values and call for the inbuilt prepare function that will prepare the SQL query and return query string to be executed.
Then, we can call the inbuilt execute function to the query string returned by prepare() where we'll send the values from the array. We'll use the try-catch block to execute the query and store the exception as we did earlier. SQL insert query won't return anything on it's own, we can return true or use inbuilt lastInsertId() function to return the auto incremented id of the data row being inserted. We'll need it in the latter stages as well.
public function update($table,$data=array(),$criteria="",$value=array()){
if(empty($table) || empty($data) || empty($criteria) || empty($value)) return false;
$fields = implode('= ?,',array_keys($data));
$sql = "UPDATE " . $table ." SET " . $fields;
$sql .= " = ? WHERE " .$criteria;
$fieldvalues = array_merge(array_values($data),$value);
$stmt = $this->_connect->prepare($sql);
try{
$stmt->execute($fieldvalues);
return true;
}catch(PDOException $e){
die($e->getMessage());
}
return false;
}
Quite similar to insert method but here we'll need criteria to update the data row behind the WHERE keyword. So, we'll declare two more parameters criteria and check for their values in array before running the codes. Finally, we'll merge the criteria array value along with other fields' array values and prepare the required query string.
public function delete($table,$criteria,$value=array()){
if(empty($table) || empty($criteria)) return false;
$sql = "DELETE FROM ". $table . " WHERE " . $criteria;
$stmt = $this->_connect->prepare($sql);
try{
$stmt->execute($value);
return true;
}catch(PDOException $e){
die($e->getMessage());
}
return false;
}
Similar to the update method but here only the tablename and the criteria and it's value in array to define after WHERE keyword in the SQL query needs to be defined.
public function select($table,$column="*",$criteria="",$value=array(),$clause=""){
if (empty($table)) return false;
$sql = "SELECT " . $column . " FROM " . $table;
if (!empty($criteria)){
$sql .= " WHERE " . $criteria;
}
if (!empty($clause)){
$sql .= " " . $clause;
}
$stmt = $this->_connect->prepare($sql);
try{
if($stmt->execute($value)){
return $stmt->fetchAll(PDO::FETCH_CLASS);
}
return false;
}catch(PDOException $e){
die($e->getMessage());
}
}
SQL SELECT query is the most complex one as it can be used in a number of ways depending on the situation and the data we need to retrieve. To keep it generalized we'll declare the tablename, columnname, criteria and it's value in array along with other possible clauses.
The only condition where we can't use this method is the time when table name is not defined.
If only the table name is defined, we'll display all data from the table. So, we defined the default column value as *.
If the criteria is defined then we'll concatenate WHERE key to the SQL query with its value.
If the clause such as LIMIT or JOIN is also defined then we'll concatenate it too with the query and prepare it for execution.
This query will return boolean value. To retrieve all the resultset from the query we need to use fetchAll and that'll return each row as an array indexed by colum name and column position.
We can use either FETCH_ASSOC or FETCH_CLASS to return the array indexed by the column name.
public function count($table="",$criteria="",$value=array()){
if (empty($table)) throw new Exception("Error Processing Request");
$sql = "SELECT count(*) FROM " .$table;
if(!empty($criteria) && !empty($value)){
$sql .= " WHERE " . $criteria;
}
$stmt = $this->_connect->prepare($sql);
try{
if($stmt->execute($value)){
$result = $stmt->fetchAll(PDO::FETCH_COLUMN);
return $result[0];
}
return false;
}catch(PDOException $e){
die($e->getMessage());
}
}
Count method is also quite similar to the select query. The only difference is that we don't need additional clauses except WHERE in this SQL query.
FETCH_COLUMN is useful while returning single value using the query.
Leave a comment