Wrong DB Connection

[krotkruton]

I belive this is a php problem and not a database problem.

When I try to connect to my MySQL with one of my php pages, it connects to the wrong database, and I really can't figure out why.

<?php require_once('../Connections/connBarHopper.php');

include "level1_check.php";
include "search.php";
checklogin ();

mysql_select_db($database_connBarHopper, $connBarHopper);

if (isset($_POST["entry"])) {
	$keywords = $_POST["entry"];
	}
else {
	$keywords = $_GET["query"];
	}

$search_args = array(
	"table"=>"userprofiles",
	"return_fields"=>"email, first_name, last_name, home_town, age, state, high_school, college, major",
	"search_fields"=>"email, first_name, last_name, home_town, age, state, high_school, college, major",
	"sort_by"=>"last_name");

$search = new MysqlSearch($search_args);
$search_results = $search->find($keywords); 

?>

Now the thing is, when this page loads and I try to display the search results, it displays an error that says "Table 'dbuserdb.userprofiles' doesn't exist" which I'm pretty sure is an error displayed by the search... only thing is, $database_connBarHopper clearly has 'testBarHopper' as the database and that is the only database connection made because the search.php file that conducts the search does not connect to a database itself... dbuserdb exists as a database, but it is completely unrelated to this page...

Not sure if all that makes sense, but it seems like the site is just skipping the database im telling it to go to and then goes to the first database in my list, which is dbuserdb... I use this same code in multiple other sections of my site and everything works fine, but for some reason it doesn't here.

I know this wasn't really clear, so I guess I'm just wondering if anyone else has ever heard of this happening. Here's the search.php file:

<?
/*

MysqlSearch.class.php

Usage:
1. Instansiate class with arguments:
$search_args = array(
					"table"=>"tblArticles",
					"return_fields"=>"ArticleId, Title, Subject, Content",
					"search_fields"=>"ArticleId, Title, Subject, Content",
					"joins=>array(
									array("type"=>"INNER",
										"source_table"=>"tblArticles",
								 		"destination_table"=>"tblCategories",
								 		"source_field"=>"CategoryId",
								 		"destination_field"=>"CategoryId"
									)
								 ),
					"sort_by"=>"Title"
					);
$search = new MysqlSearch($search_args);

2. Use the find method - this will return a result set of the specified fields:
$search_results = $search->find("a");

3. Loop the results and output, checking for errors:
if($search_results)
{
	foreach($search_results as $result)
	{
		print $result["Title"];
		print "<br/>";
	}
}
elseif($search->errors)
{
	foreach($search->errors as $error)
	{
		print "$error <br/>";
	}
}
else
{
	print "No results";
}

Notes:
	o For relevancy calculations to be correct, search_fields must be the
	  same as return_fields
*/
class MysqlSearch
{
	var $args;
	var $errors;

function MysqlSearch($args)
{
	// remove illegal characters
	$args = $this->clean_var($args);

	$this->args = $args;
	$this->errors = array();
}

function clean_var($var)
{
	if(is_array($var))
	{
		foreach($var as $key=>$value)
		{
			$var[$key] = $this->clean_var($value);
		}
	}
	else
	{
		$var = preg_replace("/;|'|\\\/","",$var);
	}
	return $var;
}

function find($search_term)
{
	global $p_db, $p_site;

	// get args
	$table = $this->args["table"];
	$joins = $this->args["joins"];
	$return_fields = $this->args["return_fields"];
	$search_fields = $this->args["search_fields"];

	$sql = "SELECT {$return_fields} ".
			"FROM {$table} ";

	if($joins)
	{
		foreach($joins as $join)
		{
			$destination_table = $join["destination_table"];
			$source_table = $join["source_table"];
			$source_field = $join["source_field"];
			$destination_field = $join["destination_field"];
			$join_type = $join["type"];

			if(!$join_type)
			{
				$join_type = "INNER";
			}

			$sql .= "{$join_type} JOIN {$join_table} ".
					"ON {$destination_table}.{$destination_field} = {$source_table}.{$source_field} ";
		}
	}

	if($search_term)
	{
		$search_term = $this->clean_var($search_term);
		$search_term = explode(" ",$search_term);
		$search_fields = explode(",",$search_fields);

		$sql .= "WHERE ";

		for($ti=0;$ti<sizeof($search_term);$ti++)
		{
			$keyword = $search_term[$ti];
			$last_keyword = $search_term[$ti-1];
			$next_keyword = $search_term[$ti+1];

			if(preg_match("/^[A-Za-z0-9]+$/",$keyword))
			{
				// was the last keyword an operator
				if($this->is_operator($last_keyword))
				{
					$sql .= strtoupper($last_keyword)." ";
				}

				// continue if this keyword is an operator
				if($this->is_operator($keyword))
				{
					continue;
				}
				else
				{
					$sql .= "(";
					for($fi=0;$fi<sizeof($search_fields);$fi++)
					{
						$field = $search_fields[$fi];

						$sql .= "$field LIKE '%{$keyword}%' ";

						if($fi != sizeof($search_fields)-1)
						{
							$sql .= "OR ";
						}
					}
					$sql .= ") ";
				}

				if(!$this->is_operator($keyword) && !$this->is_operator($next_keyword) && $next_keyword)					
				{
					$sql .= "AND ";
				}
			}
		}

		if($this->args["constraints"])
		{
			foreach($this->args["constraints"] as $field=>$constraint)
			{
				$sql .= "AND $field = '$constraint' ";
			}
		}

		// carry out the query
		$res = mysql_query($sql);

		if($res)
		{
			$result_set = array();

			while($row = mysql_fetch_assoc($res))
			{
				// get keyword score
				$keyword_score = 0;
				foreach($search_term as $keyword)
				{
					if($keyword)
					{
						if(!$this->is_operator($keyword))
						{
							foreach($row as $key=>$value)
							{
								$keyword_score = preg_match_all("/$keyword/i", $value, $null);
							}
						}
					}
				}

				$row["relevancy"] = $keyword_score;
				array_push($result_set, $row);
			}

			usort($result_set, array($this, "order_result_set"));

			return $result_set;
		}
		else
		{
			// error
			array_push($this->errors, mysql_error());
		}
	}
}

function is_operator($var)
{
	$var = strtoupper($var);

	switch($var)
	{
		case "AND":
			$return = 1;
			break;
		case "OR":
			$return = 1;
			break;
		default:
			$return = 0;
			break;
	}

	return $return;
}

function order_result_set($a, $b)
{
	if(!$this->args["sort_by"])
	{
		$sort_by = "relevancy";
	}
	else
	{
		$sort_by = $this->args["sort_by"];
	}

	if($a[$sort_by] && $b[$sort_by])
	{
		$ax = $a[$sort_by];
		$bx = $b[$sort_by];
	}

	if ($ax == $bx){ return 0; }
	return ($ax > $bx) ? -1 : 1;
}
}
?>

