[RESOLVED] assiging values to two fields during foreach loop

[kiwibrit]

I have a members MySQL database table that has 3 fields - id, confirm_code, and email.

When mailing a newsletter, I want to use from each row email to actually send the newsletter, and confirm_code for an unsusbscribe link.

At present, I have not figured how to do this, so I simply select email for both jobs, not really desirable, with the following code, which gives me the variable $email:

$query="SELECT email FROM $tbl_name1 ";
			$result = mysql_query($query) or die ("Error in query: $query. " .
			mysql_error());
			while ($row = mysql_fetch_row($result)) { 
			foreach ($row as $email) {
///stuff
}}

How can I get variables assigned to confirm_code AND email for each iteration of the loop?

[laserlight]

You can select more than one column. I would suggest:

$query = "SELECT email, confirm_code FROM $tbl_name1";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
while ($row = mysql_fetch_assoc($result)) {
    // Use $row['email'] and $row['confirm_code']
}

[kiwibrit]

Thanks for getting back to me.

Following up, here's what I wrote:

<?php
//connect to data base
//extractl list of emails of newsletter subscribers
$tbl_name1="testmembers";
$query="SELECT email, confirm_code FROM $tbl_name1";
			$result = mysql_query($query) or die ("Error in query: $query. " .
			mysql_error());
			while ($row = mysql_fetch_row($result)) { 
			foreach ($row['email'] , $row['confirm_code']) {
			$email=$row['email'];
			$confrim_code=$row['confirm_code'];
//post newsletter to $email, with unsubscribe link containing $confirm code
}}
?>

However,

			foreach ($row['email'] , $row['confirm_code']) {

throws up a syntax error.

How should I write that line?

[rulian]

following up on what laserlight instructed, this is more how to structure your code:

<?php
//connect to data base
//extractl list of emails of newsletter subscribers
//// $tbl_name1="testmembers";
//// Unless your querying different tables, no need to have a variable tablename

//// Define constants 
$Newletter="Once upon a midnight dreary...."; 
$Headers="From: [e-mail]";
$Subject="My newsletter";   

$query="SELECT email, confirm_code FROM `testmembers`";
            ///die($query) is okay when your developing, b ut never reveal your sql to users on a live server
            $result = mysql_query($query) or die ("Error in query: $query. " .
            mysql_error());
//// notice mysql_fetch_assoc instead of mysql_fetch_row 
            while ($row = mysql_fetch_assoc($result)) 
            {
        //// the $row variable with mysql_fetch_assoc will already be an array
       //// so you do not have to recreate them or restate them
      ////post newsletter to $email, with unsubscribe link containing $confirm code
            $Link="<a href='http://www.mywebsite.com/unsubscrible.php?email=".$row['email']."&confirm=".$row['confirm_code']."'>Unsubscribe here</a>"; 
            $Newsletter_wLink=$Newsletter.$Link; 
///// Be sure to insert line breaks so the link has its own line
            mail($row['email'], $Subject, $Newsletter_wLink, $Headers); 


        }
?> 

that should give you an idea, there are some security and failsafe issues I did not touch in that code

[kiwibrit]

Hmm.

Doing some simple testing here,

<?php
//connect to data base
//extractl list of emails of newsletter subscribers
$tbl_name1="testmembers";
$query="SELECT email, confirm_code FROM $tbl_name1";
			$result = mysql_query($query) or die ("Error in query: $query. " .
			mysql_error());
			while ($row = mysql_fetch_row($result)) { 
echo $row['email'].'<br>';
echo $row['confirm_code'].'<br>';
}
echo "Newsletter sent";

?>

gives me some blank screen above 'Newsletter sent'.

<?php
//connect to data base
//extractl list of emails of newsletter subscribers
$tbl_name1="testmembers";
$query="SELECT email, confirm_code FROM $tbl_name1";
			$result = mysql_query($query) or die ("Error in query: $query. " .
			mysql_error());
			while ($row = mysql_fetch_row($result)) { 
			 foreach ($row as $whatsthere){
echo $whatsthere.'<br>';
}}
echo "Newsletter sent";

?>

gives me on the screen as expected:

email address 1
confirm code 1
email address 2
confirm code 2

So it seems the foreach loop is needed - I just can't figure out how to write it so that I can call up the email and corresponding confirm code as I write the email handling code. I have a feeling I am almost there and will have a 'doh!' moment when I see the solution.

[rulian]

because again, you are using mysql_fetch_row
fetch_row outputs numerical indices for the array
fetch_assoc wil output your fields as index names
so $row['email'] will not exist in a fetch_row, only fetch_assoc
if you really want to stick with fetch_row, you can still avoid doing the for each
by using $row[0] for email, $row[1] for confirm_code
the problem with the foreach loop is that it will treat the email and confirm code as the same thing, $whatsthere, and you wont be able to distinguish between the two.

Just use $row[0] and $row[1] to get then information out

[rulian]

basically, the reason we use mysql_fetch_whatever is to avoid doing a walkthrough, since while is already an iteration
, so :

while($row=mysql_fetch_row)
{
// $row[0], $row[1]
}

is the same as

 
for ($i=0; $i<mysql_num_rows($query); $i++)
{
$email=mysql_result($query, $i, 'email'); 
$confirm_code = mysql_result($query, $i, 'confirm_code'); 
}

isnt mysql_fetch much prettier?

[kiwibrit]

Bingo!🙂
Thanks to you and laserlight for your patience.