Page 1 of 1

[SOLVED] MySQL DB Function in Include Not Returning Results

Posted: Thu Aug 12, 2004 4:25 am
by rgroom
I'm trying to move my database query code (currently at each place I call the database) out to a common function in an include file which is loaded at the top of every PHP page. The function contained in the include is simple as follows:

Code: Select all

<?php
function dbquery ($query)
{
    @$db = mysql_pconnect('localhost','root','password');
    if (!$db)
    {
       echo 'Connection failed.';
       exit;
    }
    
    mysql_select_db('dbname');
    $result = mysql_query($query);
    return $result;
}
?>
However, when it is called from code coming after the include in the main page:

Code: Select all

<?php
dbquery ('SELECT countryid, countryname FROM country;');
for($i = 0; $i < mysql_num_rows($result); $i++)
{
    echo mysql_result($result, $i, "countryid");
}
?>

I get the following errors:

Notice: Undefined variable: result in ...(page)

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in ... (page)



All other variables appear to be available between the include and the main page, just not results from the database.

I've tried both with, and without the 'return $result;' entry in the function, but neither works.

Can anyone help me out? Is this a scope issue, or something peculiar to database recordsets?

I figure this must be a common enough thing to try to do, but can't find reference to it anywhere on the web.

Many thanks in advance.

Rob.

Posted: Thu Aug 12, 2004 5:01 am
by markl999
dbquery() returns $result, so you need to assign the return value:
$result = dbquery ('SELECT countryid, countryname FROM country;');

There's not much advantage in putting your query inside a function like this anyway, imho.

Posted: Thu Aug 12, 2004 5:07 am
by rgroom
Thanks Mark, you 'da man.

Stupid really. I don't know why I've not run into that problem before...

The reason for separating it out is just to un-clutter my code and keep the global variables and database failure errorhandling routine all in one place, in the pageheader include I start every page with.

Thanks again,

Rob.

Posted: Thu Aug 12, 2004 5:49 am
by CoderGoblin
I would actually make it into a class. The class opens a connection and keeps it open throughout your code. Plenty of MySQL class examples on the web. All you do once it is open and linked to the correct database is to send queries to it. Methos included in the class should contain things like fetch_assoc and num_rows.

I find this methodology really useful as it keeps all your database queries separate from your main code. Also if at some stage you change database types (MySQL to POSTGRES for example) you just need to change the class, not your code.