Skip to content

Instantly share code, notes, and snippets.

@edoves
Last active September 3, 2024 11:46
Show Gist options
  • Save edoves/3410ade2ae45dd48cf10057ad21fce11 to your computer and use it in GitHub Desktop.
Save edoves/3410ade2ae45dd48cf10057ad21fce11 to your computer and use it in GitHub Desktop.
PDO Class Object

Signup and SignIn and Signout

class Database
{
    private $host = "localhost";
    private $db_name = "mydb";
    private $username = "root";
    private $password = "";
    public $conn;

    public function dbConnection()
	{

	    $this->conn = null;
        try
		{
            $this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name, $this->username, $this->password);
			$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
		catch(PDOException $exception)
		{
            echo "Connection error: " . $exception->getMessage();
        }

        return $this->conn;
    }
}

class USER
{	

	private $conn;
	
	public function __construct()
	{
		$database = new Database();
		$db = $database->dbConnection();
		$this->conn = $db;
    }
	
	public function runQuery($sql)
	{
		$stmt = $this->conn->prepare($sql);
		return $stmt;
	}
	
	public function register($uname,$umail,$upass)
	{
		try
		{
			$new_password = password_hash($upass, PASSWORD_DEFAULT);
			
			$stmt = $this->conn->prepare("INSERT INTO users(user_name,user_email,user_pass) 
		                                               VALUES(:uname, :umail, :upass)");
												  
			$stmt->bindparam(":uname", $uname);
			$stmt->bindparam(":umail", $umail);
			$stmt->bindparam(":upass", $new_password);										  
				
			$stmt->execute();	
			
			return $stmt;	
		}
		catch(PDOException $e)
		{
			echo $e->getMessage();
		}				
	}
	
	
	public function doLogin($uname,$umail,$upass)
	{
		try
		{
			$stmt = $this->conn->prepare("SELECT user_id, user_name, user_email, user_pass FROM users WHERE user_name=:uname OR user_email=:umail ");
			$stmt->execute(array(':uname'=>$uname, ':umail'=>$umail));
			$userRow=$stmt->fetch(PDO::FETCH_ASSOC);
			if($stmt->rowCount() == 1)
			{
				if(password_verify($upass, $userRow['user_pass']))
				{
					$_SESSION['user_session'] = $userRow['user_id'];
					return true;
				}
				else
				{
					return false;
				}
			}
		}
		catch(PDOException $e)
		{
			echo $e->getMessage();
		}
	}
	
	public function is_loggedin()
	{
		if(isset($_SESSION['user_session']))
		{
			return true;
		}
	}
	
	public function redirect($url)
	{
		header("Location: $url");
	}
	
	public function doLogout()
	{
		session_destroy();
		unset($_SESSION['user_session']);
		return true;
	}
}

function add_project($title, $category) {
    global $db;
    $sql = "INSERT INTO projects(title, category) VALUES(:title, :category)";
    try {       
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':category', $category);
        $stmt->execute();
    } catch (Exception $e) {
        echo "Error!: " . $e->getMessage() . "</br>";
        return false;
    }
    return true;
}

Database Class

class Database {
    private $DB_HOST = "localhost";
    private $DB_USER = "root";
    private $DB_PASS = "";
    private $DB_NAME = "expenses";
    private $db;

    public function db_connection() {  
        $dns = "mysql:host=".$this->DB_HOST.";dbname=".$this->DB_NAME;     

        $options = array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
            PDO::ATTR_EMULATE_PREPARES => false
        );
        try {
           
            $this->db = new PDO($dns,$this->DB_USER,$this->DB_PASS, $options);
            // $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            // $this->db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
            // $this->db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        } catch (PDOException  $e) {
            echo "Connection error: " . $e->getMessage();
        }    
        return $this->db;  
    }
}

// AutoLoad
function my_autoload($class) {
    if(preg_match('/\A\w+\Z/', $class)) {
      include('classes/' . $class . '-class.php');
    } 
  }
  spl_autoload_register('my_autoload');

