Smarty Forum Index Smarty
The discussions here are for Smarty, a template engine for the PHP programming language.
PDO (PHP Data Objects) - caching prepared statements

 
Post new topic   Reply to topic    Smarty Forum Index -> Tips and Tricks
View previous topic :: View next topic  
Author Message
AnrDaemon
Smarty Rookie


Joined: 03 Dec 2012
Posts: 34

PostPosted: Mon Dec 03, 2012 4:38 am    Post subject: PDO (PHP Data Objects) - caching prepared statements Reply with quote

It's not a Smarty trick, but I think it's still worth sharing.
When you work with database, the performance increase from using prepared statements could be from mere 2% to 25% and more. But you often run into an issue of using statement call inside a function, making reuse of a prepared statement something of a problem.
Well, I've solved it the most straightforward way:
Code:
function get_page_details($id)
{
  if(isset($GLOBALS['_pdo_query'][__FUNCTION__]))
  {
    $_query = $GLOBALS['_pdo_query'][__FUNCTION__];
  }
  else
  {
    $_query = $GLOBALS['_pdo']->prepare('SELECT s.*, t.tname, t.alias
      FROM structure s, templates t
      WHERE t.id = s.template_id
        AND s.id = ?
      LIMIT 1');
    $GLOBALS['_pdo_query'][__FUNCTION__] = $_query;
  }

  if($_query->execute(array((int)$id)))
  {
    $result = $_query->fetch();
    return $result;
  }
  return false;
}


All prepared statememnts are stored in global scope, each associated with it's function name.
Functions, that use multiple statements (while they are few and far between, sometimes you need a few additional pieces from database before you can save a new row, or you need to alter 2 or more tables for one function call (a very bad practice - it makes tracking changes harder, but still happens sometimes)), save them with some __tail, clarifying the statement meaning.
Back to top
View user's profile Send private message
tron2k
Smarty Rookie


Joined: 01 Dec 2010
Posts: 7

PostPosted: Wed Jan 16, 2013 2:26 am    Post subject: Reply with quote

How about putting the function into an object?
Back to top
View user's profile Send private message
JHON-STIFFLER
Smarty n00b


Joined: 31 Mar 2013
Posts: 3

PostPosted: Mon Apr 01, 2013 8:47 am    Post subject: Reply with quote

If you use $dbh = new PDO('pgsql:host=localhost;dbname=test_basic01', $user, $pass); and you get the following error:
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[08006] [7] could not connect to server: Connection refused\n\tIs the server running on host "localhost" and accepting\n\tTCP/IP connections on port 5432?'
then as pointed out under pg_connect at: http://www.php.net/manual/en/function.pg-connect.php#38291
******
you should try to leave the host= and port= parts out of the connection string. This sounds strange, but this is an "option" of Postgre. If you have not activated the TCP/IP port in postgresql.conf then postgresql doesn't accept any incoming requests from an TCP/IP port. If you use host= in your connection string you are going to connect to Postgre via TCP/IP, so that's not going to work. If you leave the host= part out of your connection string you connect to Postgre via the Unix domain sockets, which is faster and more secure, but you can't connect with the database via any other PC as the localhost.
******
Back to top
View user's profile Send private message
AnrDaemon
Smarty Rookie


Joined: 03 Dec 2012
Posts: 34

PostPosted: Mon Apr 22, 2013 12:30 pm    Post subject: Reply with quote

tron2k wrote:
How about putting the function into an object?

What for? Just because? Why everyone think that objects are catch-all solution to life, universe and all that shit?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Smarty Forum Index -> Tips and Tricks All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group
Protected by Anti-Spam ACP