Hi
I have 4 tables named
tbl_user(id,email) id:PK
tbl_user_interest(user_id,cat_id) user_id:FK(tbl_user) , cat_id:FK(tbl_category)
tbl_category(id,name) id:PK
tbl_link(id,cat_id,url,subject,price) id:PK , cat_id:FK(tbl_category)
Now I've got new links and want to email them to users according to their interests. My solution is this:
$sql = 'SELECT tbl_user_interest.user_id,tbl_link.id,tbl_link.cat_id,tbl_link.subject,tbl_link.price FROM tbl_user_interest INNER JOIN tbl_link ON tbl_user_interest.cat_id = tbl_link.cat_id ORDER BY tbl_user_interest.user_id';
$db = new db;
$links = $db->queryAll($sql);
$result = array();
if(!empty($links))
{
foreach($links as $link)
{
$result[$link['user_id']][$link['id']] = array('subject'=>$link['subject'],'price'=>$link['price']);
}
$mail = $this->setEmail();
foreach($result as $user_id=>$info)
{
$body = '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>my site</title>
</head>
<body>
<h3>new links</h3>';
$sql = 'SELECT email FROM tbl_user WHERE id = \''.$user_id.'\'';
$email = $db->queryOne($sql);
$body .= '<ul>';
foreach($info as $link_id=>$link_info)
{
$body .= '<li><a href="'._PATH.'index.php/link/view?id='.$link_id.'" title="price: '.$link_info['price'].' " >'.$link_info['subject'].'</a></li>';
}
$body .= '</ul></body></html>';
$body = eregi_replace("[\]",'',$body);
$mail->MsgHTML($body);
$mail->AddBCC($email['email']);
$mail->Send();
}
But I think It's not very efficient.
I think its not necessary, but the code for "db" and "setEmail" are as the following:
class db
{
const name = 'dbname';
const host = 'localhost';
const user = 'root';
const pass = '';
private $connection;
public function __construct()
{
$connection = $this->connect();
}
public function connect()
{
if(empty($this->connection))
{
$this->connection = new mysqli(self::host ,self::user, self::pass,self::name) or die('connection error');
}
return $this->connection;
}
public function queryAll($sql)
{
$result = $this->execute($sql);
$rows = array();
while($row = $result->fetch_array(MYSQLI_ASSOC))
{
$rows[] = $row;
}
return $rows;
}
public function queryOne($sql)
{
$result = $this->execute($sql);
return $result->fetch_array(MYSQLI_ASSOC);
}
public function execute($sql)
{
$result = $this->connection->query($sql);
return $result;
}
}
private function setEmail()
{
$mail = new PHPMailer(true);
$mail->IsSMTP();
$mail->SMTPAuth = true; // enable SMTP authentication
$mail->SMTPSecure = "ssl"; // sets the prefix to the servier
$mail->Host = "smtp.gmail.com"; // sets GMAIL as the SMTP server
$mail->Port = 465; // set the SMTP port for the GMAIL server
$mail->Username = "
[email protected]"; // GMAIL username
$mail->Password = "password"; // GMAIL password
$mail->SetFrom('
[email protected]', 'my website');
$mail->AddReplyTo("
[email protected]","my website");
$mail->Subject = "my website";
$mail->WordWrap = 80; // set word wrap
$mail->IsHTML(true); // send as HTML
return $mail;
}
Do you have any idea?
thanks in advance.
