Quick question for you ...

[chayden]

Hey;

I have quick question for you. Would you happen to know how to pull VALUES from one MySQL table, and VALUES from a second MySQL table, then merge them together as to personalize an email message?

What I'm writing is a membership script. I'm allowing users to sign themselves up on their own. Their information is then placed in a MySQL table called 'users'. The second part of this is allowing the admin to add the email response that goes out to the new user with their "account details" in a second table called, 'emails'.

What I'm having problems with is converting the user information from the 'user' table, into it's actual VALUE when I merge it with the email respones in the admin table, 'emails'.

Here's what I have coded so far.

---

 if($sendmail == "yes")
 {
 $admin_result=mysql_query("SELECT * FROM $admin_tbl WHERE id='2'");
  echo mysql_error();
 $admin=mysql_fetch_row($admin_result);

     $admin_name="$admin[1]";
     $admin_mail="$admin[2]";

 $email_result=mysql_query("SELECT * FROM $email_tbl WHERE id='2'");
  echo mysql_error();
 $mail=mysql_fetch_row($email_result);

     $thanks_mail_subject="$mail[1]";
     $thanks_mail_body="$mail[2]";
     $order_mail_subject="$mail[3]";
     $order_mail_body="$mail[4]";

 $user_result=mysql_query("SELECT * FROM $user_tbl WHERE id='$id'");
  echo mysql_error();
 $user=mysql_fetch_row($user_result);

     $id="$user[0]";
     $fname="$user[1]";
     $lname="$user[2]";
     $email="$user[3]";
     $epref="$user[4]";
     $optin="$user[5]";
     $username="$user[6]";
     $password="$user[7]";
     $password2="$user[8]";
     $affid="$user[9]";

 $thanks_email="".CleanSlashes($email)."";
 $thanks_subject="".CleanSlashes($thanks_mail_subject)."";
 $thanks_body=FormatText("".CleanSlashes($thanks_mail_body)."");
 if(@mail($thanks_email, $thanks_subject, $thanks_body.
 "",
 "From: \"".CleanSlashes($admin_name)."\" <".CleanSlashes($admin_mail).">\n".
 "Reply-To: \"".CleanSlashes($admin_name)."\" <".CleanSlashes($admin_mail).">\n".
 "Return-path: \"".CleanSlashes($admin_name)."\" <".CleanSlashes($admin_mail).">"));

 $admin_email="".CleanSlashes($admin_mail)."";
 $admin_subject="".CleanSlashes($order_mail_subject)."";
 $admin_body=FormatText("".CleanSlashes($order_mail_body)."");
 if(@mail($admin_email, $admin_subject, $admin_body.
 "",
 "From: \"".CleanSlashes($fname)." ".CleanSlashes($lname)."\" <".CleanSlashes($email).">\n".
 "Reply-To: \"".CleanSlashes($fname)." ".CleanSlashes($lname)."\" <".CleanSlashes($email).">\n".
 "Return-path: \"".CleanSlashes($fname)." ".CleanSlashes($lname)."\" <".CleanSlashes($email).">"));
 }

---

What I've been trying to do is add in the MySQL table for 'emails' are things such as ...

$fname, thanks for signing up ...

... for the subject line. But it then prints exactly that. I'm unable to replace $fname with the VALUE from the 'user' table.

Any suggestions would be greatly appreciated!

Chayden -

[killerbishop]

Try:

SELECT * FROM $admin_tbl,$email_tbl,$user_tbl WHERE $admin_tbl.id = 2 AND $email_tbl.id = 2 AND $user_tbl.id = 2

This called a JOIN. For information about joins, see:

http://www.mysql.com/doc/en/JOIN.html

[chayden]

Patrick,

Thanks for the heads up. I'm about to head over there and check it out. See how well it does for me.

On a side note, I finally did figure something out. Here's what I used:

---

 $email_result=mysql_query("SELECT * FROM $email_tbl WHERE id='1'");
  echo mysql_error();
 $mail=mysql_fetch_row($email_result);

     $mail_body="$mail[1]";

 $user_result=mysql_query("SELECT * FROM $user_tbl WHERE id='$id'");
  echo mysql_error();
 $user=mysql_fetch_row($user_result);

$patterns = array (".user_id.", ".user_fname.", ".user_lname.", ".user_email.");

$replace = array ("$user[0]", "$user[1]", "$user[2]", "$user[3]");

$text = preg_replace ($patterns, $replace, "$mail_body");

---

That way, anything entered into my database for the subject and body of my email message using "user_" in front of the value, will then print the correct user information.

user_fname = John
user_lname = Doe
user_email = [e-mail]

This is just a little FYI for anyone with a similar question.

Thanks again, Patrick. ;~)

Chayden -