[Resolved] PEAR DB Problem

[Reformed]

I'm usign PEAR DB and so far I have been impressed with the ease of use, but I have run into a problem that I can't figure out.

I'm attempting multiple queries in one script and one query depends on the other in an if statement. The first query is excecuted with no problems, but the second one is skipped over completely.

I know my query is good and if I put typos in the second query (the one that won't work) I dont' even get an error.

Is it not possible to perform 2 queries in one script with DB? I would gladdly post my code if someone thinks they can help me.

[YappyDog]

Post your code...............

[Reformed]

Here is my code. Notice that I'm attempting to pass the variable $db2 by reference. I also tried instantiating DB with a new reference by the $db2 variable and had the same problem

//this is the user login page for main entrance

//include necessary files
require("lib/SmartWind.php");
require("lib/DB.php");
require("lib/FormClass.php");
require("lib/dsn.php");

//validate the incoming field values
if(isset($_POST['submit'])) {
$fv = new FormValidator();

$fv->isEmpty("userName", "Enter a username");
$fv->isEmpty("userPass", "Enter a password");
$fv->isNotSymbol("userName", "Some values entered are forbidden");
$fv->isNotSymbol("userPass", "Some values entered are forbidden");

	//Check to see if there are errors.
	if($fv->isError()) {
		//if errors exist, populate the array
		$errors =  $fv->getErrorList();
		//register the error session
		$_SESSION['VAL_ERROR'] = $errors;
		header("location:login.php");
		exit();

	} else {

		//clean up the variables
		$userName = trim(htmlspecialchars($_POST['userName']));
		$userPass = trim(htmlspecialchars($_POST['userPass']));

	//connect to the database
	$db = DB::connect($dsn);
	if(DB::isError($db)) {
		die ($db->getMessage());
	}

	//copy the connection by reference for later use
	$db2 =& $db;

	//check the result
	$result = $db->query("SELECT user_id, username, password, AccountName, security_level, security_desc FROM user_login, lu_user_security WHERE user_login.security_level = lu_user_security.security_id AND user_login.username = '$userName' AND user_login.password = '$userPass'");
	if(DB::isError($db)) {
		die ($result->getMessage());
	}

		if($result->numRows() == 1) {
			//get the result set
			 $row = $result->fetchRow(DB_FETCHMODE_ASSOC);

			 //create a session for valid user
			 if(!isset($_SESSION['USER_NAME'])) {

			  $_SESSION['WINDSTORM'] = 'WINDSTORM_USER12f8jGlKQa7bi2LcX4zDi';
			  $_SESSION['USER_NAME'] = $row['AccountName'];
			  $_SESSION['SEC_DESC'] = $row['security_desc'];
			  $_SESSION['SEC_LEVEL'] = $row['security_level'];
			  $_SESSION['USER_ID'] = $row['user_id'];


			  //update the database to show the login date and time
			  $update = $db2->query("UPDATE user_login SET visit_count = visit_count + 1, last_logged_on = null WHERE username = '$userName'");
			  if(DB::isError($db2)) {
					die ($db2->getMessage());
				}

			 }

			 //send the valid user to the default page, free results and disconnect
			 $result->free();
			 $update->free();
			 $result->disconnect();
			 $update->disconnect();
			 header("location:default.php");
			 exit();

		} else {

			//regisiter a login error function
			$_SESSION['LOGIN_ERROR'] = "LOGIN_ERROR";

			//user login failed 
			//call smarty and display the form with an error message
			$smarty = new SmartWind();

			//register error sessions and assign them to smarty
			$smarty->assign("LOGIN_ERROR", $_SESSION['LOGIN_ERROR']);
			$smarty->assign("VAL_ERROR", $_SESSION['VAL_ERROR']);


			//display the page
			$smarty->display('main_nav.tpl');
			$smarty->display('login_form.tpl');
			$smarty->display('footer.tpl');

		} 
	}

} else {

//call smarty and display the form
$smarty = new SmartWind();

//display the page
$smarty->display('main_nav.tpl');
$smarty->display('login_form.tpl');
$smarty->display('footer.tpl');
}

?>

The problem is with the second db call. It is completely ignored.

[YappyDog]

Instead of trying to copy the connection, I'd just create a new one.

//connect to the database again
$db2 = DB::connect($dsn);

Then, on your disconnects, I think you need to use the connection not the results. So.......

$result->disconnect();
$update->disconnect();

would be...........

$db->disconnect();
$db2->disconnect();

[Reformed]

Thanks for the tip on the disconnects. I was un-clear about that. As for the copying of the connection, it seems that my problem still remains even with a new istance of db, but I'll switch it back to play it safe.

[YappyDog]

Are you sure you are even getting to the update part of your code? Looks like if you don't have a USER_NAME session variable, you would skip it.

//create a session for valid user
if(!isset($_SESSION['USER_NAME'])) {

[Reformed]

Well, the idea is that if no username session exists, the session variables are registered and the query is called to up the visit count and record the date of the visit. So it's if no username session exists the query is called, unless I'm totally spacing it (which is very possible). 😃

[YappyDog]

Sorry, my bad. I missed the NOT portion of the if statement.

So then, what happens if you DO have a session variable?

Maybe you should echo something out after the if statement just to make sure you are getting there.

//create a session for valid user
if(!isset($_SESSION['USER_NAME'])) {
echo "meaningful debug information";

[Reformed]

Good idea. I also noticed that switching the disconnection variables results in an error. Now I can't get either one to work. In all of the examples I have seen the disconnection looks like this:

DB::disconnect();

I have only recieved errors when using this and so I'm at a loss for how to properly dissconnect. This PEAR stuff is great when it works, but it really takes some time to learn.

[Reformed]

Well, my original problem is solved. I guess making a reference to the existing db object was the cause. Now if I could just figure out how to free results and disconnect properly.

[YappyDog]

This is code that I've used that works just fine.......

$conn2 = DB::connect($DSN);
if(!$conn2) {
echo "<li>Unable to open datasource.";
} else if(DB::isError($conn2)) {
echo "<li>Unable to open datasource. " . $conn2->getMessage();
} else {
$conn2->setFetchMode(DB_FETCHMODE_ASSOC);

// do stuff that uses the connection and fills $rs..........

$rs->free();
$conn2->disconnect();
}

[Reformed]

Cool, I figured a few things out. First, my copy of the db object was not the problem, it works now. I think your suggestion to put in an else statement if a user is registered was the key. For some reason I never suspected that.

I think in order to disconnect from DB you have to first free the result sets and then call the diconnect method. Here is the code now that everything is working:

//this is the user login page for main entrance

//include necessary files
require("lib/SmartWind.php");
require("lib/DB.php");
require("lib/FormClass.php");
require("lib/dsn.php");

//validate the incoming field values
if(isset($_POST['submit'])) {
$fv = new FormValidator();

$fv->isEmpty("userName", "Enter a username");
$fv->isEmpty("userPass", "Enter a password");
$fv->isNotSymbol("userName", "Some values entered are forbidden");
$fv->isNotSymbol("userPass", "Some values entered are forbidden");

	//Check to see if there are errors.
	if($fv->isError()) {
		//if errors exist, populate the array
		$errors =  $fv->getErrorList();
		//register the error session
		$_SESSION['VAL_ERROR'] = $errors;
		header("location:login.php");
		exit();

	} else {

		//clean up the variables
		$userName = trim(htmlspecialchars($_POST['userName']));
		$userPass = trim(htmlspecialchars($_POST['userPass']));

	//connect to the database
	$db = DB::connect($dsn);
	if(DB::isError($db)) {
		die ($db->getMessage());
	}

	//copy the connection
	$db2 =& $db;

	//check the result
	$result = $db->query("SELECT user_id, username, password, AccountName, security_level, security_desc FROM user_login, lu_user_security WHERE user_login.security_level = lu_user_security.security_id AND user_login.username = '$userName' AND user_login.password = '$userPass'");
	if(DB::isError($db)) {
		die ($result->getMessage());
	}

		if($result->numRows() == 1) {
			//get the result set
			 $row = $result->fetchRow(DB_FETCHMODE_ASSOC);

			 //create a session for valid user
			 if(!isset($_SESSION['USER_NAME'])) {

			  $_SESSION['WINDSTORM'] = 'WINDSTORM_USER12f8jGlKQa7bi2LcX4zDi';
			  $_SESSION['USER_NAME'] = $row['AccountName'];
			  $_SESSION['SEC_DESC'] = $row['security_desc'];
			  $_SESSION['SEC_LEVEL'] = $row['security_level'];
			  $_SESSION['USER_ID'] = $row['user_id'];

			  //update the database to show the login date and time
			  $update = $db2->query("UPDATE user_login SET visit_count = visit_count + 1, last_logged_on = null WHERE username = '$userName'");
			  if(DB::isError($db2)) {
					die ($db2->getMessage());
				}

			 } else {

			 	header("location:default.php");
			 	$result->free();
			 	$update->free();
			 	$db->disconnect();
			 	$db2->disconnect();
			 	exit();
			 }

			 //send the valid user to the default page, free results and disconnect
			 header("location:default.php");
			 $result->free();
			 $update->free();
			 $db->disconnect();
			 $db2->disconnect();
			 exit();

		} else {

			//regisiter a login error function
			$_SESSION['LOGIN_ERROR'] = "LOGIN_ERROR";

			//user login failed 
			//call smarty and display the form with an error message
			$smarty = new SmartWind();

			//register error sessions and assign them to smarty
			$smarty->assign("LOGIN_ERROR", $_SESSION['LOGIN_ERROR']);
			$smarty->assign("VAL_ERROR", $_SESSION['VAL_ERROR']);


			//display the page
			$smarty->display('main_nav.tpl');
			$smarty->display('login_form.tpl');
			$smarty->display('footer.tpl');

		} 
	}

} else {

//call smarty and display the form
$smarty = new SmartWind();

//display the page
$smarty->display('main_nav.tpl');
$smarty->display('login_form.tpl');
$smarty->display('footer.tpl');
}

?>

[toma42]

Rather than use if (DB::isError($db)) just use the built-in error handling of pear. That way you can have different error messages for development vs deployment installations.

Also, you only ever need one connection to the database and using two is just wasting resources (you're simply creating a pointed to $db in $db2 so it's not a big deal in this case but it is more confusing code).

Next, don't use
$row = $result->fetchRow(DB_FETCHMODE_ASSOC);
Instead, use this:
$row = $db->getRow($sql);

and set the fetchmode in the global scirpt you use to instantiate the $db object.

See my article here for more details:
http://smarty.incutio.com/?page=SmartestSmartyPractices

[toma42]

Oh, concider changing this:
//display the page
$smarty->display('main_nav.tpl');
$smarty->display('login_form.tpl');
$smarty->display('footer.tpl');

Smarty can do it in login_form.tpl instead as:

{include file="main_nav.tpl"}
body of login_form here
{include file="footer.tpl"}

then your code is simply
$smarty->display('login_form.tpl');

[Reformed]

Thanks for the tip. That'll save me some lines of code and confusion.

[minorgod]

I also wanted to point out that you can pretty much forget about doing a $dbh->disconnect() in PEAR if you want to. Your connection will close automatically when your script finishes executing anyway, and I've run into many problems that turned out to be caused by calling a disconnect() too soon in my script. If you use the following PEAR functions, disconnects are handled automatically:

$dbh->getOne()
$dbh->getRow()
$dbh->getCol()

I almost never need anything other than getRow and getOne in my scripts. Of course, you'll still occasionally have to use $dbh->query(), but I never bother calling a disconnect() anymore because it never seemed to help anything and sometimes hurt things.