I am tring to learn about PDO people have said its easy and the best way to interact with the database, but im not finding it easy. can any help?
Connection.php
<?php
class Connection {
public function dbConnect() {
return new PDO("mysql:host=localhost; dbname=lr", "root", "");
}
}
?>
index.php
<?php
require_once('pdo.php');
$obj = new Connection();
$obj->dbConnect();
echo 'hello';
?>
This works but its too simple. what is the best way?
Should i have my connection in its own class and extend it to the rest of my classes or should i use a global variable to connect? or is there another way better way? Thanks
You are just creating a connection and not telling us what you are trying to achieve. Do you want to post, update, delete something in the database or what?
The best I can do since you aren't asking anything is link you to a website that will try to teach you the basics. I hope you have used databases before though.
http://net.tutsplus.com/tutorials/php/php-database-access-are-you-doing-it-correctly/
It's indeed easy, in a way.
Using global variable would be easier. and of course you need to use correct filenames for include.
Straight from PDO tag wiki:
pdo.php
<?php
$dsn = "mysql:host=localhost;dbname=lr;charset=utf8";
$opt = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$pdo = new PDO($dsn,'root','', $opt);
index.php
<?php
ini_set('display_errors',1);
error_reporting(E_ALL);
require_once('pdo.php');
$stm = $pdo->prepare("SELECT name FROM table WHERE id=?");
$stm->execute(array($_GET['id']));
$name = $stm->fetchColumn();
echo "Hello $name";
Check this out (https://gist.github.com/dirte/5331258):
<?php
/*
* PDO Database class
*
* Usage:
* $db = Database::getInstance();
* Return array of query results, normal statement: $results = $db->query("SELECT * FROM test WHERE name = 'Bob'");
* Return array of query results, prepared statement (named params): $results = $db->query("SELECT * FROM test WHERE name = :name", array(":name" => "matthew"));
* Return int of last insert result row id: $db->lastInsertId()
* Return int of last query result row count: $db->lastQueryRowCount()
*/
class Database {
/*
* Instance of the database class
*
* @static Database $instance
*/
private static $instance;
/*
* Database connection
*
* @access private
* @var PDO $connection
*/
private $connection;
/*
* Constructor
*
* @param $dsn The Data Source Name. eg, "mysql:dbname=testdb;host=127.0.0.1;port=3306"
* @param $username
* @param $password
*/
private function __construct() {
$this->connection = new PDO("mysql:dbname=".DBNAME.";host=".HOST.";port=".PORT, USER, PASS, array(
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_TIMEOUT => DBTIMEOUT));
if (empty($this->connection)) {
trigger_error("Error #D001:", E_USER_ERROR);
return false;
}
$this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
/*
* Gets an instance of the Database class
*
* @static $instance
* @return Database An instance of the database singleton class
*/
public static function getInstance() {
if (empty(self::$instance)) {
try {
self::$instance = new Database();
} catch (\PDOException $e) {
trigger_error("Error #D002: ".$e->getMessage(), E_USER_ERROR);
}
}
return self::$instance;
}
/*
* Runs a query using the current connection to the database
*
* @param string query
* @param array $args An array of arguments for the sanitization such as array(":name" => "foo")
* @return array Containing all the remaining rows in the result set.
*/
public function query($query, $args = false) {
$tokens = array_map('trim',explode(" ",trim($query)));
$query = str_replace(array("\r\n", "\r", "\t"), " ", $query);
$query = str_replace(' ', ' ', $query);
try {
// Prepare results
$results=false;
// Allow for rollback if query fails
$this->connection->beginTransaction();
// Prepared statements
$sth = $this->connection->prepare($query);
// Execute prepared statement, with or without arguments
if (empty($args)) {
$sth->execute();
} else {
$multiple = false;
foreach ($args as $arg) {
if (!is_array($arg)) { continue; }
$multiple = true;
break;
}
if ($multiple) {
$i=0;$j=count($args);
foreach ($args as $arg) {
foreach ($arg as $k=>$v) {
if ($v === "NULL") { $arg[$k] = NULL; }
}
$sth->execute($arg);
$i++;
}
} else {
$i=0;$j=count($args);
foreach ($args as $a=>$arg) {
if ($arg === "NULL") {$args[$a] = NULL;}
$i++;
}
$sth->execute($args);
}
}
// SELECT: Return array of data or false if 0 rows
if ($tokens[0] == "SELECT") {
$sth->setFetchMode(PDO::FETCH_ASSOC);
$results = $sth->fetchAll();
}
// INSERT/UPDATE/REPLACE: Return number of affected rows / array of affected ids ?
// Note: lastInsertId only works if ID col on table is auto_incremented
elseif ($tokens[0] == "INSERT"
|| $tokens[0] == "UPDATE"
|| $tokens[0] == "REPLACE") {
// If sessions table, assume key = return id
$results = $this->connection->lastInsertId();
}
// Else: Return number of affected rows
else {
$results = $sth->rowCount();
}
// Attempt to commit changes, triggers exception if fails
$this->connection->commit();
// Rollback changes on failure
} catch (\PDOException $e) {
$msg = 'query(): ***** Caught Exception! Rolling back changes *****'.PHP_EOL.'<hr />Query:<pre>'.$query.'</pre>'.PHP_EOL.'<hr />Exception Message:<pre>'.$e->getMessage().'</pre><hr />'.PHP_EOL;
$this->connection->rollBack();
trigger_error($msg, E_USER_ERROR);
return false;
}
return $results;
}
/*
* Returns the last insert result row id
*
* @return int of last insert result row id
*/
public function lastInsertId() {
return $this->connection->lastInsertId();
}
/*
* Returns the last query result row count
*
* @return int of last query result row count
*/
public function lastQueryRowCount() {
return $this->connection->lastQueryRowCount();
}
}
?>
