Problem in sending links to users according to their interes

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
reimei
Forum Newbie
Posts: 1
Joined: Wed Jul 27, 2011 9:13 am

Problem in sending links to users according to their interes

Post by reimei »

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:

Code: Select all

$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:

Code: Select all

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;	
	}
}

Code: Select all

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@gmail.com";  // GMAIL username
		$mail->Password   = "password";            // GMAIL password
		$mail->SetFrom('email@gmail.com', 'my website');
		$mail->AddReplyTo("email@gmail.com","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 :)
Post Reply