我想在我的程序上创建一个sendmail函数。但首先，我想将信息：send_to、subject和message存储在另一个执行自动邮件的数据库（mes）的表中。问题是从另一个数据库（pqap）提取的数据没有添加到数据库（mes）的表（email_queue）中。
在这段代码中，我有一个表，其中存储了服务器中的所有数据库。我进行了一个查询以选择一个特定的数据库。
$sql5 = "SELECT pl.database, pl.name FROM product_line pl WHERE visible = 1 AND name='PQ AP'";
$dbh = db_connect("mes");
$stmt5 = $dbh->prepare($sql5);
$stmt5->execute();
$data = $stmt5->fetchAll(PDO::FETCH_ASSOC);
$dbh=null;
然后，在选择数据库之后，它会有一个查询，用于选择所选数据库的表中的信息。这是代码。
foreach ($data as $row5) GenerateEmail($row5['database'], $row5['name']);
那么这就是部分（我认为）不起作用。我不知道出了什么问题。
function GenerateEmail($database, $line) {
$sql6 = "SELECT * FROM invalid_invoice WHERE ID=:id6";
$dbh = db_connect($database);
$stmt6 = $dbh->prepare($sql6);
$stmt6->bindParam(':id6', $_POST['idtxt'], PDO::PARAM_INT);
$stmt6->execute();
$data = $stmt6->fetchAll(PDO::FETCH_ASSOC);
$dbh=null;
foreach ($data as $row6) {
$invnumb=$row6['Invoice_Number'];
$partnumb=$row6['Part_Number'];
$issue=$row6['Issues'];
$pic=$row6['PIC_Comments'];
$emailadd= $row6['PersoninCharge'];
if($row6['Status']=="Open") {
$message = "<html><b>Invoice Number: {$invnumb}.</b><br><br>";
$message .= "<b>Part Number:</b><br><xmp>{$partnumb}</xmp><br><br>";
$message .= "<b>Issues:</b><br><xmp>{$issue}</xmp><br>";
$message .= "<b>{$pic}<b><br>";
$message .= "</html>";
if(!empty($emailadd)) {
dbInsertEmailMessage($emailadd, "Invoice Number: {$invnumb} - {$issue}.", $message);
$dbh=null;
}
}
}
}
function dbInsertEmailMessage($send_to, $subject, $message) {
$sql7 = "INSERT INTO email_queue (Send_to, Subject, Message) VALUES (:send_to, :subject, :message)";
$dbh = db_connect("mes");
$stmt7 = $dbh->prepare($sql7);
$stmt7->bindParam(':send_to', $send_to, PDO::PARAM_STR);
$stmt7->bindParam(':subject', $subject, PDO::PARAM_STR);
$stmt7->bindParam(':message', $message, PDO::PARAM_STR);
$stmt7->execute();
$dbh=null;
}
这是我的数据库连接：
function db_connect($DATABASE) {
session_start();
// Connection data (server_address, database, username, password)
$servername = '*****';
//$namedb = '****';
$userdb = '*****';
$passdb = '*****';
// Display message if successfully connect, otherwise retains and outputs the potential error
try {
$dbh = new PDO("mysql:host=$servername; dbname=$DATABASE", $userdb, $passdb, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
return $dbh;
//echo 'Connected to database';
}
catch(PDOException $e) {
echo $e->getMessage();
}
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
有几件事可能有助于处理失败的插入。看看这是否是你想要的，我已经记下了需要考虑的要点：
<?php
// take session_start() out of your database connection function
// it draws an error when you call it more than once
session_start();
// Create a connection class
class DBConnect
{
public function connect($settings = false)
{
$host = (!empty($settings['host']))? $settings['host'] : false;
$username = (!empty($settings['username']))? $settings['username'] : false;
$password = (!empty($settings['password']))? $settings['password'] : false;
$database = (!empty($settings['database']))? $settings['database'] : false;
try {
$dbh = new PDO("mysql:host=$host; dbname=$database", $username, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
// You return the connection before it hits that setting
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $dbh;
}
catch(PDOException $e) {
// Only return the error if an admin is logged in
// you may reveal too much about your database on failure
return false;
//echo $e->getMessage();
}
}
}
// Make a specific connection selector
// Put in your database credentials for all your connections
function use_db($database = false)
{
$con = new DBConnect();
if($database == 'mes')
return $con->connect(array("database"=>"db1","username"=>"u1","password"=>"p1","host"=>"localhost"));
else
return $con->connect(array("database"=>"db2","username"=>"u2","password"=>"p2","host"=>"localhost"));
}
// Create a query class to return selects
function query($con,$sql,$bind=false)
{
if(empty($bind))
$query = $con->query($sql);
else {
foreach($bind as $key => $value) {
$kBind = ":{$key}";
$bindVals[$kBind] = $value;
}
$query = $con->prepare($sql);
$query->execute($bindVals);
}
while($row = $query->fetch(PDO::FETCH_ASSOC)) {
$result[] = $row;
}
return (!empty($result))? $result:0;
}
// Create a write function that will write to database
function write($con,$sql,$bind=false)
{
if(empty($bind))
$query = $con->query($sql);
else {
foreach($bind as $key => $value) {
$kBind = ":{$key}";
$bindVals[$kBind] = $value;
}
$query = $con->prepare($sql);
$query->execute($bindVals);
}
}
// Do not create connections in your function(s), rather pass them into the functions
// so you can use the same db in and out of functions
// Also do not null the connections out
function GenerateEmail($con,$conMes,$line = false)
{
if(empty($_POST['idtxt']) || (!empty($_POST['idtxt']) && !is_numeric($_POST['idtxt'])))
return false;
$data = query($con,"SELECT * FROM `invalid_invoice` WHERE `ID` = :0", array($_POST['idtxt']));
if($data == 0)
return false;
// Instead of creating a bunch of inserts, instead create an array
// to build multiple rows, then insert only once
$i = 0;
foreach ($data as $row) {
$invnumb = $row['Invoice_Number'];
$partnumb = $row['Part_Number'];
$issue = $row['Issues'];
$pic = $row['PIC_Comments'];
$emailadd = $row['PersoninCharge'];
if($row['Status']=="Open") {
ob_start();
?><html>
<b>Invoice Number: <?php echo $invnumb;?></b><br><br>
<b>Part Number:</b><br><xmp><?php echo $partnumb; ?></xmp><br><br>
<b>Issues:</b><br><xmp><?php echo $issue; ?></xmp><br>
<b><?php echo $pic; ?><b><br>
</html>
<?php
$message = ob_get_contents();
ob_end_clean();
if(!empty($emailadd)) {
$bind["{$i}to"] = $emailadd;
$bind["{$i}subj"] = "Invoice Number: {$invnumb} - {$issue}.";
$bind["{$i}msg"] = htmlspecialchars($message,ENT_QUOTES);
$sql[] = "(:{$i}to, :{$i}subj, :{$i}msg)";
}
}
$i++;
}
if(!empty($sql))
return dbInsertEmailMessage($conMes,$sql,$bind);
return false;
}
function dbInsertEmailMessage($con,$sql_array,$bind)
{
if(!is_array($sql_array))
return false;
write($con,"INSERT INTO `email_queue` (`Send_to`, `Subject`, `Message`) VALUES ".implode(", ",$sql_array),$bind);
return true;
}
// Create connections
$con = use_db();
$conMes = use_db('mes');
GenerateEmail($con,$conMes);