Reusing SQL connections

[mazdaboi]

I've got a small question... I'm writing a new application, and in it I have to do multiple queries on different tables... Is there any way to reuse a previously used database connection? I've tried using mysql_connect and mysql_pconnect, but every time I perform the second query, it acts as if the connection was closed after the first query.

I've just got done porting most of my app over to using ADODB, but am having the same problem with it... It's like a connection to the MySQL server closes after the first query results are returned...

Am I just missing something, or shouldnt the connection be valid for the duration of the script execution?

Thanks,
Brad

[stolzyboy]

as long as you use mysql_connect() at the top of the page you can use however many queries you want, just as long as you aren't using mysql_close() after that and before any other queries

[mazdaboi]

Ok, that's what I thought... I'll have to drill down in my code and see why its not working then...

Thanks for the reply!

[stolzyboy]

are you repeating query variables like

$query = ""; //whatever sql
$result = msyql_query($query);

while ($row = mysql_fetch_array($result))
{
echo $row['fieldname'];
}

then further down

$query = ""; another sql
$result = msyql_query($query);

while ($row = mysql_fetch_array($result))
{
echo $row['fieldname'];
}

sometimes that will cause you some problems, if so, choose different variable names for those

$query1 = "";
$result1 = msyql_query($query1);

while ($row1 = mysql_fetch_array($result1))
{
echo $row1['fieldname'];
}

$query2 = "";
$result2 = msyql_query($query2);

while ($row2 = mysql_fetch_array($result2))
{
echo $row2['fieldname'];
}

i have ran into this a couple times, as have others, so thought it may relate to you as well, good luck

[mazdaboi]

Hmmm. Now that you mention it, I have ran into that in the past also...

Unfortunantly, that didnt seem to help in this case...

The error I get is:

Fatal error: Call to a member function on a non-object in /home/admin/public_html/conner/radius.php on line 51

The code that relates to this problem is:

Connect to the db:$conner_connection = NewADOConnection("mysql");

$conner_connection->PConnect($conner_host, $conner_user, $conner_pass, $conner_db) or die("Unable to connect!");

Skip over some non-important unrelated stuff

if ( empty($minorfunction) ) {

    # Populate the Account Association array
    $query = "SELECT id, CONCAT(name_last, ', ', name_first) from user_data ORDER BY name_last";
    $result = $conner_connection->Execute($query) or die("Error in query: $query. " . $db->ErrorMsg());

    while (!$result->EOF) {
        $profile_ids[] = $result->fields[0];
        $profile_names[] = $result->fields[1];
        $result->MoveNext();
    }

    $smarty->assign('profile_ids', $profile_ids);
    $smarty->assign('profile_names', $profile_names);

    # Populate the RADIUS group array
    $query2 = "SELECT DISTINCT GroupName from radgroupreply";

    ### The line right below this is the aformentioned line 51
    $result2 = $conner_connection->Execute($query2) or die("Error in query: $query2. " . $db->ErrorMsg());

    while (!$result2->EOF) {
        $group_names[] = $result2->fields[0];
        $result2->MoveNext();
    }

    $smarty->assign('group_names', $group_names);

    # Display the page
    $smarty->display('header.tpl');
    $smarty->display('radius/add.tpl');
    $smarty->display('footer.tpl');

} elseif ( $minorfunction == "submit" ) {   
    // blah blah
}

Since the line involved is trying to call the Execute method on the $conner_connection variable, and with the error message given, it leads me to believe that the DB connection is being closed, and therefore the $coner_connection variable destroyed...

The first query works fine. The second one fails...

Any thoughts?

Thanks,
Brad

[mazdaboi]

Oh yeah, the PConnect on the line was one of my experiemnts to make it work. I normally just use the Connect method for the DB connection.