in sending links to users according to their interests

reimei · July 27, 2011

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.

Sign In

in sending links to users according to their interests

Recommended Posts

reimei

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information