Jump to content

Retrieving sql data to php and sending mail

jc624

By jc624
in PHP

Recommended Posts

jc624 Newbie

jc624

Posted
Posted

OK here's what I got:I got an sql data table (a huge list) all ready to go-also I have a php email relay ready to go to our clients.So what I need to do is get that data into that php email and send it out...How do I do that?Is it just getting the sql and adding it on my php email code? like this:

<?php$Database = mysql_connect("myhost", "username", "password");mysql_select_db("dbname", "$Database");?>

thanks in advance.

Link to comment
Share on other sites
jhecht Invested Member

jhecht

Posted
Posted 
$connection = mysql_connect('localhost','user','pw') or die(mysql_error());mysql_select_db('dbName',$connection);//you don't need quotes around the connection variable.

Otherwise you'd just get what information you need from the database, the emails of your clients, and send them out like that.

Link to comment
Share on other sites
jc624 Newbie

jc624

Posted
  • Author
Posted 
$connection = mysql_connect('localhost','user','pw') or die(mysql_error());mysql_select_db('dbName',$connection);//you don't need quotes around the connection variable.

Otherwise you'd just get what information you need from the database, the emails of your clients, and send them out like that.

Awesome thanks...Just one last qWhat is it has multiple tables? do I then add querries? Example:
$fields = mysql_query("SELECT * FROM Articles",$connection);

Link to comment
Share on other sites
jhecht Invested Member

jhecht

Posted
Posted

im assuming the basic structure is something along these lines:

customers(customer_id int unique not null auto_increment,customer_name varchar(200) not null,customer_email varchar(255) not null)//Keep in mind this is bare minimum.articles(article_id int unique not null auto_increment,article_title varchar(100) not null,article_text TEXT,fulltext(article_text))

Assuming you wanted the last, most recent article to be mailed out, you would do this:

$msa = mysql_connect('localhost','user','pw') or die(mysql_error());@mysql_select_db('yourDb');$getEmails = "SELECT customer_email,customer_name FROM customers";$ansEmails = mysql_query($getEmails) or die(mysql_error());$emailToString = '';while($data = mysql_fetch_assoc($ansEmails)){   $emailToString .= " ".$data['customer_name']."<".$data['customer_email'].">";}$getArticle = "SELECT * FROM articles ORDER BY article_id DESC LIMIT 1";$ansArticle = mysql_query($getArticle) or die(mysql_error());$article = mysql_fetch_assoc($ansArticle);//Article information now in an array;mysql_free_result($ansEmails);mysql_free_result($ansArticle);//free the results, gets rid of table overhang.$emailBodyString = <<<EOD   <html>	<body>	  <b>$article['title']</b><br />	  <div style="border:1px solid black;">		$article['article_text'];	  </div>	</body>   </html>EOD; mail($toString,"Monthly/Hourly/Weekly Newsletter",$emailBodyString);//I'm sure there's an extra header you have to set in order for the HTML email to work, however I'm not sure what it is off the top of my head.?>

Keep in mind you should change the columns, tables, and pretty much everything else i've put up to the things that you've got(mine is just for an example).

Link to comment
Share on other sites
jc624 Newbie

jc624

Posted
  • Author
Posted

Hey thanks! yah that looks close to what I'm trying to do.So you can understand heres what I'm inputting:I have both email.php (which is working and is relaying to the clients) and my data (I used the free mySQL yog btw) on the same server.The Data has tons of scoring/rank that clients will get on that email on how they scored on an event we had.The email will say :Thanks for participating in our event blahblahblahblah......Your store: #(the data here) store id# ranked:#(the data here)#its about 1500! The data table has:

  • store id
  • correct answers in numbers
  • wrong answers in numbers
  • rank distric
  • rank region
  • etc

I'll start doing what you mentioned and thanks!!!

Link to comment
Share on other sites
jc624 Newbie

jc624

Posted
  • Author
Posted

One other question:I just got the big list of emails(about 1500) to our clients email and their store id that I have to send it to. So I guess I'll have to identified them by the store id (create another table with it)..so base on the id it will give them their proper ranking.One thing I never done is do a mass email...but do you just create another data and just have the email to equal the id?Thx!

Link to comment
Share on other sites
justsomeguy w3schools Guru

justsomeguy

Posted
Posted

If you're going to be sending out bulk email it will be better to use PEAR. The PHP mail function opens and closes a socket connection every time you send a mail, so if you have a loop that sends 1000 mails then the script needs to open and close 1000 connections to the mail server, that's not very efficient. The PEAR Mail and Mail_Queue packages would be better.http://pear.php.net/manual/en/package.mail.mail.phphttp://pear.php.net/manual/en/package.mail.mail-queue.php

Link to comment
Share on other sites
astralaaron Dedicated Member

astralaaron

Posted
Posted

will someone point out the difference between$connection = mysql_connect('localhost','user','pw');mysql_select_db('dbName',$connection);and mysql_select_db('dbName');why do you have to add the connection variable to the select_db arguments?

Link to comment
Share on other sites
Ingolme w3schools Guru

Ingolme

Posted
Posted
will someone point out the difference between$connection = mysql_connect('localhost','user','pw');mysql_select_db('dbName',$connection);and mysql_select_db('dbName');why do you have to add the connection variable to the select_db arguments?
If you are connecting to more than one server (it can happen sometimes) you can choose which server you want to select the database from.If you don't select a connection it will use the last connection you opened.For example:
$con = mysql_connect("sql.server1.com","user1","password1");$con2 = mysql_connect("sql.server2.com","user2","password2");mysql_select_db("information",$con2);

This will select the database "information" that's in sql.server2.com

Link to comment
Share on other sites
jc624 Newbie

jc624

Posted
  • Author
Posted
If you're going to be sending out bulk email it will be better to use PEAR. The PHP mail function opens and closes a socket connection every time you send a mail, so if you have a loop that sends 1000 mails then the script needs to open and close 1000 connections to the mail server, that's not very efficient. The PEAR Mail and Mail_Queue packages would be better.http://pear.php.net/manual/en/package.mail.mail.phphttp://pear.php.net/manual/en/package.mail.mail-queue.php
Awesome thanks I heard of PEAR just never tried it.So this would be the best way I assume otherwise I would have to do it manually....I thought there was a way /script to identify the id/email and send it out.Thanks guys!
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...