// Treehouse FUnctions

function get_project_list() {
    include 'connection.php';
    
    try {
        return $db->query('SELECT project_id, title, category FROM projects');
    } catch (Exception $e) {
        echo "Error!: " . $e->getMessage() . "<br />";
        return array();
    }
}

function get_task_list($filter = null) {
    include 'connection.php';
    
    $sql = 'SELECT tasks.*, projects.title as project FROM tasks'
        . ' JOIN projects ON tasks.project_id = projects.project_id';
    
    $where = '';
    if (is_array($filter)) {
        switch ($filter[0]) {
            case 'project':
                $where = ' WHERE projects.project_id = ?';
                break;
            case 'category':
                $where = ' WHERE category = ?';
                break;
            case 'date':
                $where = ' WHERE date >= ? AND date <= ?';
                break;
        }
    }
    
    $orderBy = ' ORDER BY date DESC';
    if ($filter) {
        $orderBy = ' ORDER BY projects.title ASC, date DESC';
    }
    
    try {
        $results = $db->prepare($sql . $where . $orderBy);
        if (is_array($filter)) {
            $results->bindValue(1, $filter[1]);
            if ($filter[0] == 'date') {
                $results->bindValue(2, $filter[2],PDO::PARAM_STR);
            }
        }
        $results->execute();
    } catch (Exception $e) {
        echo "Error!: " . $e->getMessage() . "<br />";
        return array();
    }
    return $results->fetchAll(PDO::FETCH_ASSOC);
}

function add_project($title, $category, $project_id = null){
    include 'connection.php';
    
    if ($project_id) {
        $sql = 'UPDATE projects SET title = ?, category = ? WHERE project_id = ?';
    } else {
        $sql = 'INSERT INTO projects(title, category) VALUES(?, ?)';
    }
    
    try {
        $results = $db->prepare($sql);
        $results->bindValue(1, $title, PDO::PARAM_STR);
        $results->bindValue(2, $category, PDO::PARAM_STR);
        if ($project_id) {
            $results->bindValue(3, $project_id, PDO::PARAM_INT);
        }
        $results->execute();
    } catch (Exception $e) {
        echo "Error!: " . $e->getMessage() . "<br />";
        return false;
    }
    return true;
}

function get_project($project_id){
    include 'connection.php';
    
    $sql = 'SELECT * FROM projects WHERE project_id = ?';
    
    try {
        $results = $db->prepare($sql);
        $results->bindValue(1, $project_id, PDO::PARAM_INT);
        $results->execute();
    } catch (Exception $e) {
        echo "Error!: " . $e->getMessage() . "<br />";
        return false;
    }
    return $results->fetch();
}

function get_task($task_id){
    include 'connection.php';
    
    $sql = 'SELECT task_id, title, date, time, project_id FROM tasks WHERE task_id = ?';
    
    try {
        $results = $db->prepare($sql);
        $results->bindValue(1, $task_id, PDO::PARAM_INT);
        $results->execute();
    } catch (Exception $e) {
        echo "Error!: " . $e->getMessage() . "<br />";
        return false;
    }
    return $results->fetch();
}
function delete_task($task_id){
    include 'connection.php';
    
    $sql = 'DELETE FROM tasks WHERE task_id = ?';
    
    try {
        $results = $db->prepare($sql);
        $results->bindValue(1, $task_id, PDO::PARAM_INT);
        $results->execute();
    } catch (Exception $e) {
        echo "Error!: " . $e->getMessage() . "<br />";
        return false;
    }
    return true;
}
function delete_project($project_id){
    include 'connection.php';
    
    $sql = 'DELETE FROM projects WHERE project_id = ?'
        . ' AND project_id NOT IN (SELECT project_id FROM tasks)';
    
    try {
        $results = $db->prepare($sql);
        $results->bindValue(1, $project_id, PDO::PARAM_INT);
        $results->execute();
    } catch (Exception $e) {
        echo "Error!: " . $e->getMessage() . "<br />";
        return false;
    }
    if ($results->rowCount() > 0 ) {
        return true;
    } else {
        return false;
    }
}
function add_task($project_id, $title, $date, $time, $task_id=null){
    include 'connection.php';
    
    if ($task_id) {
        $sql = 'UPDATE tasks SET project_id = ?, title = ?, date = ?, time = ? WHERE task_id = ?';
    } else {
        $sql = 'INSERT INTO tasks(project_id, title, date, time) VALUES(?, ?, ?, ?)';
    }
    
    try {
        $results = $db->prepare($sql);
        $results->bindValue(1, $project_id, PDO::PARAM_INT);
        $results->bindValue(2, $title, PDO::PARAM_STR);
        $results->bindValue(3, $date, PDO::PARAM_STR);
        $results->bindValue(4, $time, PDO::PARAM_INT);
        if ($task_id) {
            $results->bindValue(5, $task_id, PDO::PARAM_INT);
        }
        
        $results->execute();
    } catch (Exception $e) {
        echo "Error!: " . $e->getMessage() . "<br />";
        return false;
    }
    return true;
}

