Link to home
Start Free TrialLog in
Avatar of myyis
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


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;
}

Open in new window

Avatar of Gary
Gary
Flag of Ireland image

Can you give some examples of your $sql and $arr - just to be sure you are preparing everything correctly.
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.
Avatar of myyis
myyis

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,LOGIN_DATE_TIME,ORID) VALUES ('$userId','$session_id','$ipset','$login_date_time','$oridtry')";
insertsql($sql);

When I need to check the data I use this

$sql = "insert into CONTACT (NAME,TYPE,ORID,USERID,DATETIME,USERIDEDIT,DATETIMEEDIT,PARENTCONTACTNAME,PARENTCONTACTID)  values (?,'P','$orid','$uid','$date','$uid','$date','$cname','$cid')";
                  
$cid=insertsqlp($sql,array($searchStr));
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(':user_id'=>$user_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.
Avatar of myyis

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,DATETIME,USERIDEDIT,DATETIMEEDIT,PARENTCONTACTNAME,PARENTCONTACTID)  values (?,'P','$orid','$uid','$date','$uid','$date','$cname','$cid')";
                 
$cid=insertsqlp($sql,array($searchStr));
ASKER CERTIFIED SOLUTION
Avatar of Gary
Gary
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.

<?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);

Open in new window

Avatar of myyis

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?
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.
@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!)
Avatar of myyis

ASKER

Thank you Ray I will buy the book, it looks very useful.