phppdo

How to do queries with pdo db connection function?


I read too many questions and answers around but couldn't be sure. I have 2 questions

I turned my db connection into a function and I am not sure if its safe?

define('DB_SERVER', 'localhost'); 
define('DB_USERNAME', 'root'); 
define('DB_PASSWORD', ''); 
define('DB_NAME', 'demo'); 
 
function DB()
{
    try {
        $pdo = new PDO('mysql:host='.DB_SERVER.';dbname='.DB_NAME.'', DB_USERNAME, DB_PASSWORD);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $pdo;
    } catch (PDOException $e) {
        return "Error!: " . $e->getMessage();
        die();
    }
}

Is my query done right way?

Query:

try {
    $pdo = DB();
    $stmt = $pdo->prepare("SELECT * FROM settings"); 
    $stmt->execute();
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
                        $c = htmlspecialchars($row['site_url']);
                        $e = filterString($row['contact']);
    } 
    unset($stmt);   
    } catch (PDOException $e) {
            exit($e->getMessage());
        }

Solution

  • Perhaps keep one connection, rather than opening multiple connections to the Database. You can look into a project PDOEasy that I created to make MVC easy with PDO or use the below static example.

    class DB
    {
         private $_connection;
         private static $_instance;
    
         public static function getInstance() {
             if(self::$_instance) return self::$_instance;
             self::$_instance = new self();
             return self::$_instance;
         }
    
         private function __construct() {
             $this->_connection = new PDO('mysql:host='.DB_SERVER.';dbname='.DB_NAME.'', DB_USERNAME, DB_PASSWORD, array(
                  PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
                  PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
                  PDO::ATTR_EMULATE_PREPARES => false
             ));
         }
    
         public function getConnection() { return $this->_connection; }
    }
    

    Which can be used like so:

    $stmt = DB::getInstance()
                ->getConnection()
                ->Prepare('SELECT * FROM settings');
    
    $stmt->execute();
    foreach($stmt->fetchAll() as $row) {
        // ...
    }