Emailing orders from Database

[Anon]

I will be storing orders in the mysql database. At the end of each day, I want to select all orders to be sent to the appropriate vendor and email the specific order (fields) contents to the vendor. I need to send each order (row data) separately (1 email per order) to the selected vendor, so they can print it, use it as a "pick list", and then ship from it. Orders could be in the magnitude of 100-300 per day for each vendor. I anticipate 5 vendors.

Thanks for the help.

[[deleted]]

And you problem is.... ?

It's clear what you want to do, but which part do you need help with?

[Anon]

Thanks for the response. I need the help in sending the email portion.

kend

[[deleted]]

The easiest way is to use the mail() function, but that is not know for it's speed.
You may be better of talking directly to you sendmail deamon.

You can use simple SQL to fetch the order and create the message itself,
then use an exec() to call sendmail and feed it the message.

Have a look at this page, it has sourcecode for this function.

http://bi.org/php/sendmail.html
(BTW, I just searched google and found this. No particular preference for this link)

[Anon]

Ok, thanks. Now how would one go about getting and sending them one at a time when there would be 100-300 meeting the query as "ready to go"? This is the area giving me the problem.

[[deleted]]

Get yourself a CGI copy of PHP so you can run the script from the crontab.

Then the simplest way would be to start an SQL query that fetches all the "ready to go" records and perform the sendmail action for each result of the query.

in pseudo code:
$query="select * from table where order is ready for sending";
while ($row=mysql_fetch_array())
{

create email text.

send email

};

If you run into preformance problems you could try running two scripts, one processing the first 150 results, and the other the second 150.
or three scripts doing 100 each, etc.

[Anon]

Thanks,

I will give it a try.

[kparker]

Yes, but if the whole reason for talking directly to the mailserver is to avoid the overhead of calling mail() each time, let's make that explicit in the pseudocode:

$query="select * from table where order is ready for sending";
if (there is at least one result)
  {
  #open socket to smtp server, say HELO

  while ($row=mysql_fetch_array())
    {
    # create email text.
    # send email
    ## consisting of
    ## MAIL FROM: ttttt
    ## RCPT TO: ttttt
    ## DATA
    ## the body of the mail,
    ## making sure you don't have a bare '.' line
    ## . on a line by itself to terminate the data
    ## but DON'T exit from the mailserver yet
    }
  #disconnect from smtp server
  }

[[deleted]]

Who ever talked about opening a socket to a mailserver? :-)

I already gave an example of piping data to sendmail, not the sendmail deamon.
So putting 2 and 2 together in this case shouldn't be a problem for Kend.

[Anon]

Is using the socket, more reliable, better, etc?

[[deleted]]

Using a socket saves you the trouble of starting up a copy of sendmail, which means itsaves you some memory.
That's helpfull when you have to do tons and tons of email.

On the other hand, doing it with sockets is more complicated than starting a sendmail.

[Anon]

Thanks for your help, I appreciate it.