Connecting to two Databases at the same time.

elmas156 · April 18, 2011

Hello everyone. I have a site that serves multiple clients, each of which have their own database. For each client there are a few people who I have to send reports to each day of the activity that occurred on their account. Instead of creating a different script to run individually as a cron job for each client, I decided to create one that will run each day and will handle sending each clients report to every person on the list to receive the reports. To do this, I'm going to have to connect to two databases at the same time to get each client's account number from a general database, then get the activity on each client's account database to send the reports. I'm pretty sure I'm on the right track, but I'm still having problems. Any help would be greatly appreciated. Here's what I have so far:

This code returns "Table 'dbase_admin.dbase_admin' doesn't exist"

<?php
$db1_user = "uname"; // Username
$db1_pass = "*****"; // Password
$db1_database = "dbase_admin"; // Database Name
$db1_host = "localhost"; // Server Hostname
$db1_connect = mysql_connect ($db1_host, $db1_user, $db1_pass); // Connects to the database.
$db1_select = mysql_select_db ($db1_database); // Selects the database.

$clientresult = mysql_query("SELECT `acct_number`,`school` FROM `accounts`, $db1_database") or die (mysql_error());

while ($clientrow = mysql_fetch_row($clientresult)) {

$acct = $clientrow[0];
$school = $clientrow[1];

$db2_user = "uname"; // Username
$db2_pass = "*****"; // Password
$db2_database = "dbase_$acct"; // Database Name
$db2_host = "localhost"; // Server Hostname
$db2_connect = mysql_connect ($db2_host, $db2_user, $db2_pass); // Connects to the database.
$db2_select = mysql_select_db ($db2_database); // Selects the database.

$result3 = mysql_query("'SELECT * FROM allmsgs WHERE reported = 'n', $db2_database") or die (mysql_error());
$row3 = mysql_fetch_row($result3);

$cdate = date('m-d-Y');

if ($row3 > 0) {

$result = mysql_query("SELECT `studentname`,`staffname`,`subject`,`message`,`date` FROM allmsgs WHERE reported = 'n' ORDER BY `messid` ASC", $db2_database);

$result2 = mysql_query("SELECT `prefix`,`lname`,`email` FROM admin WHERE send = 'y', $db2_database");

$eachprefix = '';
$eachlname = '';
$eachemail = '';

	while ($row2 = mysql_fetch_row($result2)) {

		$prefix = $row2[0];
		$lname = $row2[1];
		$email = $row2[2];

		$eachprefix .= "$prefix, ";
		$eachlname .= "$lname, ";
		$eachemail .= "$email, ";

	}

	$sendto = "$eachemail";
	$emailsubject = "$school Message Report For $cdate.";				
	$eachmessage = '';

	while ($row = mysql_fetch_row($result)) {

		$studentname = $row[0];
		$staffname = $row[1];
		$subject = $row[2];
		$message = $row[3];
		$saveddatetime = $row[4];

		$message2 = nl2br("$message");

		$eachmessage .= "<p>
		<table width=\"500\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">
		<tr>
		<td align=\"left\">
		<hr width=\"500\" />
		<hr width=\"500\" />
		<font size=\"+1\"><strong>New Message:</strong></font><br>
		<hr width=\"500\" />
		<hr width=\"500\" /><br />
		To: $staffname<br />
		From: $studentname<br />
		On $saveddatetime<br> <br />
		Subject: $subject<br> <br />
		$message2<br> <br />
		</td>
		</tr>
		</table>
		</p>";

	}

	$emailmessage = "<html>
	<body>
	<p>
	<table width=\"500\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">
	<tr>
	<td>
	<strong>$eachprefix $eachlname,
	<p>Here is a list of the messages that have been exchanged in the last 24 hours using the mysite.com system.</p></strong>
	</td>
	</tr>
	</table>
	</p> <br \>
	$eachmessage
	</body>
	</html>";

	// To send HTML mail, the Content-type header must be set
	$headers  = 'MIME-Version: 1.0' . "\r\n";
	$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";

	// Additional headers

	$headers .= 'From: mysite.com Report <support@mysite.com>' . "\r\n";

	// Tells the mail server who the email is from				
	$fromaddress = '-fsupport@mysite.com';

	// Mail it
	mail($sendto, $emailsubject, $emailmessage, $headers, $fromaddress);

	mysql_query("UPDATE `allmsgs` SET `reported` = 'y' WHERE `reported` = 'n'");		

}

}
?>

PFMaBiSmAd · April 18, 2011

Are the databases on the same database server and can you access them using the same username/password?

Also, don't put your second connection INSIDE of a loop.

And, the 2nd parameter in the mysql_query() statement is the connection resource, not the database name.

elmas156 · April 18, 2011

Yes, they ARE on the same server and they ARE accessed with the same username/password.

elmas156 · April 18, 2011

Also, the second connection is INSIDE the loop because part of the database name is the client's account number. So I'm trying to use the loop to get each client's account number to use so the database name in the second connection is set to "dbase_$acct". Any ideas to get around this?

PFMaBiSmAd · April 18, 2011

If the same username/password has access to all the databases, you only need one connection.

You can specify the database name in the query using - database_name.table_name where you are currently using the table_name

Also, I mis-read what I thought was the second parameter in the msyql_query() statement. What you had was inside the query string, not out side it.

elmas156 · April 18, 2011

Got it working perfectly. Thanks for your help!

Sign In

Connecting to two Databases at the same time.

Recommended Posts

elmas156

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

elmas156

Link to comment

Share on other sites

elmas156

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

elmas156

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information