PHP Newsletter troubles 1

[mroos81]

Hello everybody,

I'm working on a PHP newsletter system for my website.
As people subscribe for the newsletter, they need to choose their fields of interest from a list of 5. They need to pick at least 1 interest, but can also pick all 5.

If a newsletter is sent, there is a text that everybody gets, no matter what interest they signed up for. After that, there is a text for every different interest.

A small example: if somebody is signed up for interest 1 and 5, they need to get the general text, the text for interest 1 and the text for interest 5.

I hope I made clear what I want to do.

Here is the code I have so far:

$query="SELECT personen.persoon_id, personen.email, interesse_persoon.interesse_id FROM personen JOIN interesse_persoon 
ON personen.persoon_id = interesse_persoon.persoon_id ORDER BY persoon_id";

$res=mysql_query($query);
while($sendemail=mysql_fetch_array($res)){ [COLOR=red]//I think there is something wrong in this line[/color]
$lastid="";
$to = $sendemail["email"];
$from = "Michael Roos<m.roos@proeducation.nl>";
 
$subject = $mail->onderwerp->GetText();
$message[0]= $mail->tekst->GetText(); [COLOR=red]//This text is ment for everybody on the list[/color]
$message[1]= $mail->tekst1->GetText(); [COLOR=red]//This text is only ment for people with interest1[/color]
$message[2]= $mail->tekst2->GetText(); [COLOR=red]//This text is only ment for people with interest2[/color]
$message[3]= $mail->tekst2->GetText(); [COLOR=red]//This text is only ment for people with interest3[/color]
$message[4]= $mail->tekst2->GetText(); [COLOR=red]//This text is only ment for people with interest4[/color]
$message[5]= $mail->tekst2->GetText(); [COLOR=red]//This text is only ment for people with interest5[/color]
 
$headers = "From: $from\nReply-To: $from\nContent-Type: text/html";
while(mysql_num_rows($res) && $row=mysql_fetch_array($res)){
if($lastid <> $row["persoon_id"] && $lastid <> "") mail ($to, $subject, "$message[0]<p>$suffix", $headers);
$suffix.="<P>".$message[$row["interesse_id"]];
$lastid=$row["persoon_id"];
}
}

I think there is something wrong with the way the e-mailaddresses are fetched from the database. I have 3 addresses in my test-database, but only 1 e-mail is sent.

I'm not at all a PHP-expert so the fault can be somewhere else as well.

Can somebody help me out here? That would be great!

Many thanks,

Michael Roos
Holland

 

[DRJ478]

Here are some hints that will help you:
1. Indent your code. It's hard to follow the flow without indentation.
2. Define the messages outside the loop that handles the mailing - they don't change and therefore it is processing overhead to retrieve them each time.
3. Write a function to send the mail, it's just a bit clearer and makes the main body more concise.
4. Do the minimum error trapping with the MySQL commands (see below).
5. I see the problem with the second nested while loop. You don't need that. You also don't need to keep track of IDs, just work through the result set:

# initialize messages
$message[0]= $mail->tekst->GetText(); //This text is ment for everybody on the list
$message[1]= $mail->tekst1->GetText(); //This text is only ment for people with interest1
$message[2]= $mail->tekst2->GetText(); //This text is only ment for people with interest2
$message[3]= $mail->tekst2->GetText(); //This text is only ment for people with interest3
$message[4]= $mail->tekst2->GetText(); //This text is only ment for people with interest4
$message[5]= $mail->tekst2->GetText(); //This text is only ment for people with interest5

# define SQL query and execute
$query="SELECT personen.persoon_id, personen.email, interesse_persoon.interesse_id FROM personen JOIN interesse_persoon
ON personen.persoon_id = interesse_persoon.persoon_id ORDER BY persoon_id";
$res=mysql_query($query) OR die("Query failed: ".$query." MySQL said: ".mysql_error());

# iterate the result set
while($row=mysql_fetch_array($res)){
	
	$to = $sendemail["email"];
	$from = "Michael Roos<m.roos@proeducation.nl>";
	$subject = $mail->onderwerp->GetText();
	# headers
	$headers = "From: $from\nReply-To: $from\nContent-Type: text/html";
	# send message
	$suffix.="<P>".$message[$row["interesse_id"]];
	mail ($to, $subject, $message[0]."<p>".$suffix, $headers);
}

There are a few more things:
Your line