PDO setup

// Note From Tree House
/**
A prepared statement can be thought of as a kind of template for a SQL statement that can be customized using variable parameters.
Prepared statements offer two major benefits. First, the query only needs to be parsed or prepared once. 
But can be executed multiple times with the same or different parameters.
This means that a prepared statement has fewer resources and thus runs faster.
Second, the one we're really concerned about here. A prepared statement properly escapes the variables so that no SQL injection will occur.
*/
//////////////////////////////////////////////////////////////////////////
// Brad Traversy
$host = 'localhost';
$user = 'root';
$password = '';
$dbname = 'pdoposts';

// set DSN
$dsn = 'mysql:host=' . $host . ';dbname=' . $dbname;

// Create a PDO instance
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); // to male the sql code LIMIT work
// PDO Query
$stmt = $pdo->query('SELECT * FROM posts');

// while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
//     echo $row['title'] . "<br>";
// }

// while($row = $stmt->fetch()) {
//     echo $row->title . "<br>";
// }

// UNSAFE
// $sql = "SELECT * FROM posts WHERE author = '$author'";

// FETCH Multiple posts

// sample user unout
$author = 'Brad';
  $is_published = true;
  $id = 1;
  $limit = 1;
  // with positional parameter the order is very sensitive it should be the same order
  // and when you execute it with a regular array like this $stmt->execute([$author, $is_published, $limit]);
 // Positional Params
 $sql = 'SELECT * FROM posts WHERE author = ? && is_published = ? LIMIT ?';
 $stmt = $pdo->prepare($sql);
 $stmt->execute([$author, $is_published, $limit]);
 $posts = $stmt->fetchAll();
 // With named parameters it execute with associative array like this $stmt->execute(['author' => $author, 'is_published' => $is_published]);
// Named Params
  $sql = 'SELECT * FROM posts WHERE author = :author && is_published = :is_published';
  $stmt = $pdo->prepare($sql);
  $stmt->execute(['author' => $author, 'is_published' => $is_published]);
  $posts = $stmt->fetchAll();

echo "<pre>";
print_r($posts);
echo "</pre>";

  //var_dump($posts);
  foreach($posts as $post){
    echo $post->title . '<br>';
  }

 // FETCH SINGLE POST
  $sql = 'SELECT * FROM posts WHERE id = :id';
  $stmt = $pdo->prepare($sql);
  $stmt->execute(['id' => $id]);
  $post = $stmt->fetch();
  echo $post->body;

// GET ROW COUNT
  $stmt = $pdo->prepare('SELECT * FROM POSTS WHERE author = ?');
  $stmt->execute([$author]);
  $postCount = $stmt->rowCount();

  // echo $postCount;

// echo $postCount;

 // INSERT DATA
  $title = 'Post Five';
  $body = 'This is post five';
  $author = 'Kevin';

  $sql = 'INSERT INTO posts(title, body, author) VALUES(:title, :body, :author)';
  $stmt = $pdo->prepare($sql);
  $stmt->execute(['title' => $title, 'body' => $body, 'author' => $author]);
  echo 'Post Added';

