Hello,
I am having trouble with creating a user defined function for PDO database connection. First, here’s my code:
function set_dbh() {
global $hostname, $dbname, $username, $password; //not sure if this line is required... do these variables get evalutated when execute() is used?
$hostname='hostname';
$dbname='dbname';
$username='username';
$password='password';
global $dbh;
$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
}
Then when I go to prepare my statement WITHIN another function:
function display_product() {
$pkid = 12;
set_dbh();
$stmt = $dbh->prepare("SELECT * FROM exampleproducttable WHERE pkid = :pkid");
$stmt->bindParam(':pkid', $pkid);
$stmt->execute();
}
I am getting the following error: Fatal error: Call to a member function prepare() on a non-object HOWEVER, if I combine the same code and run it all in one function (see below), it works perfect. I thought this might have to do with variable scope, which is why I tried setting the variables to global in set_dbh(); …any advice?
// this works fine when it's all in one function
function display_product() {
$pkid = 12;
$hostname='hostname';
$dbname='dbname';
$username='username';
$password='password';
$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
$stmt = $dbh->prepare("SELECT * FROM exampleproducttable WHERE pkid = :pkid");
$stmt->bindParam(':pkid', $pkid);
$stmt->execute();
}
The idea is to not have to run the following code in every function that contains a prepared statement on the site:
$hostname='hostname';
$dbname='dbname';
$username='username';
$password='password';
$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
That’s 5 lines of code. I thought it I create the function set_dbh(), I can call those lines of code just by calling set_dbh(). This would also help if I ever decide to move the database, change a password, etc. because then I would just have to connect info in the set_dbh() function and it would apply anywhere set_dbh() is used.
As for variable scope, doesn’t making $dbh a global variable in set_dbh() define $dbh in my display_product() function?
I figured it out. I did not even need to use global vars. I needed to return the value in my set_dbh() function, and then define a database handling variable to be used in my display_product() function using set_dbh() to define the new variable (rather then just calling set_dbh() by itself) …just like you said, $dbh wasn’t defined in display_product(). Thank you for the hint!
For the benefit of others, here’s the working code:
function set_dbh() {
$hostname='hostname';
$dbname='dbname';
$username='username';
$password='password';
$set = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
return $set;
}
function display_product() {
$pkid = 12;
$dbh = set_dbh();
$stmt = $dbh->prepare("SELECT * FROM exampleproducttable WHERE pkid = :pkid");
$stmt->bindParam(':pkid', $pkid);
$stmt->execute();
}
Be careful. Every time you call that function, you open a new database connection. The connection process does have overhead, and the mysql server often imposes a relatively low limit on how many simultaneous connections it will service. Be mindful of this or you may exhaust your resources prematurely.
You might as well just use the $dbh variable as a global.
Maybe not the most flexible design, but it’s common, and convenient to do something to the nature of using require_once ‘dbconnect.php’ which connects to the database and defines a global symbol that the rest of the script can use.
require_once() will check to see if it’s been included already in the same php file. i will only be using it set_dbh() once per php page. as soon as the user get’s redirected to another php page that requires the database, doesn’t the connection will need to be re-established? or does it remain open? this is different than opening a session right.
if i do use a require_once() instead of a user-defined function, wouldn’t it open that connect at the top of every PHP file? if the user accesses 5 different php files that interact with the database, wouldn’t that open up 5 connections during the time he/she viewed all 5 pages? is there a such thing as closing a PDO connection, once all the data has been read by the database?
anyway… see i was under the assumption that $dbh=new PDO(connection details); just sets the database handling details, and execute(); actually makes the connection, performs the query, and closes the connection. who’s to say my assumption means anything though. can anybody verify how the connection process works with PDO?
i feel stupid (ignore my last post), sorry i should have done my research. a simple test confirmed that even if execute() is never used on a page, if the database handling details in the new PDO statement are wrong it will throw an error because it cannot connect. on this subject though, WHEN is the connection actually closed? when the user leaves the page or gets redirected to another?
as for my function set_dbh()… maybe it’s easier to use require_once(), but is it possible to check if there is an open connection using a conditional statement, and only make a new connection if there already isn’t one already open?
The thing is that if you use two functions on a page, display_product() update_product(), then you will call set_dbh() twice and make two connections. It’s possible to check in any number of ways, but doing it with functions like that isn’t gonna be any better than just requiring connect.php on pages that need it.
When the php script finishes running all resources are destroyed, including db connections; you don’t need to close them manually.
Yes I understand this, but I was asking if you can use a conditional statement within the set_dbh() function to detect if a connection is already open, and if so the function would not open a second connection.
I think you guys are right though. I might as well forget the function thing and use require(“connect.php”). I have to make $dbh a global variable though so it can be used to prepare SQL statements within functions, right?
Maybe that should have been obvious to me, but thank you. Is there a way of keeping a constant connection open while user visits different pages of the website? I guess the problem here is that when the user leaves the website, the connection would have to time out right? I would think if the connection is re-used from script to script, it would improve performance of the web application?
A global variable is one that is declared at the top level, that is, outside any classes or functions. This doesn’t mean it’s available globally (like super globals), just that it is in the global namespace. A function doesn’t inherit this namespace, if it did you would have to have unique variable names for pretty much everything. You can import a global variable into the function with the keyword global, but using globals like this is frowned upon, rather pass it in: function($var);
Is there a way of keeping a constant connection open
Short answer no, long answer yes BUT. (Actually I think it might be impossible with php??). At any rate, if you needed persistent connections then you’d know why and how to do it.
Put simply, because scope is a good thing, using globals breaks that. You seem keen to learn, perhaps you should head to the library and check out some books on programming/OOP. E.g. what you wanted to do earlier has a very common solution in the singleton: check if something exists, if not create it, make sure no more than one ever exist.
Any PHP books you guys recommend? I have the 3rd edition of Kevin Yanks’s Sitepoint book, “Database Driven Website Using PHP & MySQL”. I really like his approach, but I know the 3rd edition is getting a bit outdatted now (for example, he talks about a few features that have been deprecated like magic quotes and eregi_replace). I haven’t finished the book though as I am not very good at getting through manuals. I learn by doing (and unfortunately I learn better by making mistakes). Maybe I should take a course.
What would be the best way to check if the PDO connection exists? Can you give me a code example?