while(mysql_num_rows($res) && $row=mysql_fetch_array($res)){

makes not much sense. The second loop is not needed at all.
Also, In your example you assign something to $suffix after the mail has been sent.
Another big caveat:
Don't use $array[n] or $array['blah'] within double quotes. It seems to me better practive to concatenate the portions of the final string rather than relying on PHP to correctly process the metadata in double quotes.
In my opinion:
Good: echo "<strong>".$array['whatever']."</strong>"

Not good: echo "<strong>$array['whatever']</strong>"

Hope that helps.

 

[mroos81]

Hello,

Thanks for your advice, it helped me out quite a bit! The system now actually sends mail to every address in the database. But it's still not quite working the way I want it.
Let me sketch a situation here.

Let's say this is the result of the query:

SELECT personen.persoon_id, personen.email, interesse_persoon.interesse_id FROM personen JOIN interesse_persoon
ON personen.persoon_id = interesse_persoon.persoon_id ORDER BY persoon_id


persoon_id | email | interesse_id
-------------------------------------------
150 | test_1@test.nl | 2
150 | test_1@test.nl | 1
151 | test_A@test.nl | 2
152 | test_X@test.nl | 2
152 | test_X@test.nl | 1


What happens is the following: everybody in the database gets the general piece of text, which is excellent.
After that, things go weird.

- The first person (id 150, signed up for two interests) on the list gets two e-mails, one only with the text for interest 1, the second one with the text for interest 2 and 1.

- The second person (id 151, signed up for one interest) gets one e-mail, but with texts for 2, 1 and then 2 again.

- The third person (id 152, signed up for 2 intrests) gets two e-mails again, the first one with the texts for 2, 1, 2 and 2. The second e-mail with the texts for 2, 1, 2, 2 and 1.

You see what's going on here? It's some sort of pyramid-system....
What I want is when somebody signs up for 1 and 2, he should only get one e-mail with only those texts in it!

Do you have any idea have to achieve this? Because like I said: I'm not PHP-expert at all, and I'm pretty stuck!
I'm gratefull for any help!

Michael Roos
Holland

 

[DRJ478]

The culprit is the dot which i failed to remove:

$suffix.="<P>"[b][COLOR=red].[/color][/b][code]$message[$row["interesse_id"]];

It just keeps gathering all special interest messages.
Remove it, and you should be all set. It should be a straight assignment, not concatenation:

$suffix = "<P>".$message[$row["interesse_id"]];

 

[mroos81]

Thanks again for your help!

You were right, this solved the 'pyramid-effect'!

But now, a new mail is sent for evey interest. So if somebody is signed up for interest 1 and 2, he now receives 2 seperate e-mails, both with the general text, but one with just text 1 and the other one just with text 2.

What I want is to include in ONE e-mail.
So if somebody signs up for interest 1 and 2, both these texts need to be included in the same e-mail.

You know what I mean? Is there any way to achieve that?
I allready searched the net for examples but I can't find a single thing that can help me.

Hopefully you can give me some more tips, that would be great!

Thanks again!

Michael

 

[DRJ478]

Ok, now we need to clarify something about the database:
The way you describe it now: the table you have has multiple records per UserId. Right - that wasn't clear to me from your initial post ]my mistake]. That's a little different then, but not impossible.

I would split up the query into 2 statements:
1. Retrieve all unique UserId's from the preference table.
2. Loop through them and issue a second query that retrieves all interests for the current UserId.

$mainQuery = "SELECT distinct UserId FROM table...";
# retrieve result set
$mResult = mysql_query($mainQuery) OR die("Main query failed");

# iteration
while ($row = mysql_fetch_assoc($mResult)){
   # setup sub query
   $subQuery = "SELECT interesID FROM table WHERE UserId='".$row['UserId']."'";
   $sResult = mysql_query($subQuery) OR die("Sub query failed");
   while ($sRow = mysql_fetch_assoc($sResult)){
      $interestMessage .= .'<p>'.$messages[$sRow['interesID']];
   }
   # now mail function
   etc.
}

 

[mroos81]

Once again - thanks very much for your help, I really appreciate it!

You're right, I have multiple records per userID. Also kinda my mistake, it's a bit difficult for me to explain everything in a clear way in English.

Anyway, I copied your example and I still got a little error. If I open the page with the script on it, I get a parse error.

This is the code as I have it now, I marked te place were the parse error is caused.

//main query
$mainQuery = "SELECT distinct personen.persoon_id, personen.email FROM personen";
//retrieve result set
$mResult = mysql_query($mainQuery) OR die("Main query failed");

//iteration
while ($row = mysql_fetch_assoc($mResult)){
   # setup sub query
   $subQuery = "SELECT interesse_persoon.interesse_id FROM interesse_persoon WHERE interesse_persoon.persoon_id='".$row['personen.persoon_id']."'";
   $sResult = mysql_query($subQuery) OR die("Sub query failed");
   while ($sRow = mysql_fetch_assoc($sResult)){
   $interestMessage .= ."<P>".$message[$row["interesse_id"]]; //this is the line were things go wrong --> parse error
   }

//mail function
    
    $to = $row["email"];
    $from = "Michael Roos<m.roos@proeducation.nl>";
    $subject = $mail->onderwerp->GetText();
    # headers
    $headers = "From: $from\nReply-To: $from\nContent-Type: text/html";
    # send message
    mail ($to, $subject, $message[0]."<p>".$interestMessage, $headers);
}

I played with it a bit, but whatever I tried with te dots, nothing seemed to work. When I removed the dot after the '=', I didn't got the error but none of the interest texts were sent.
Can you tell me what's the problem here?

Thanks!

Michael

 

[DRJ478]

Once again, an additional dot. Where do I come up with these?

# WRONG
$interestMessage .= .'<p>'.$messages[$sRow['interesID']];
   }