// UPDATE DATA
  $id = 1;
  $body = 'This is the updated post';

  $sql = 'UPDATE posts SET body = :body WHERE id = :id';
  $stmt = $pdo->prepare($sql);
  $stmt->execute(['body' => $body, 'id' => $id]);
  echo 'Post Updated';

  // DELETE DATA
  $id = 3;

  $sql = 'DELETE FROM posts WHERE id = :id';
  $stmt = $pdo->prepare($sql);
  $stmt->execute(['id' => $id]);
  echo 'Post Deleted';


  // SEARCH DATA
  $search = "%f%";
  $sql = 'SELECT * FROM posts WHERE title LIKE ?';
  $stmt = $pdo->prepare($sql);
  $stmt->execute([$search]);
  $posts = $stmt->fetchAll();

  foreach($posts as $post){
    echo $post->title . '<br>';
  }

//////////////////////////////////////////////////////////////////////////
// Lynda Couse
portable way to find a result in a result set is to run two queries
$count = $db->query("SELECT COUNT(*) FROM names");
$numrows = $count->fetchColumn();

$row = $result->fetch();
if(!$row) {
	echo "<p>No results found</p>"
} else {
}

Which Should I use?
query()
	Returns the result set for SELECT queries
	Retuns the SQL String with INSERT, UPDATE, and DELETE // INSERT INTO names (name, meaning, gender) VALUES ('William','resilute guardian', 'boy');
exec()
	Returns the number of rows affected;
	Better for INSERT, UPDATE, and DELETE

$sql =  "INSERT INTO names (name, meaning, gender) VALUES ('William','resilute guardian', 'boy')";
$result = $db->query($sql);
echo $result->queryString; // return INSERT INTO names (name, meaning, gender) VALUES ('William','resilute guardian', 'boy')

try {
	$sql =  "INSERT INTO names (name, meaning, gender) VALUES ('William','resilute guardian', 'boy')";
	$affected = $db->exex($sql);
	echo $affected . 'row inserted with ID' . $db->lastInsertedId();
} catch(Exeption $e) {
	$error - $getMessage();
}
/////////////////////////////////
// Insert
try {
	$sql =  "INSERT INTO names (name, meaning, gender) VALUES ('William','resilute guardian', 'boy')";
	$affected = $db->exec($sql);
	echo $affected . 'row inserted with ID' . $db->lastInsertedId(); // note lastInsertedI() it doesnt work to all database
} catch(Exeption $e) {
	$error - $getMessage();
}


if(isset($error)) {
	echo $error;
}
/////////////////////////////////
// Delete
try {
	$sql =  "DELETE FROM names WHERE name = 'William'";
	$affected = $db->exec($sql);
	echo $affected . 'records deleted';
} catch(Exeption $e) {
	$error - $getMessage();
}

if(isset($error)) {
	echo $error;
}

/////////////////////////////////
// error INfo
try {
	$sql = "SELECT name, meaning, gender FROM names ORDER BY name";
	$result = $db->query($sql);
	$errorInfo - $db->errorInfo();
	print_r($errorInfo);
} catch(Exeption $e) {
	$error - $getMessage();
}


/////////////////////////////////
// Using the quote() method to sanitize user input

Disadvantage of qoute()
	not supported by all PDO Drivers
	Dependent on the databases character set
	Character set must be set at server level or in DSN
	Often slower than prepare d statements	

try {
	$serchterm = '%' . $_GET['searchterm'] . '%';
	$sql = "SELECT name, meaning, gender FROM names WHERE name LIKE " . $db->quote($serchterm);
	$result = $db->query($sql);
	$errorInfo - $db->errorInfo();
	print_r($errorInfo);
} catch(Exeption $e) {
	$error - $getMessage();
}


/////////////////////////////////////////////////////////////
// Using named parameters
Using bindParam()
	Bind only a variable
	Value not evaluated until staement is executed
	"Fatel error: Cannot pass parameter 2 by reference"
