Hi All,

Im running a script that goes 3 deep on parentheses, and then needs to change database at the deepest level.

Im having trouble switching to that database in an if else query.

any help would be appreciated

function getElement1(){
		$con = mysql_connect('localhost','uname','pword') or die (mysql_error());
		$authenticate = mysql_select_db('authenticate');
		$today = date("Y-m-d");
		$SQL = "SELECT  campaign, agent, SUM(duration), firstname, lastname
				FROM rota_element
				JOIN people on agent= user_id
				WHERE date = '$today'
				AND rota_element.active ='Yes'
				AND campaign NOT IN('BCC', 'Holiday')
				GROUP BY agent";
		$result = mysql_query($SQL) or die (mysql_error());
		echo "<table border=\"1\">
			  <tr><th>First Name</th>
			  <th>Rota Hours</th>
			  <th>Volume Target</th>
		while ($row = mysql_fetch_array($result)){
			$agent = $row['agent'];
			$campaign = $row['campaign'];
			echo "<tr><td>" . $row['firstname'] . '</td><td>' . $row['lastname'] . '</td><td>' . $row['SUM(duration)'] . "</td></tr>";
				$SQL2 = "SELECT distinct(campaign) FROM rota_element WHERE agent = '$agent' AND date = '$today' AND campaign NOT IN ('BCC', 'Holiday') AND active = 'Yes'";
				$results = mysql_query($SQL2) or die(mysql_error());
				while ($row = mysql_fetch_array($results)){
					$campaign = $row['campaign'];
					echo "<tr><td></td><td>" . $row['campaign'] . "</td>";
					$SQL3 = "SELECT SUM(duration), agent FROM rota_element WHERE agent = '$agent' AND date = '$today' AND campaign = '$campaign' and active = 'yes' group by campaign";
					$results3 = mysql_query($SQL3) or die(mysql_error());
					while ($row = mysql_fetch_array($results3)){
						echo "<td>" . $row['SUM(duration)'] . "</td>";
						$user_id = $row['agent'];
						$SQL4 = "SELECT agentid FROM people WHERE user_id = '$agent'";
						$results4 = mysql_query($SQL4) or die(mysql_error());
						$row = mysql_fetch_assoc($results4);
						$user_id = $row['agentid'];
						if ($campaign == 'one'){
							$SQL5 = "SELECT COUNT(*) FROM one.accounts WHERE agentid = '$user_id' AND status = 'COMPLETED' and DATE(timestamp_dtm) = '$today'";
							$results5 = mysql_query($SQL5) or die (mysql_error());
							$row = mysql_fetch_assoc($results5);
							$completed = $row['COUNT(*)'];
							echo "<td>" . $completed . "</td>";
       //once i get here it wont switch databases.  It stops showoing all results


Are you saying that you want to abandon the results of the Selects that were done on the first pass and now start over with another database (in the same format as the first) and keep executing your while loops using data from the second database? That would be a pretty unusual approach. If that isn't what you mean, then you need to explain what you're aiming to do with the second database.


Thanks for the response.

I dont want to lost the main connection, as it would stop the query, as at that point is is relying on that database. I need to remain connected, and then based on an if else, connect to a totally different database.


I cracked it.

after FROM i just needed to put the database name at the start of it, so rather than rota_element, i stated authenticate.rota_element.

Also, this was a class method. I took it out of the class and just made it a function, which speeded it up as well.

Thanks again