PHP/MySQL table question

newuser455 · 11-01-2005, 07:10 PM

I am trying to write a script that has a mass mail function, which sends an email to everyone in a database.

My MySQL table looks like this:

id name email

Anyways, all I want to do is output the email column to a string, such as

PHP Code:


			
$emails = email from row 1, email from row 2, email from row 3

etc....

Does anyone know how I could do this, or a better way to set up the table?

Thanks in advance.

fouldsy · 11-02-2005, 03:20 AM

The easiest way is to run a loop whilst executing your query, and stick the e-mail addresses into an array. Something like:

Code:

$i=0;
$result = @mysql_query("SELECT email FROM tablename");
while ($row = mysql_fetch_array($result)) {
     $email[$i] = $row["email"];
     $i++;
}

You will then have all your e-mail address stored in an array $email. A simple loop can then be used again to e-mail out whatever you need.

zeitounator · 11-02-2005, 03:45 AM

Quote:

Originally posted by fouldsy
You will then have all your e-mail address stored in an array $email. A simple loop can then be used again to e-mail out whatever you need.

Supposing (from the different pieces of code I can read) you are using php as your scripting language, and supposing you really want to put all these emails in a variable separated by commas, you don't even need a loop. Just use the implode() fuction (alias join()).

Code:

$emailfield = implode(',', $email);

See the implode function doc: http://www.php.net/manual/en/function.implode.php

fouldsy · 11-02-2005, 04:12 AM

That would work, but I don't see why the e-mail address would be constructed into a string before e-mail out. You'd end up with a massive string. Depending on the exact number of addresses being used, it might hit the size limit for a string variable. Plus, what if you then wanted to filter them or sort them? An array is much easier to work with than a long string. Effectively, you'd be e-mailing to multiple people from the one mail, meaning everyone sees the other addresses. The alternative is to run through each address as separate mail. You still the send the same number of e-mails, but in a slightly cleaner way.

zeitounator · 11-02-2005, 05:01 AM

Quote:

Originally posted by fouldsy
That would work, but I don't see why the e-mail address would be constructed into a string before e-mail out. You'd end up with a massive string.

This is why I took the precaution to state: supposing you really want to put all these emails in a variable separated by commas

j-ray · 11-02-2005, 06:58 AM

maybe you are better off using a perl script for that job. if u have to send huge numbers of emails the php script may run out of time. There is a maximal execution time assigned in php.ini.
You better keep track of where you already sent a mail in the database in case the script does not run thru the whole array of addresses. If u use php this is very likely to happen. So run an "update"-statement while looping thru the array...
cheers, j.

newuser455 · 11-02-2005, 07:23 AM

Hmm.. well.. I came up with this code which actually works.

PHP Code:


			
//Retrieve email addresses

$emails = '';

while (($row = mysql_fetch_array($result)) !== false){

$emails .= $row['email'].', ';

} 

$emails = trim($emails, ', ');

$emails_array = explode(', ', $emails);

fouldsy · 11-02-2005, 07:36 AM

Glad you got something going. Have fun!

zeitounator · 11-02-2005, 08:46 AM

Quote:

Originally posted by newuser455
Hmm.. well.. I came up with this code which actually works.

PHP Code:

//Retrieve email addresses $emails = ''; while (($row = mysql_fetch_array($result)) !== false){ $emails .= $row['email'].', '; } $emails = trim($emails, ', '); $emails_array = explode(', ', $emails);

Or the art to do it the complicated way when it can be simple.....

PHP Code:


			
// retreive all email addresses

while (($row = mysql_fetch_array($result)) !== false){

      $emails_array[] = $row['email'];

}

newuser455 · 11-02-2005, 03:31 PM

If I need it in a string, I can use $email, if I need an array I can use $email_array. Works fine for me

.

zeitounator · 11-03-2005, 04:50 AM

Quote:

Originally posted by newuser455
If I need it in a string, I can use $email, if I need an array I can use $email_array. Works fine for me .

PHP Code:


			
// retreive all email addresses

while (($row = mysql_fetch_array($result)) !== false){

      $emails_array[] = $row['email'];

}



// Then use a loop to send email ...

foreach ($emails_array as $email) {

    doSomeStuffWith($email);

}



// Or join all emails in a string

mail(implode(',', $mail_array), 'A test message', 'The content of test message', 'From: [email]blabla@nowhere.com[/email]');

Doing it this way as several (non exhaustive list....) advantages over your original piece of code:

you don't make unnecessary string operations
you don't use memory for a second variable containing duplicate data that you might not use
you limit the chances to have an other piece of code modifying one variable and not the other
your keep the ability to sort or filter your array before you implode it
you keep your code more clean, readeable and understandable
etc.

But you can of course still use you're piece of code that "works"