elmas156 Posted April 18, 2011 Share Posted 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'"); } } ?> Quote Link to comment https://forums.phpfreaks.com/topic/234075-connecting-to-two-databases-at-the-same-time/ Share on other sites More sharing options...
PFMaBiSmAd Posted April 18, 2011 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/234075-connecting-to-two-databases-at-the-same-time/#findComment-1203096 Share on other sites More sharing options...
elmas156 Posted April 18, 2011 Author Share Posted April 18, 2011 Yes, they ARE on the same server and they ARE accessed with the same username/password. Quote Link to comment https://forums.phpfreaks.com/topic/234075-connecting-to-two-databases-at-the-same-time/#findComment-1203100 Share on other sites More sharing options...
elmas156 Posted April 18, 2011 Author Share Posted 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? Quote Link to comment https://forums.phpfreaks.com/topic/234075-connecting-to-two-databases-at-the-same-time/#findComment-1203104 Share on other sites More sharing options...
PFMaBiSmAd Posted April 18, 2011 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/234075-connecting-to-two-databases-at-the-same-time/#findComment-1203107 Share on other sites More sharing options...
elmas156 Posted April 18, 2011 Author Share Posted April 18, 2011 Got it working perfectly. Thanks for your help! Quote Link to comment https://forums.phpfreaks.com/topic/234075-connecting-to-two-databases-at-the-same-time/#findComment-1203161 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.