[kwl33181]

Please post teh connBarHopper.php, so I can see how it is set up and see if the problem is in there. All the code you posted looks fine so it must be a problem witht he connBarHopper.php page.

[krotkruton]

connBarHopper is a file created by Dreamweaver. After changing my password and username, here's the file:

<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_connBarHopper = "localhost";
$database_connBarHopper = "testbarhopper";
$username_connBarHopper = "myusername";
$password_connBarHopper = "mypassword";
$connBarHopper = mysql_pconnect($hostname_connBarHopper, $username_connBarHopper, $password_connBarHopper) or die(mysql_error());
?>

This whole thing is a little mind-boggling to me because I can't figure out where this code could ever get the database "dbuserdb".

[Azala]

<?php 
# FileName="Connection_php_mysql.htm" 
# Type="MYSQL" 
# HTTP="true" 
$hostname_connBarHopper = "localhost"; 
$database_connBarHopper = "testbarhopper"; 
$username_connBarHopper = "myusername"; 
$password_connBarHopper = "mypassword"; 
$connBarHopper = mysql_pconnect($hostname_connBarHopper, $username_connBarHopper, $password_connBarHopper) or die(mysql_error()); 
mysql_select_db($database_connBarHopper); // Add this line
?>

EDIT:
I missed it in your first file, I see it was added there.

[Azala]

mysql_select_db($database_connBarHopper, $connBarHopper);

These variables aren't accessable however, so either put the line in the connBarHopper file as instructed above, or access the variables using $GLOBALS[$database_connBarHopper].

[krotkruton]

Azala, I'm a little confused on why those variables aren't accessible... connBarHopper.php is the file that contains the information $connBarHopper and $database_connBarHopper, and since I include connBarHopper.php with the line "require_once('../Connections/connBarHopper.php');", shouldn't they all be accessable?

Regardless, I believe I found the problem, but don't have much of a solution for it. It has to do with the search class and the $joins array nested in the $search_args array... My code doesn't use the $joins array because I used an example provided by someone else on how to implement the code. My reasoning for believing this is based largely on the fact that the error I get states "Table 'dbuserdb.userprofiles' doesn't exist" and 'dbuserdb.userprofiles' looks like it was created from this section of code:

            foreach($joins as $join) 
            { 
                $destination_table = $join["destination_table"]; 
                $source_table = $join["source_table"]; 
                $source_field = $join["source_field"]; 
                $destination_field = $join["destination_field"]; 
                $join_type = $join["type"]; 

            if(!$join_type) 
            { 
                $join_type = "INNER"; 
            } 

            $sql .= "{$join_type} JOIN {$join_table} ". 
                    "ON {$destination_table}.{$destination_field} = {$source_table}.{$source_field} "; 
        } 

So if anyone else reads this and understands how to properly implement this code without taking the shortcut of leaving out the $joins array, I would really appreciate an explanation. As you can see from the code, the sample on how to use search.php is not incredibly informative, or at least it isn't to someone who isn't great at php.

[Azala]

Look at

//yourfile
...
$search_args = array( 
...
//searchfile
...
$joins = $this->args["joins"];
...
if($joins)

You've never specified any JOINS in the search, thus it should not access that part of the code, and I don't believe that is your problem.

Did you try what I said above?