MySQL DB Function in Include Not Returning Results

[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:

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:


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.

 

[cLFlaVA]

You're not storing the returned value in any variable. Although you're setting $result in the dbquery() function, it is local only to that function.

Try this:

[tt]$result = dbquery ('SELECT countryid, countryname FROM country;');
for($i = 0; $i < mysql_num_rows($result); $i++)
{
echo mysql_result($result, $i, "countryid");
}[/tt]

As a side note, rather than this for statement, you may want to try a while:

[tt]
$result = dbquery ('SELECT countryid, countryname FROM country;');
while($row = mysql_fetch_array($result)) {
echo $row['country_id'] . '<br>';
}[/tt]

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[sleipnir214]

I see your script invoke dbquery(), which returns a value. However, your invocation of the function does not store the return value.

Your code then tries to reference $result, which was a variable local to the function.

Try:

[red]$result = [/red]dbquery ('SELECT countryid, countryname FROM country;');


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[DRJ478]

Another suggestion:
Instead of using the @ operator to suppress error messages you can handle the failure within the line using a construct like

$db = mysql_connect($host,$user,$name) OR die("Could not connect to $host: ".mysql_error());
# Another one:
mysql_select_db($dbname) OR die("Could not select $dbname: ".mysql_error());

Also, it is highly recommended to use a different MySQL user than root. Create a web user that has limited privileges, it helps contain possible security issues.