# CORRECT
$interestMessage .= '<p>'.$messages[$sRow['interesID']];
   }

It's the extra dot after the equal sign. Sorry, typing too fast. Best regards to Holland!

 

[mroos81]

Hello,

Thanks again for your reply!
I allready figured out myself what dot was wrong. But it still isn't working OK.

When I send a message, everything seems to work fine, and just one e-mail is sent to everybody on the list.
But - only the general text is in it and none of the interest-texts....

In my opinion that must mean that the main query works fine, but that things go wrong during the sub query or the processing of it.

Here is what I have now:

[COLOR=red]//main query[/color]
$mainQuery = "SELECT DISTINCT personen.persoon_id, personen.email FROM personen
WHERE personen.type = '$type'";

[COLOR=red]//retrieve result set[/color]
$mResult = mysql_query($mainQuery) OR die("Main query failed");

[COLOR=red]//iteration[/color]
while ($row = mysql_fetch_assoc($mResult)){

[COLOR=red]//setup sub query -> I think it goes wrong somewhere during this query or the processing of it[/color]
$subQuery = "SELECT interesse_persoon.interesse_id, interesse_persoon.persoon_id FROM interesse_persoon
WHERE interesse_persoon.persoon_id='".$row['personen.persoon_id']."'";

$sResult = mysql_query($subQuery) OR die("Sub query failed");
while ($sRow = mysql_fetch_assoc($sResult)){
$interestMessage .= '<P>'.$message[$sRow["interesse_persoon.interesse_id"]];
}

[COLOR=red]//mail function[/color]
$to = $row["email"];
$from = "Michael Roos<m.roos@proeducation.nl>";
$subject = $mailform->onderwerp->GetText();

[COLOR=red]//headers[/color]
$headers = "From: $from\nReply-To: $from\nContent-Type: text/html";

[COLOR=red]//send message[/color]
mail ($to, $subject, $message[0]."<p>".$interestMessage, $headers);
}

Any idea why it isn't working just yet?
It's very frustrating for me to be so close but not being able to solve the problems completely on my own. So I really hope you can help me out here again!

Many thanks,

Michael

 

[DRJ478]

Have a look at how MySQL returns the associative array. I belive this to be the reason:

# wrong
$interestMessage .= '<P>'.$message[$sRow["interesse_persoon.interesse_id"]];
# correct
$interestMessage .= '<P>'.$message[$sRow["interesse_id"]];

Just fit in a quick print_r($row) to examine the structure of the associative array. You'll see how MySQL keys the array.

 

[mroos81]

You were right, that was the reason why it wasn't sending anything.
It sends mails now, but in the strange 'pyramid' kinda way I had before...

I tried it in tow different ways:

[COLOR=red]//way one[/color]
$interestMessage .= '<P>'.$message[$sRow["interesse_id"]];

[COLOR=red]//way two[/color]
$interestMessage = '<P>'.$message[$sRow["interesse_id"]];

In the first way it is the 'pyramid' way again.

In the second way it only sends one interest text where it needs to send multiple.


Again: thanks for your help!

Michael

 

[DRJ478]

Here comes the solution:
After sending the mail the variable $interestMessage needs to be reset otherwise it will just keep the messages from the previous person.
All you need to add is after sending the mail:

$interestMessage = '';

This should really be it.

 

[mroos81]

Excellent!
That did the trick, it's working just fine now!
Thanks so much for all your help!