PDO in a php class

[Pain]

Hi there. Can someone give me an example of how to use PDO in a class?

 

I have this class called Join in class.join.php

class Join {
 
private $name;
private $lastname;
private $email;
private $email_repeat;
private $password;
 
 
public function newUser($name, $lastname, $email, $email_repeat, $password) {
 
$this->name = $name;
$this->lastname = $lastname;
$this->email = $email;
$this->email_repeat = $email_repeat;
$this->password = $password;
    
    if(!empty($this->name) && !empty($this->lastname) && !empty($this->email) && !empty($this->email_repeat) && !empty($this->password)) {
 

    $database->query("INSERT INTO users (name, lastname, email, password) VALUES (:name, :lastname, :email, :password)");
    $database->bind(':name', $this->name);
    $database->bind(':lastname', $this->lastname);
    $database->bind(':email', $this->email);
    $database->bind(':password', $this->password);
    $database->execute();
    return TRUE;
 
    }
 
 
 
}
 
 
   
 
 
}

Then i have my Database class in class.database.php

class Database {
    
    
    private $host      = DB_HOST;
    private $user      = DB_USER;
    private $pass      = DB_PASS;
    private $dbname    = DB_NAME;
 
    private $dbh;
    private $error;
    private $stmt;
 
    public function __construct(){
        // Set DSN
        $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname;
        // Set options
        $options = array(
            PDO::ATTR_PERSISTENT    => true,
            PDO::ATTR_ERRMODE       => PDO::ERRMODE_EXCEPTION
        );
        // Create a new PDO instanace
        try{
            $this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
        }
        // Catch any errors
        catch(PDOException $e){
            $this->error = $e->getMessage();
        }
    }
 
public function query($query){
    $this->stmt = $this->dbh->prepare($query);
}
 
 
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);
}
 
 
public function execute(){
    return $this->stmt->execute();
}
 
 
public function resultset(){
    $this->execute();
    return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
}
 
 
public function single(){
    $this->execute();
    return $this->stmt->fetch(PDO::FETCH_ASSOC);
}
 
 
public function rowCount(){
    return $this->stmt->rowCount();
}
 
}

any help is appreciated, thank you!

Edited February 4, 2014 by Pain

[doddsey_65]

You would need to add an instance of database to the Join class scope. A simple way would be

$joinClass->database = $database

and then in the join class you would reference it using

$this->database

[Pain]

Thanks for the reply. I've instantiated it the way you said and then referenced it, but it didn't work.

[trq]

Post your code.

[doddsey_65]

Can't do much without the code. But just incase you didn't do something I should have mentioned you need to add the property to the Join class

public $database;

[Pain]

Sorry guys, my explanation was really poor. Here is what I want to do.

 

I want to use the Join Class to manipulate the database.

 

I want my class.join.php to look somewhat like this:

class Join {
 
    public $database;
    
private $name;
private $lastname;
private $email_repeat;
private $password;
 
public $email;
 
public $subject = 'Welcome to HomeLocator';
public $message;
 
 
public function newUser($name, $lastname, $email, $email_repeat, $password) {
 
$this->name = $name;
$this->lastname = $lastname;
$this->email = $email;
$this->email_repeat = $email_repeat;
$this->password = $password;
    
    if(!empty($this->name) && !empty($this->lastname) && !empty($this->email) && !empty($this->email_repeat) && !empty($this->password)) {
    
    $database->query("INSERT INTO users (name, lastname, email, password, date_joined) VALUES (:name, :lastname, :email, :password, :date_joined)");
    $database->bind(':name', $name);
    $database->bind(':lastname', $lastname);
    $database->bind(':email', $email);
    $database->bind(':password', $password);
    $database->bind(':date_joined', $date_joined);
    $database->execute();
 
    $_SESSION['email'] = $this->email;
    return TRUE;
 
    }
 
 
 
}
}

So in the index.php file I would obviously instantiate the Join class

$join = new Join;

and try to insert into the db:

 

$join->newUser();

 

Note that my database class is in yet another file (class.database.php)

 

I think this approach Im taking is wrong.
 

Edited February 4, 2014 by Pain

[ignace]

Your Join class does too much work, you need to delegate the work between classes and let them each handle their job:

 

<?php

namespace My\Model\Entity;
{
    use My\Model\Entity\ValueObject\Email;
    use My\Model\Entity\ValueObject\Password;
    
    // represents a User in the system
    class User
    {
        private $id;
        private $fname;
        private $lname;
        private $email;
        private $password;
        
        public function setId($id) { $this->id = $id; return $this; }
        public function setFirstName($id) { $this->fname = $id; return $this; }
        public function setLastName($id) { $this->lname = $id; return $this; }
        public function setEmail(Email $id) { $this->email = (string) $id; return $this; }
        public function setPassword(Password $id) { $this->password = (string) $id; return $this; }
        
        public function getId() { return $this->id; }
        public function getFirstName() { return $this->fname; }
        public function getLastName() { return $this->lname; }
        public function getEmail() { return $this->email; }
        public function getPassword() { return $this->password; }
    }
}

namespace My\Model\ValueObject
{
    // holds a password, makes sure it's valid,
    // is passed around between objects without having to check if it's valid, it is!
    class Password { }
    
    // holds an e-mail address, makes sure it's valid
    // is passed around between objects without having to check if it's valid, it is!
    class Email { }
}

