Question on getting data from two tables

[survivorjoe]

Greetings,

I have 2 tables that I need to work on. From my Client table, I need to go through and look at the client email address for each client (unique email addresses stored in this table). For each client, I need to pull out their name, company name and phone number.

Next, I have to access the Subscriber table, where for each client email address, I access all the subscribers who they have added to their email list. In this table, I have the subscriber email address, plus their name.

For each subscriber email address, I need to send out an email message that contains their sponsor's info from the client table (client email address, name, company name & phone number). Plus, the email that goes out to the subscriber needs to have their name, which is stored in the Subscriber table.

Basically, it looks like this:

for each Client {
get Client email
get Client info
find all Client's Subscribers from Subscriber table
for each Subscriber {
get Subscriber email address
get Subscriber's name
do variable substitution in the email message
send out the personalized email to the Subscriber
}
}

What is the best way to structure all this?

 

[r937]

select C.client_email
     , C.client_name
     , C.company_name
     , C.company_phone
     , S.sub_first_name
     , S.sub_last_name
     , S.sub_email
  from Client as C
inner
  join Subscriber as S
    on S.client_email = C.client_email
   [blue]and S.portal in ('realtor','planner')[/blue]
order
    by C.company_name
     , C.client_name
     , S.sub_last_name
     , S.sub_first_name

the part in blue could also be written as a WHERE clause

r937.com | rudy.ca

 

[survivorjoe]

You are a blessing to all novice coders! I have used your other solution to get my problem solved and to learn more about how to write proper code.

- Joe

 

[echristm]

I am actually working on a similar project. I tried to modify the code listed for my appication but if failed. The original code:

// show online member names
if ($showmode > 0) {
$query = "SELECT DISTINCT a.username"
."\n FROM #__session AS a"
."\n WHERE a.guest = 0"
."\n ORDER by a.username"
;
$database->setQuery($query);
$rows = $database->loadObjectList();

if ( count( $rows ) ) {
// output
$output .= '<ul>';
foreach($rows as $row) {
$output .= '<li>';
$output .= '<strong>';
$output .= "<a href=\"aim:goim?screenname=".$row->email."\"><b>".$row->username."</a></b>";
$output .= '</strong>';
$output .= '</li>';
}
$output .= '</ul>';
}
}

echo $output;


What I need to do is somehow pull the email address from the jos_users table so it will populate into the AIM link.

Any suggestions?

 

[r937]

what is the relationship between the #__session table and the jos_users table? which columns would you match rows on?

r937.com | rudy.ca

 

[echristm]

as best I can tell thus far, the #__session table is pulling the logged in users from the primary jos_users table and storing that information. All in all, all the user details are intially being pulled from the jos_users table. I need to match up the username column from #__session to the email column in jos_users. The jos_users table also has a column named username, which is where #__session is getting it's data from initally. I hope this is clear enough. I am working with an "out of the box" CMS system that myself and a coworker have modified for our department needs as we go. -- Thanks

 

[r937]

if the #__session table is populated from the jos_users table, why wouldn't the email address be in there too?

i would trace back to where this #__session table is created, and make sure

r937.com | rudy.ca