Nested loops

[rubino]

Hi to u all, I'm using a SELECT that through a JOIN extracts a recordset like that:

username, email, favourite hobby

where each user has many hobbies.

So for example output could look like this:

Paul, [e-mail], fishing
Paul, [e-mail], chess
David, [e-mail], art
...

I need to compose 1 e-mail for each user, resuming all his hobbies. So I need to send one e-mail to Paul telling him:

Hi Paul, your hobbies are:
1. fishing
2. chess

I'm wondering how to work this out: we are talking of about 5000 user, so I think it would be a stress for mySQL to create a SELECT and then inner SELECT for each username!

As an old ASP developer I was used to putting Recordset results in two arrays, and then use nested loops...how can I manage this in PHP?

Thanks in advance, hope this all makes sense and someone can give help!

[Kudose]

rubino wrote:

So I need to send one e-mail to Paul telling him:

Hi Paul, your hobbies are:
1. fishing
2. chess

...ok

Just as an FYI, you may get spam blacklisted for sending 5000 emails in an hour. I think somewhere around 250 per hour is the safe mark.

$SQL = mysql_query("SELECT username, email, hobbies FROM table");
$rows = array();
while($row = mysql_fetch_array($SQL)){
  $rows[] = $row;
  mysql_free_result($SQL);
}
foreach($rows as $row){
  $to = $row['email'];
  $subject = 'Forget you\'re hobbies?  Never fear!';
  $headers = "FROM: Timmy! <[e-mail]>\r\n";
  $body = "Hi {$row['username']}, your hobbies are:\n\n";
  $hobbies = explode(",", $row['hobbies']); //assuming they are CSV
  for($i=1;$i<=count($hobbies);$i++)
   $body .= $i." ".$hobbies[$i];
  if(mail($to,$subject,$body,$headers))
   echo 'Mail sent to '.$row['email'].'<br />';
  else
   echo 'Mail NOT sent to '.$row['username'].'<br />';
}

Need something like that?

[rubino]

that's just what I need Kudose!

Some considerations/questions:

1. didn't know about this:

   $rows[] = $row;

2.Surprised by this
explode(",", $row['hobbies'])
I can explode $row['hobbies'] so easily!

3. Wanna ask about this:

mysql_free_result($SQL);
Shouldn't go after the "}"?

4. Is it useful in general to mysql_free_result() when you are dealing with many records or u think I'm too much worried for just 5000 records, as they are not so stressing for MySQL?

Tnx for your advice about spamming risk, actually I'm going to launch mail sending at different hours!

[Kudose]

1. Basically all I did was add the result of $row to the next key available to the $rows array.
2. I was excited when I learned about this function too.
3. Doesnt matter -- the way I wrote it it frees memory after ever $row -- your way does it after all $rows are set.
4. Since you are the only one using it, it shouldnt be a big deal. But if many people were accessing 5k records, then you might need to stress.

[laserlight]

3. Wanna ask about this:

mysql_free_result($SQL);
Shouldn't go after the "}"?

Yes, it should go after the while loop, if you want to use it at all.

[rubino]

1st of all: I'm learning a lot from u and from this website, thank u so much.

I'm trying to apply Kudose code to my web page, and maybe I'm wrong but:

will it manage the nested loop I need for my app? I mean, I seem to understand that it will loop through users, and then loop through hobbies...BUT:

1. In this way (remember the INNER JOIN result) a single user will receive one mail for each entry with his name, won't he/she?

2. I can't understand where in the code above I'm checking that all the hobbies (inner loop) I write are referred to the user I'm sending my mail to. Is there something I should consider?

[Kudose]

Will you marry me laserlight?

😉

Post up your DB scheme please rubino

[rubino]

hi kudose & laserlight (in love), my db scheme is complicated, but to make it simple:

USER TABLE
id_user, username, email

HOBBY TABLE
id_hobby, ext_user, hobby_name

SQL makes INNER JOIN between the two tables ON id_user = ext_user

The result would look like:

Paul, [e-mail], fishing
Paul, [e-mail], chess
David, [e-mail], art
...

So I should need a sort of nested loop I guess...I guess 🙂

[laserlight]

This code isnt fully correct, but it is an idea:

$query = "SELECT u.username, u.email, h.hobby_name
	FROM usertable u, hobbytable h
	WHERE u.id_user=h.ext_user
	ORDER BY u.username";
$result = mysql_query($query);
$users = array();
while ($row = mysql_fetch_assoc($result)) {
	if (array_key_exists($row['username'], $users)) {
		$users[$row['username']][1][] = $row['hobby_name'];
	} else {
		$users[$row['username']] = array($row['email'], array($row['hobby_name']));
	}
}

foreach ($users as $user => $details) {
	$hobbies = implode(', ', $details[1]);
	// send mail to $details[0]
}

[rubino]

...tnx laserlight let me understand:

u create an array $users, and than you populate it OK

if user exists
$users[$row['username']][1][] = $row['hobby_name'];

otherwise
$users[$row['username']] = array($row['email'], array($row['hobby_name']));

In the end you implode the array using "," but can u explain this if/else pliz, I mean the way u populate array: I'd like to learn not just copy it!

Thank u so much, I've reached the result my way, but it's a very messy code compared to yours 🙂

[laserlight]

In the end you implode the array using "," but can u explain this if/else pliz, I mean the way u populate array: I'd like to learn not just copy it!

Well, in this case just read up on [man]implode/man in the PHP Manual. It takes the array elements and joins them together to a string, using ', ' as glue (in this case).

[rubino]

tnx laserlight, implode() function is clear, I just need some explanation on the line in the IF/ELSE...in both case I seem to understand that you are inserting something in the array, but the construction u use is not clear to me.

Can u say it more explicitely please? 🙂

[Jack_McSlay]

what he does is storing the e-mail in the key 0 of each user, and the e-mails in the key 1. i personally would prefer like this:

    if (array_key_exists($row['username'], $users)) { 
        $users[$row['username']]['hobbies'][] = $row['hobby_name']; 
    } else { 
        $users[$row['username']] = array('email' => $row['email'], 'hobbies' => array($row['hobby_name'])); 
    } 

and then use 'email' and 'hobbies' rather than keys 0 and 1

[Kudose]

I never looked at laserlights profile, but I am assuming from "PHP Witch" laserlight is a she ... not a he. 😉