namespace My\App
{
    // persists state for a user between requests
    class Session { }
}

namespace My\DataStore\DataMapper
{
    use PDO;
    
    // performs the mapping of a user to a record in the database and vice versa
    // translates a record in the database to a User object.
    class UserDataMapper
    {
        private $database;
        
        public function __construct(PDO $pdo) {
            $this->database = $pdo;
        }
        
        public function insert(User $user) {
            $stmt = $this->database->prepare('INSERT INTO users (id, fname, lname, email, password) VALUES (NULL, :fname, :lname, :email, :password)');
            $stmt->bindValue(':fname', $user->getFirstName());
            $stmt->bindValue(':lname', $user->getLastName());
            $stmt->bindValue(':email', $user->getEmail());
            $stmt->bindValue(':password', $user->getPassword());
            $stmt->execute();
            
            $user->setId($this->database->lastInsertId());
            return true;
        }
        
        public function update(User $user) {}
        
        public function delete(User $user) {}
    }
}

So that you can combine them to:

 

namespace My\Model\Service
{
    // sends a specific e-mail
    class SendUserRegistrationMailerService { }
    
    // creates a new user in the system
    // notifies the user and requests him to verify his e-mail address if necessary
    // works with other services to achieve it's goal
    class RegisterUserService {
        private $session;
        private $userRegistrationMailer;
        private $userDataMapper;
        
        public function registerUser(..) {
            $user = new User(..);
            $this->userDataMapper->insert($user);
            $this->userRegistrationMailer->sendUserRegistrationMail($user, $this->emailVerificationRequired);
            $this->session->createAuthSession($user);
        }
    }
}

Edited February 5, 2014 by ignace

[sKunKbad]

For what it's worth, if you like the idea of using PDO, then you might want to check out Doctrine DBAL:

 

http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html

 

This is mostly a PDO wrapper with some convenience methods. It also has excellent examples. Usage is very much like using PDO, so the docs would be worth reading, even if you don't end up using it.

[john_c_1984]

Although I do myself now use Symfony and Doctrine and would highly recomend this to anyone. I did write a simple PDO layer for ease of use (when i was rolling my own).. reduced the amount of code I had to write all the time (this forum editor and my editor don't agree so the spacing seems a little ott but you get the picture)

//***** in a database class file*****/
namespace yourproject;
class Database {
	
	private $db_con = '';
	
	/*** Function to login to the database ***/
	public function db_login()
		{
			// Try to connect
			try{
					// Retrieve the login details
					require_once WEBROOT_PRIVATE.'logins/General_login.php';
					
					// Connect to the server and select database
					$this->db_con = new \PDO("mysql:host=$db_hostname;dbname=$db_database",
												"$db_username",
												"$db_password",
												array(\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
					
					// Prevent emulation of prepared statements for security
					$this->db_con->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);
					$this->db_con->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
					
					return true;
				}
			// If it fails, send user to maintenance page
			catch(PDOException $e)
				{
					header("location:$_WEBROOT/maintenance.php");
					exit();
				}
		}
		
	/*** Function for database control ***/
	public function db_control($query , $parameters, $returnID = false)
		{
			if(!is_array($query) && is_array($parameters))
				{
					try{
							//prepare the statement
							$statement = $this->db_con->prepare($query);
							
							//execute the statement
							$statement->execute($parameters);
							
							//check whether this is a select, if it is then we need to retrieve the selected data
							if(strpos($query, 'SELECT') !== false)
								{
									//fetch the results
									$result = array();
									while( $row = $statement->fetch(\PDO::FETCH_ASSOC) )
										{
											$result[] = $row;
										}
									
									//count the results
									$count = count($result);
									
									//return the array
									return array( 'results' => $result, 'result_count' => $count );
								}
							//else return the number of affected rows
							else{
									//count the affected rows and place into a returnable array
									$affected_rows = $statement->rowCount();
									$returnArray = array('result_count' => $affected_rows);
								
									//check to see if we are to return a newly inserted autoincrement ID from an INSERT
									if($returnID)
										{
											//find the newly created ID and add this data to the return array
											$insertID = $this->db_con->lastInsertId();
											$returnArray['ID'] = $insertID;
										}
									
									return $returnArray;
								}
						}
					catch(PDOException $e)
						{
							return false;
						}
				}
			else{
					return false;
				}
		}
}

//***** Place this in a global functions file ********//
// Start the database class and connect to the database
$db = new \yourproject\Database();
$db->db_login();
function _db( $sql , $params , $returnID = false ){
	return $GLOBALS['db']->db_control( $sql , $params , $returnID );
}

//*****From anywhere in your project ******//
//then plonk any sql query in like so (as you have declared the db_control from the class as a global function) from anywhere in your application.
$sql = 'SELECT ID FROM user WHERE name=:name';
$params = array(':name' => 'Bill Gates');
$query = _db($sql, $params);

//the $query is now an assoc array with 'results' and 'result_count'.. extend accordingly.

In particular, i wouldn't bother bind the values individually (see inside the db_control function):

//prepare the statement
$statement = $this->db_con->prepare($query);                            
//execute the statement
$statement->execute($parameters);

Edited February 5, 2014 by john_c_1984

[trq]

this forum editor and my editor don't agree

Hence it is recommended to always use spaces instead of tabs when writing code.

[john_c_1984]

Hence it is recommended to always use spaces instead of tabs when writing code.

 

tabs are the main sticking point on yml for most people