[PHP] Re: Reusing MySQL Connections - Can it be done?
http://pear.php.net/package/db
Monty [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
I am trying to use fewer resources and processes when making database
connections in my scripts, so, upon the first call to make a DB
connection,
I store the Link Resource ID in a constant variable that can be accessed
by
all other functions that need a DB connection, like this...
function connect_db() {
if (!defined('_CONNECT_DB')) {
$result = @mysql_pconnect(localhost, Username, Password);
@mysql_select_db(database_name);
define('_CONNECT_DB', $result);
}
return _CONNECT_DB;
I call this function this way...
$query = SELECT * FROM table;
$connid = connect_db();
$result = mysql_query($query, $connid);
What's odd is that this works for the first query, but after that the
Resource Link ID stored in _CONNECT_DB ceases to work, making MySQL issue
the error: mysql_query(): 4 is not a valid MySQL-Link resource...
Is there any reason why this isn't working? Normally I store the Resource
Link ID in a local variable that I can re-use with no problem, so, I can't
understand why storing the Resource Link ID in a constant var won't work?
Is
it not possible to re-use the same connection for multiple queries to
different tables in the DB? I know I can do this if the Resource Link ID
is
assigned to a local var, so, maybe it can't be stored in a constant var??
Only other solution I can think of is to use non-persistent connections
and
simply make a connection to the DB then immediately disconnect for each
function that needs DB access, but, I'm wondering if this will create more
overhead and overload the database with too many connections?
I've searched and read just about everything I could find about exactly
how
PHP and MySQL work together, but, if there's anything that explains in
detail exactly what is happening when you call mysql_connect or
mysql_query,
please point me in its direction.
Any input on this is most appreciated!
Monty
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Reusing MySQL Connections - Can it be done?
[...] I tried this, but, same results. If I store the Resource ID from a mysql_pconnect() in $GLOBALS['_CONNECT_DB'] and then call... mysql_query($query, $GLOBALS['_CONNECT_DB']); [...] How are you setting the global? [...] I guess he said he is using $GLOBALS['_CONNECT_DB'] :-p -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Reusing MySQL Connections - Can it be done?
This is weird, it works for me, I use Oracle though.
The real application uses ADODB but it should work with the PHP native
resources.
I do this:
1. Define a singleton function, for example: getDBConnection()
function getDBConnection() {
$db = GLOBALS['APP_DB_CONNECTION'];
if (!is_object($db)) {
// Here we make the connection
$db = WHAT_EVER_CONNECTION_FUNCTION_YOU_WANT();
// At this point $db is a valid connection object
GLOBALS['APP_DB_CONNECTION'] = $db;
}
return $db
}
2. From every function that connects to the database you just call the
singleton:
function getActiveCustomers() {
$db = getDBConnection();
$sql = SELECT .;
// Here you execute the sql like you normally would
return $rs; // This is the resultset
}
Note that in step 1 you can use the resource variable returned by native
PHP functions, in my case I use ADODB so I store the object which isn't
that different because inside that object resides the PHP resource. So
you can change the line 'if (!is_object($db))' for
'if(!is_resource($db))'.
If you look the singleton carefully you'll see that the connection
process is executed just the first time. For example, if a user
requests executes 10 queries to the database, the connection will be
actually made in the first call to the singleton. The successive calls
will return the connection stored in the global scope.
I can tell for sure that this approach works very well. If it doesn't
for you, there might be a more fundamental problem in your configuration
or in your code.
Regards,
-William
El lun, 05-04-2004 a las 17:18, Monty escribió:
A define is pretty much for strings only, not objects or resources. Try
using $GLOBALS['_CONNECT_DB'] instead.
I tried this, but, same results. If I store the Resource ID from a
mysql_pconnect() in $GLOBALS['_CONNECT_DB'] and then call...
mysql_query($query, $GLOBALS['_CONNECT_DB']);
PHP gives me the following error:
4 is not a valid MySQL-Link resource
When I look into this var, here's what I see:
Resource id #4
This is the same that was stored in the constant var, so, it appears that
whether it's a Global or Constant isn't making a difference.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Reusing MySQL Connections - Can it be done?
Red Wingate wrote: [...] I tried this, but, same results. If I store the Resource ID from a mysql_pconnect() in $GLOBALS['_CONNECT_DB'] and then call... mysql_query($query, $GLOBALS['_CONNECT_DB']); [...] How are you setting the global? [...] I guess he said he is using $GLOBALS['_CONNECT_DB'] :-p I was asking for the exact line of code... -- paperCrane Justin Patrin -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Reusing MySQL Connections - Can it be done?
Monty wrote:
I am trying to use fewer resources and processes when making database
connections in my scripts, so, upon the first call to make a DB connection,
I store the Link Resource ID in a constant variable that can be accessed by
all other functions that need a DB connection, like this...
function connect_db() {
if (!defined('_CONNECT_DB')) {
$result = @mysql_pconnect(localhost, Username, Password);
@mysql_select_db(database_name);
define('_CONNECT_DB', $result);
}
return _CONNECT_DB;
I call this function this way...
$query = SELECT * FROM table;
$connid = connect_db();
$result = mysql_query($query, $connid);
What's odd is that this works for the first query, but after that the
Resource Link ID stored in _CONNECT_DB ceases to work, making MySQL issue
the error: mysql_query(): 4 is not a valid MySQL-Link resource...
Is there any reason why this isn't working? Normally I store the Resource
Link ID in a local variable that I can re-use with no problem, so, I can't
understand why storing the Resource Link ID in a constant var won't work? Is
it not possible to re-use the same connection for multiple queries to
different tables in the DB? I know I can do this if the Resource Link ID is
assigned to a local var, so, maybe it can't be stored in a constant var??
Only other solution I can think of is to use non-persistent connections and
simply make a connection to the DB then immediately disconnect for each
function that needs DB access, but, I'm wondering if this will create more
overhead and overload the database with too many connections?
I've searched and read just about everything I could find about exactly how
PHP and MySQL work together, but, if there's anything that explains in
detail exactly what is happening when you call mysql_connect or mysql_query,
please point me in its direction.
Any input on this is most appreciated!
Monty
A define is pretty much for strings only, not objects or resources. Try
using $GLOBALS['_CONNECT_DB'] instead.
--
paperCrane Justin Patrin
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Reusing MySQL Connections - Can it be done?
A define is pretty much for strings only, not objects or resources. Try using $GLOBALS['_CONNECT_DB'] instead. I tried this, but, same results. If I store the Resource ID from a mysql_pconnect() in $GLOBALS['_CONNECT_DB'] and then call... mysql_query($query, $GLOBALS['_CONNECT_DB']); PHP gives me the following error: 4 is not a valid MySQL-Link resource When I look into this var, here's what I see: Resource id #4 This is the same that was stored in the constant var, so, it appears that whether it's a Global or Constant isn't making a difference. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Reusing MySQL Connections - Can it be done?
Monty wrote: A define is pretty much for strings only, not objects or resources. Try using $GLOBALS['_CONNECT_DB'] instead. I tried this, but, same results. If I store the Resource ID from a mysql_pconnect() in $GLOBALS['_CONNECT_DB'] and then call... mysql_query($query, $GLOBALS['_CONNECT_DB']); PHP gives me the following error: 4 is not a valid MySQL-Link resource When I look into this var, here's what I see: Resource id #4 This is the same that was stored in the constant var, so, it appears that whether it's a Global or Constant isn't making a difference. How are you setting the global? -- paperCrane Justin Patrin -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
