nutballs

So I am having a problem with the code below. It works fine from one part of my application but not from this part.
The DoesItemExist function is causing this error:
Warning: mysql_query(): 4 is not a valid MySQL-Link resource in somepagename.

php

  on line 63

the only difference between how this function is called when there is an error and not, is this call is made from within a loop of an existing database connection recordset. $db1

So I am guessing there is some kind of crazy ass interplay between connection references. But i thought vars had only LOCAL scope in

PHP

 , not trickle up or down.

So execution is:
preprocess
open DB connection as $db1 using OpenDB1()
load recordset as $rs using Mysql_query()
loop through recordset as $row
  Call function DoesItemExist()
      Open DB connection as $db1 using OpenDB1()
      Mysql_query to test if record exists
      closeDB
      return true or false
Loop end
Close $db1


MAIN function

function preprocess()
{
if (!empty($_POST['tod']) && $_SESSION['UserID']!='')
{
//the page has been submitted with a new item.
$isvalid = validateform();
if ($isvalid)
{
$db1=OpenDB1();
$sql="SELECT * from db1_suggestions where SuggestionGroup='".SQLsafe('string',$_POST['group'])."'";
$rs = mysql_query($sql, $db1);
while ($row = mysql_fetch_assoc($rs))
{
$isvalid = DoesItemExist($_SESSION['UserID'],$row['SuggestionNote']); //THIS IS THE PROBLEM
if ($isvalid)
{
                                      //do some stuff
}
}
CloseDB($db1);
}
}
}


The function that is causing the problem

function DoesItemExist($userID,$note)
{
$db1=OpenDB1();
$sql="select * from manydo_items where ItemDeleted=0 and fk_userID=".SQLsafe('integer',$userID)." and ItemNote='".SQLsafe('string',$note)."'";
$result = mysql_query($sql, $db1);
if (mysql_numrows($result)!=0)
{
AddMsg('ERROR: You already have an Item with the Note "'.$note.'", you will need to make the Note unique.');
$retval=false;
}
else
{
$retval=true;
}
CloseDB($db1);
return $retval;
}


Database Wrappers.

define('DB1SQLSERVER','someserver

.net

 );
define('DB1SQLUSERNAME','someuser');
define('DB1SQLPASSWORD','password');
define('DB1SQLDATABASE','somedatabase');

function OpenDB1()
{
return OpenDB(DB1SQLSERVER,DB1SQLDATABASE,DB1SQLUSERNAME,DB1SQLPASSWORD);
}

function OpenDB($server,$database,$username,$password)
{
    $db = mysql_connect( $server, $username, $password) or die('Could not connect to database server.' );
    mysql_select_db($database, $db) or die('Could not select database.');
    return $db;
}

function CloseDB($db) //pass in a database object
{
    if( $db != false )
        mysql_close($db);
    $db = false;
}

nutballs

also, to note what I have already tried.

passing a hardcoded value to the DoesItemExist function
changing the variable name of the database connection object inside DoesItemExist
commenting out the call to DoesItemExist, proves the error is in that function call.

What i think is happening is that DoesItemExist is closing not just the connection within its own scope, but also closing the connection in the parent function as well. and thats why i get the error.

perkiset

Alright: As per our phone convo, here's what needs to be done:

Since

PHP

 5 you don't *require* the ampersands, but you still would like to infer that you are passing references...
Additionally, you need to *force*

PHP

  to open a brand new connection on the connect because the way you have it,

PHP

  will
try to be smart and retask the connection for you and boom.

So: it's all here:
function &OpenDB1()
{
return OpenDB(DB1SQLSERVER,DB1SQLDATABASE,DB1SQLUSERNAME,DB1SQLPASSWORD);
}

function &OpenDB($server,$database,$username,$password)
{
    $db = mysql_connect( $server, $username, $password, true) or die('Could not connect to database server.' );
    mysql_select_db($database, $db) or die('Could not select database.');
    return $db;
}

function CloseDB(&$db) //pass in a database object
{
    if( $db != false )
        mysql_close($db);
    $db = false;
}

That being said, it's good that you're going to rethink your strategy since this code will just thrash the fish out of your TCP stack as you connect and disconnect from it.

Good luck!
/p

nutballs

i actually opted for a minor change. I implemented the & like you suggested, and the TRUE addition to the connection, is nice, but. It promotes lazy coding i think. Since I really on want 1 connection at any given time to be open, I am forgoing the TRUE setting.

I also changed the DoesItemExist to my normal methodology, which i would have eventually anyway, which is to pass in the database connection object, which already exists. Normally I just barrel forward, then refine, but I guess with

PHP

  i am going to have to make a few adjustments to my methods.

thanks for the help


Perkiset's Place Home   Politics @ Perkiset's