Using bindValue()
	Binds a known value
	Useful for setting a field to null
	Example: $stmt->bindValue(':id', NULL, PDO:PARAM_NULL);
	Can be used with a variable as long its value is already set. However, you should normally use bindValue only when you need to use a actual value
	or to combine a variable with expression such as a calculation or part of the string. On the variable on its own always use bindParam()
Note bindParam() for variables and bindValue() for expressions such as a calculation or string
if (isset($_GET['search'])) {
    try {
        require_once '../../includes/pdo_connect.php';
        $sql = 'SELECT make, yearmade, mileage, price, description
                FROM cars
                LEFT JOIN makes USING (make_id)
                WHERE make LIKE :make AND yearmade >= :yearmade AND price <= :price
                ORDER BY price';
        $stmt = $db->prepare($sql);
        $stmt->bindValue(':make', '%' . $_GET['make'] . '%');
        $stmt->bindParam(':yearmade', $_GET['yearmade'], PDO::PARAM_INT);
        $stmt->bindParam(':price', $_GET['price'], PDO::PARAM_INT);
        $stmt->execute();
        $errorInfo = $stmt->errorInfo();
        if (isset($errorInfo[2])) {
            $error = $errorInfo[2];
        }
    } catch (Exception $e) {
        $error = $e->getMessage();
    }
}

/////////////////////////////////////////////////////////////
// Using question marks as anonymous placeholders

if (isset($_GET['search'])) {
    try {
        require_once '../../includes/pdo_connect.php';
        $sql = 'SELECT make, yearmade, mileage, price, description
                FROM cars
                LEFT JOIN makes USING (make_id)
                WHERE make LIKE ? AND yearmade >= ? AND price <= ? ORDER BY price';
        $stmt = $db->prepare($sql);
        $stmt->bindValue(1, '%' . $_GET['make'] . '%');
        $stmt->bindParam(2, $_GET['yearmade'], PDO::PARAM_INT);
        $stmt->bindParam(3, $_GET['price'], PDO::PARAM_INT);
        $stmt->execute();
        $errorInfo = $stmt->errorInfo();
        if (isset($errorInfo[2])) {
            $error = $errorInfo[2];
        }
    } catch (Exception $e) {
        $error = $e->getMessage();
    }
}

/////////////////////////////////////////////////////////////
// Passing an array of values to the execute() method
////////////////// PDO Named Execute //////////////////
if (isset($_GET['search'])) {
    try {
        require_once '../../includes/pdo_connect.php';
        $sql = 'SELECT make, yearmade, mileage, price, description
                FROM cars
                LEFT JOIN makes USING (make_id)
                WHERE make LIKE :make AND yearmade >= :yearmade AND price <= :price
                ORDER BY price';
        $stmt = $db->prepare($sql);
	$values = array(':price' => $_GET['price'],
			':make' => $_GET['make'],
			':yearmade' => $_GET['yearmade']);
        $stmt->execute($values);
        $errorInfo = $stmt->errorInfo();
        if (isset($errorInfo[2])) {
            $error = $errorInfo[2];
        }
    } catch (Exception $e) {
        $error = $e->getMessage();
    }
}
////////////////// PDO Anonymous Execute //////////////////
if (isset($_GET['search'])) {
    try {
        require_once '../../includes/pdo_connect.php';
        $sql = 'SELECT make, yearmade, mileage, price, description
                FROM cars
                LEFT JOIN makes USING (make_id)
                WHERE make LIKE ? AND yearmade >= ? AND price <= ? ORDER BY price';
        $stmt = $db->prepare($sql);
        $stmt->execute(array('%' . $_GET['make'] . '%', $_GET['yearmade'],$_GET['price']));
        $errorInfo = $stmt->errorInfo();
        if (isset($errorInfo[2])) {
            $error = $errorInfo[2];
        }
    } catch (Exception $e) {
        $error = $e->getMessage();
    }
}

/////////////////////////////////////////////////////////////
// Binding results to variables

