myyis
asked on
PDO connection function
Hi,
I have constructed my PDO connection function as below.
1. How does it look? Do you see any problem? Any comment to make it better?
2. I don't use function to close the connection (like mysql_close). I read somewhere it is better for PDO connection performance, is that ok?
Thank you
I have constructed my PDO connection function as below.
1. How does it look? Do you see any problem? Any comment to make it better?
2. I don't use function to close the connection (like mysql_close). I read somewhere it is better for PDO connection performance, is that ok?
Thank you
function dbconnect()
{
global $dbpdo;
if ($_SESSION['dbchooseses']==1) {
$db_username='dbuser';
$db_name='dbname';
$db_password = 'psw';
$db_host ='localhost';
}else if ($_SESSION['dbchooseses']==2) {
$db_username='dbuser2';
$db_name='dbname2';
$db_password = 'psw2';
$db_host ='localhost';
}
try {
$dbpdo = new PDO("mysql:host=$db_host;dbname=$db_name", $db_username, $db_password, array(PDO::ATTR_PERSISTENT => true));
$dbpdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbpdo->setAttribute(PDO::ATTR_TIMEOUT, 30);
$dbpdo->query("SET NAMES utf8");
}
catch(PDOException $e)
{
$e->getMessage();
die();
}
}
function selectsql($sql) {
global $dbpdo;
$r = $dbpdo->query($sql)->fetchAll();
if ($r[0]) return $r[0];
else return Array();
}
function selectsqln($sql) {
global $dbpdo;
$r = $dbpdo->query($sql)->fetchAll();
return $r;
}
function selectsqlp($sql,$arr) {
global $dbpdo;
try{
$q = $dbpdo->prepare($sql);
$q-> execute($arr);
}catch (PDOException $e) {
$e->getMessage();
$r = $q ->fetchAll();
return $r[0];
}
function selectsqlpn($sql,$arr) {
global $dbpdo;
try{
$q = $dbpdo->prepare($sql);
$q-> execute($arr);
}catch (PDOException $e) {
$e->getMessage();
$r = $q ->fetchAll();
return $r;
}
function insertsql($sql) {
checktime($sql);
global $dbpdo;
try{
$dbpdo->query($sql);
}catch (PDOException $e) {
$e->getMessage();
$r = $dbpdo->lastInsertId();
return $r;
}
function insertsqlp($sql,$arr) {
checktime($sql);
global $dbpdo;
$q = $dbpdo->prepare($sql);
try{
$q-> execute($arr);
}catch (PDOException $e) {
$e->getMessage();
$r = $dbpdo->lastInsertId();
return $r;
}
function updatesql($sql) {
global $dbpdo;
try{
$dbpdo->query($sql);
}catch (PDOException $e) {
$e->getMessage();
}
function updatesqlp($sql,$arr) {
global $dbpdo;
$q = $dbpdo->prepare($sql);
try{
$q-> execute($arr);
}catch (PDOException $e) {
$e->getMessage();
return $q;
}
ASKER
When the parameters are created by the code, I use this.
This is the case I am sure that the data is safe
$sql="INSERT INTO LOGIN_HISTORY (LOGIN_ID,SESSION_ID,IP,LO GIN_DATE_T IME,ORID) VALUES ('$userId','$session_id',' $ipset','$ login_date _time','$o ridtry')";
insertsql($sql);
When I need to check the data I use this
$sql = "insert into CONTACT (NAME,TYPE,ORID,USERID,DAT ETIME,USER IDEDIT,DAT ETIMEEDIT, PARENTCONT ACTNAME,PA RENTCONTAC TID) values (?,'P','$orid','$uid','$da te','$uid' ,'$date',' $cname','$ cid')";
$cid=insertsqlp($sql,array ($searchSt r));
This is the case I am sure that the data is safe
$sql="INSERT INTO LOGIN_HISTORY (LOGIN_ID,SESSION_ID,IP,LO
insertsql($sql);
When I need to check the data I use this
$sql = "insert into CONTACT (NAME,TYPE,ORID,USERID,DAT
$cid=insertsqlp($sql,array
That is not how you should be using PDO, part of its purpose is to make sanitizing inserted data easy.
Example process would be
Prepare the statement - :user_id is the variable we use in the execute
$stmt = $conn->prepare('select * from table where id=:user_id)');
In the execute assign the user_id variable to its value
$stmt->execute(array(':use r_id'=>$us er_id));
So while your functions are ok you should be passing the sql statement in a prepared way and then the array of values to use in the statement.
Another benefit of PDO is that when you are using prepared statements you only need to declare them once and then you only have to use the execute line each time you need to make the same query
You say the data is safe, but it would be better to get into the practise of doing it the right way.
Example process would be
Prepare the statement - :user_id is the variable we use in the execute
$stmt = $conn->prepare('select * from table where id=:user_id)');
In the execute assign the user_id variable to its value
$stmt->execute(array(':use
So while your functions are ok you should be passing the sql statement in a prepared way and then the array of values to use in the statement.
Another benefit of PDO is that when you are using prepared statements you only need to declare them once and then you only have to use the execute line each time you need to make the same query
You say the data is safe, but it would be better to get into the practise of doing it the right way.
ASKER
In the example below $date is generated by php server , so I don't have to sanitize it.
Is this right? When I am preparing the statement, do I have to put all the parameters to array values?
function insertsqlp($sql,$arr) {
global $dbpdo;
$q = $dbpdo->prepare($sql);
try{
$q-> execute($arr);
}catch (PDOException $e) {
$e->getMessage();
$r = $dbpdo->lastInsertId();
return $r;
}
$sql = "insert into CONTACT (NAME,TYPE,ORID,USERID,DAT ETIME,USER IDEDIT,DAT ETIMEEDIT, PARENTCONT ACTNAME,PA RENTCONTAC TID) values (?,'P','$orid','$uid','$da te','$uid' ,'$date',' $cname','$ cid')";
$cid=insertsqlp($sql,array ($searchSt r));
Is this right? When I am preparing the statement, do I have to put all the parameters to array values?
function insertsqlp($sql,$arr) {
global $dbpdo;
$q = $dbpdo->prepare($sql);
try{
$q-> execute($arr);
}catch (PDOException $e) {
$e->getMessage();
$r = $dbpdo->lastInsertId();
return $r;
}
$sql = "insert into CONTACT (NAME,TYPE,ORID,USERID,DAT
$cid=insertsqlp($sql,array
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This article has examples that map MySQL, MySQLi and PDO across equivalent functions.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
This code snippet shows a Singleton design for a data base connection. Using the information in the article, it would be possible to replace the extension with the PDO extension.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
This code snippet shows a Singleton design for a data base connection. Using the information in the article, it would be possible to replace the extension with the PDO extension.
<?php // RAY_database_singleton.php
error_reporting(E_ALL);
// SINGLETON DATA BASE CONNECTION CLASS
class Database
{
// CLASS PROPERTIES ARE ALL PRIVATE
private static $connection;
private static $instance;
// CONNECTION VALUES (GET THESE FROM YOUR HOSTING COMPANY
const DB_HOST = '?';
const DB_USER = '?';
const DB_PASS = '?';
const DB_NAME = '?';
// NULLIFY THE CLONE
final private function __clone() {}
// OUR ONLY PUBLIC METHOD RETURNS THE CONNECTION
public static function getConnection()
{
if (!self::$instance) self::$instance = new self();
return self::$connection;
}
// CONSTRUCTOR RETURNS THE CONNECTION
private function __construct()
{
self::$connection
= new mysqli
( self::DB_HOST
, self::DB_USER
, self::DB_PASS
, self::DB_NAME
)
;
if (self::$connection->connect_error)
{
trigger_error(self::$connection->connect_error, E_USER_ERROR);
}
}
}
$mysql1 = database::getConnection();
$mysql2 = database::getConnection();
// PROVE THAT THESE ARE THE SAME OBJECT http://php.net/manual/en/language.oop5.object-comparison.php
if ($mysql1 === $mysql2) echo 'EQUAL';
// SHOW THE OBJECT
var_dump($mysql1);
ASKER
Ok one last question
I don't use function to close the connection (like mysql_close). I read somewhere it is better for PDO connection performance, is that ok?
I don't use function to close the connection (like mysql_close). I read somewhere it is better for PDO connection performance, is that ok?
The connection would be closed when the page finishes executing anyway, so no need to manually close it.
Cannot see how it would affect performance manually closing it unless you were reopening it again.
Cannot see how it would affect performance manually closing it unless you were reopening it again.
@myyis: Please, please, please buy this book and don't write any PHP code that has any economic value until you've finished working your way through it. I've just re-read this question and it seems to me that you have some giant holes in your understanding of computer science and PHP. That is normal and to be expected when you're just starting out, and the Welling/Thompson book can help you fill in the blanks with something that will not get you fired (or worse, sued!)
ASKER
Thank you Ray I will buy the book, it looks very useful.
For example here
function insertsql($sql) {
You don't have any params passed so I assume you are building the sql directly which defeats the purpose of PDO make data safe queries.