[RESOLVED] Help required! Three tables! Email!

[bo0]

I am now very frustrated, as I have been trying for the last few hours to code a script to no success.

I have three tables:

ufJoin
users
feeds

In “ufJoin” I have the following: joinid, userid and feedid.

My script should look in “ufJoin” and for each userid it finds do the following:

• Look in the users table and grab the information stored in the email column
• Look in the feeds table and grab the information stored in the url column

When it has done that it should email every user with every url under their id.

Thanks guys.

[laserlight]

// retrieve those userids that have a corresponding entry in the ufJoin table
$query = "SELECT users.userid AS userid, users.email AS email
	FROM users, ufJoin
	WHERE users.userid=ufJoin.userid
	GROUP BY users.userid";
$result = db_query($query);

// deal with each user separately
while ($row = db_fetch_assoc($result)) {
	// retrieve those urls that correspond to this userid
	$url_query = "SELECT feeds.url AS url
		FROM feeds, ufJoin
		WHERE ufJoin.feedid=feeds.feedid
			AND ufJoin.userid={$row['userid']}";
	$url_result = db_query($url_query);

// generate list of urls
$urls = '';
while ($url_row = db_fetch_assoc($url_result)) {
	$urls .= $url_row['url'] . "\n";
}

// email the list
mail($row['email'], 'Urls', $urls);
}

[bo0]

Ok, thanks.

Can I ask what function "db_fetch_assoc" is?

[bo0]

Nevermind, I worked it out!

Thank you for your help!

[bo0]

Hi laserlight,

I am curious. Would it be possible to take every URL, run a "function" on it and then email?

[laserlight]

Would it be possible to take every URL, run a "function" on it and then email?

Of course, since the URL is available as $url_row['url'] within the inner while loop.

[bo0]

With $url_row being an array, how do I select each URL individually?

[bo0]

I got it🙂