if (isset($_GET['search'])) {
    try {
        require_once '../../includes/pdo_connect.php';
        $sql = 'SELECT make, yearmade, mileage, price, description
                FROM cars
                LEFT JOIN makes USING (make_id)
                WHERE make LIKE :make AND yearmade >= :yearmade AND price <= :price
                ORDER BY price';
        $stmt = $db->prepare($sql);
        $stmt->bindValue(':make', '%' . $_GET['make'] . '%');
        $stmt->bindParam(':yearmade', $_GET['yearmade'], PDO::PARAM_INT);
        $stmt->bindParam(':price', $_GET['price'], PDO::PARAM_INT);
        $stmt->execute();
        $stmt->bindColumn('make', $make);
        $stmt->bindColumn(2, $year);
        $stmt->bindColumn('mileage', $miles);
        $stmt->bindColumn('price', $price);
        $stmt->bindColumn('description', $desc);
        $errorInfo = $stmt->errorInfo();
        if (isset($errorInfo[2])) {
            $error = $errorInfo[2];
        }
    } catch (Exception $e) {
        $error = $e->getMessage();
    }
}

<?php if (isset($_GET['search'])) {
    $stmt->fetch(PDO::FETCH_BOUND);
    if ($make) {
    ?>
<table>
    <tr>
        <th>Make</th>
        <th>Year</th>
        <th>Mileage</th>
        <th>Price</th>
        <th>Description</th>
    </tr>
    <?php do { ?>
    <tr>
        <td><?php echo $make; ?></td>
        <td><?php echo $year; ?></td>
        <td><?php echo number_format($miles); ?></td>
        <td>$<?php echo number_format($price, 2); ?></td>
        <td><?php echo $desc; ?></td>
    </tr>
    <?php } while ($stmt->fetch(PDO::FETCH_BOUND)); ?>
</table>
<?php } else {
        echo '<p>No results found.</p>';
    } } ?>

Databse setup with PDO

try {
    $db = new PDO('sqlite:album.sqlite');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // var_dump($db);
    $result = $db->query('SELECT * FROM album');
    echo "<pre>";
    var_dump($result->fetchAll(PDO::FETCH_ASSOC));
    echo "<pre>";
} catch (PDOException $ex) {
  echo "There is something wrong " . $ex->getMessage();   
}

pdo_mvc_database setup

class Database {

    private $DB_HOST = "localhost";
    private $DB_USER = "root";
    private $DB_PASS = "";
    private $DB_NAME = "gallery_db";

    private $dbh;
    private $stmt;


    public function __construct() {

        $dns = 'mysql:host='.$this->DB_HOST.';dbname='.$this->DB_NAME;

        try {
            $this->dbh = new PDO($dns,$this->DB_USER, $this->DB_PASS);
            $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            $this->dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
            $this->dbh->setAttribute(PDO::ATTR_PERSISTENT, true);
        } catch (PDOException $e) {
            echo "Database connection failed ". $e->getMessage();
        }
        
    }

    // Query the database
   public function query($sql) {
        $this->stmt = $this->dbh->prepare($sql);
   }

   // Bind Values
   public function bind($param, $value, $type = null) {
    if(is_null($type)) {
        switch (true) {
            case is_int($value):
                $type = PDO::PARAM_INT;
                break;
             case is_bool($value):
                $type = PDO::PARAM_BOOL;
                break;
            case is_null($value):
                $type = PDO::PARAM_NULL;
                break;
            default:
                $type = PDO::PARAM_STR;
        }
    }
    $this->stmt->bindValue($param, $value, $type);
   }

   // Execute the prepared statement
   public function execute() {
       return $this->stmt->execute();
   }

   // Get result set as a associative array
   public function resultsSet() {
        $this->execute();
        return $this->stmt->fetchAll();
   }

    // Get single record as a associative array
   public function singResult() {
        $this->execute();
        return $this->stmt->fetch();
   }

   // Get row count
   public function rowCount() {
    return $this->stmt->rowCount();
   }

}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment