drew.d.lenhart

programming, software, technology, anything on my mind...

PHP Database Class, Bootstrap, and a Discarded Project

2014/11/18

Your probably wondering right away what the discarded project means! Awhile ago I was looking into using an Arduino & or a Raspberry Pi to monitor the temperature of each room in my house using multiple temperature sensors. The idea in my head was to display a schematic of my house on a tablet and mount it by my thermostat. Cool idea! I was already using a Raspberry PI as a web server and wanted the tablet/temperature display to connect to the Ras server, which of course hosts the app. On top of all that, I really wanted to make my own little Application Launcher to display all my own personal apps (such as the temperature house schematic) as well as the ability to do web links. Therefore this idea was born:

 

st100ss

desktopst100

 

 

Needless to say, this project fell through the cracks pretty quickly due to not having much free time. But I did create the App Launcher and created my own database class in the process. I really wanted to use a framework for this project such as PHP Kohanna, Ruby on Rails, or Django. I got really frustrated with how bulky these frameworks were and decided to create my own database class for making really small database driven sites.

This app is built using PHP, HTML, CSS, Boostrap, and MySQL!

Disclaimers: *This project is incomplete! BE WARNED, there is no user logon script included so PLEASE don't put this on your production environment!!

**Im not going to go into the details of the programming, but I will point out a few things here and there. I recommend downloading the code and setting up the application on your development server to learn! This is a great script to learn/pick up PDO (PHP Data Objects).


Create Database

Ive included the .sql database with the downloadable project code if you simply want to import it using PhpMyAdmin. Otherwise create a database called "st100" or whatever you want to call it and a table called "app_info":

dbss2

 

Also create another table called "common_entry". I used this to store the website title and description.

 

commonss

 


Database Class

 

database.class.php will also serve as the connection file. The connection information can be split into another file, but it works for this mini project.

<?php
/*
    Author:  Drew D. Lenhart
    http://www.drewlenhart.com
    Page: database.class.php
    Desc: DB Connection Class
    */
class database
{
    public $pdo;
    private static $instance;

    private function __construct()
    {
    $db_username = "";
    $db_password = "";
    $db_name = "";
    $host = "localhost";

    try{
            $this->pdo = new PDO('mysql:host='. $host .';dbname='.$db_name, $db_username, $db_password);
    }
    catch ( PDOException $exception ){
            echo "Connection error :" . $exception ->getMessage();
    }
    }

    public static function getInstance()
    {
        if (!isset(self::$instance))
        {
            $object = __CLASS__;
            self::$instance = new $object;
        }
        return self::$instance;
    }

    //Grab single entry
    function getDataSingle($query){
        $queryEx = $this->pdo->prepare($query);
        $queryEx->execute();
        return $queryEx->fetch(PDO::FETCH_ASSOC);
    }

    //Grab multiple entries
    function getData($query){
        $queryEx = $this->pdo->prepare($query);
        $queryEx->execute();
        return $queryEx->fetchAll(PDO::FETCH_ASSOC);
    }

    //use update for Delete, or Update queries
    function updateData($query){
    $queryEx = $this->pdo->prepare($query);       
    $queryEx->execute();
    return;
    }

    //insert into app_info (old method, specific to table)
    function insertDataApp($title, $location, $img, $sum){
        $data = array(
            ':Title' => $title,
            ':Location' => $location,
            ':Img' => $img,
            ':Summary' => $sum);
        $query = 'INSERT INTO app_info (Title, Location, Img_loc, Summary) VALUES (:Title,:Location,:Img,:Summary)';
        $queryEx = $this->pdo->prepare($query);
        return $queryEx->execute($data);
    }

    //insert data
    function insertData($query, $data){
        $queryEx = $this->pdo->prepare($query);
        return $queryEx->execute($data);
    }

    //close connection
    function closeConn(){
    $this->pdo = null;       
    }
}

?>

Replace the empty variables in the Construct method with your database information. Each method is pretty self explanatory.


Retrieving Data

 

Now that the database.class.php file is setup, we can easily run queries with a little bit of code! Check out the code for the index.php page. On this page, use the getData() method to pull all the entries from the app_info table.


<?php
/*
    Author:  Drew D. Lenhart
    http://www.drewlenhart.com
    Page: index.php
    Desc: Main view screen. Displays installed applications.
    */
require_once('includes/database.class.php');
$db = database::getInstance();
include("includes/functions.php");
include("includes/header.php");
include("includes/navbar.php"); ?>

<article>
<h1>Applications</h1>
<br />
<div class="container-fluid">
    <div class="row" style="text-align: center">
<?php
$sql = 'SELECT * FROM app_info';
$rows = $db->getData($sql);
foreach ($rows as $row) :
    $entryID = $row['ID'];
    $title = $row['Title'];
    $loc = $row['Location'];
    $img_loc = $row['Img_loc'];
    echo "<div class='col-xs-6 col-sm-3' style='margin-bottom: 4%'><a href='$loc'><img src='$img_loc' /></a><br /><br /><a type='button' class='btn btn-default btn-xs' href='appinfo.php?id=" . $entryID . "'><span class='glyphicon glyphicon-edit'></span> " . $title . "</a></div>";      
endforeach;
?>
    </div>
</div>
</article>
<?php $db->closeConn() ?>
<?php include("includes/footer.php"); ?>

 


Conclusion

As you can see in the above code, the database class allows a lot of reusability. Feel free to download the code for this project and use as a starting point for your own!

Download Code - Includes .sql file for tables/mock data.

Github