2. PHPRunner General questions
  3. [SOLVED]  Email Question
This topic is locked
[SOLVED]

 Email Question

 		10/27/2009 11:45:11 PM
PHPRunner General questions
M
 mid3vil author

So after some banging my head on a wall, and then searching forms I haven't been able to find a solution to satisfy my problem.
I have the following tables:

Trouble_tickets - with fields :user_id, ticket_closed

users - with fields: user_id, supervisor(int value that corresponds to supervisors->supnum)

supervisors - with fields: supnum, superEmail
after an edit on trouble_tickets, if ticket_closed == 1, i want to send an email to superEmail, for the supervisor of the user_id in trouble_tickets.
Any idea how this might be accomplished after update?
J
 		Jane 10/28/2009

Hi,
use After record updated event on the Events tab for this purpose.

Here is a sample:

$str = "select superEmail from supervisors inner join users on supervisors.supnum=users.supervisor where users.user_id=".$values["user_id"];

$rstmp = CustomQuery($str);

$datatmp = db_fetch_array($rstmp);
//send email here

$email = $datatmp["superEmail"];

$from="admin@test.com";

$msg="Hello there\nBest regards";

$subject="Sample subject";

$ret=runner_mail(array('to' => $email, 'subject' => $subject, 'body' => $msg, 'from'=>$from));

if(!$ret["mailed"])

echo $ret["message"];
M
 		mid3vil author 10/28/2009

Coming up with the following
I should also mention that I am using PHPRunner 5.0
Error type 256

Error description You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to

use near '' at line 1

URL www.domainname.net/Open_Tickets_edit.php?

Error file /home/content/t/s/m/tsmcorp/html/include/dbconnection.php

Error line 34

SQL query select superEmail from supervisors inner join users on supervisors.supnum=users.supervisor where users.user_id=

Solution This is a general error. It occurs when thereis an error in event code or in SQL.
J
 		Jane 10/29/2009

Hi,
please make sure you've replaced user_id with your actual field name. Field names are case-sensitive here.

Also you can check if this value is not empty:

if ($values["user_id"])

{

$str = "select superEmail from supervisors inner join users on supervisors.supnum=users.supervisor where users.user_id=".$values["user_id"];

...

}
greggk 11/6/2009

I have a very similar query that I'm trying to do.

My tables are:

Site with fields siteID userID

Status with fields StatusID siteID

User with fields userID emailAddress
From the Status page I want to select the email address that corresponds to the Site and Status
From your query example, this is what I came up with
$str = "select emailAddress from User inner join Site on User.userID=userID AND inner join Status on Site.siteID=siteID where Site.siteID=".$values["siteID"];
I run it and I get the following error

Error type 256

it gives me the SQL Query:

select emailAddress from User inner join Site on User.userID=userID AND inner join Status on Site.siteID=siteID where Site.siteID=3
Do I have the formatting correct on the sql query?
D
 		Dale 11/6/2009

Just a quick stab at this for you,

you had

$str = "select emailAddress from User inner join Site on User.userID=userID AND inner join Status on Site.siteID=siteID where Site.siteID=".$values["siteID"];

Try

$str = "select emailAddress from User inner join Site on User.userID=userID AND inner join Status on Site.siteID=siteID where Site.siteID=".$values["siteID"]."";
greggk 11/6/2009



Just a quick stab at this for you,

you had

$str = "select emailAddress from User inner join Site on User.userID=userID AND inner join Status on Site.siteID=siteID where Site.siteID=".$values["siteID"];

Try

$str = "select emailAddress from User inner join Site on User.userID=userID AND inner join Status on Site.siteID=siteID where Site.siteID=".$values["siteID"]."";


I tried that, and it gave me the same error. I'll keep trying different things, if I solve it I'll post it. I'm sure it's something simple again. I know I'm SO close to it, haha.
greggk 11/6/2009

Ok, I did this in a much easier way, at least for me it was.
I used phprunners own query designer and linked the Site and User to the Status table. Then I added the emailAddress as an alias from User to Status.
Then I only have to use $values=["email"] and I select the email properly.
I can't believe it was as easy as that. At least in my case the query designer in phprunner itself was the best way to do it, I didn't have to manually create an sql query to get to the emailAddress <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=45239&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' />
E
 		elliespotter 11/7/2009



Ok, I did this in a much easier way, at least for me it was.
I used phprunners own query designer and linked the Site and User to the Status table. Then I added the emailAddress as an alias from User to Status.
Then I only have to use $values=["email"] and I select the email properly.
I can't believe it was as easy as that. At least in my case the query designer in phprunner itself was the best way to do it, I didn't have to manually create an sql query to get to the emailAddress <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=45243&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' />


My first contribution to the forum - How I solved it and it should work generally <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=45243&image=2&table=forumreplies' class='bbc_emoticon' alt=':rolleyes:' /> the code went in to the AfterUpdate Event

// ********** Send confirm email ************

$str = "select usermail from my_users where my_users.username= '".$_SESSION["UserID"]."'";

$rstmp = CustomQuery($str);

$datatmp = db_fetch_array($rstmp);

$email=$datatmp["usermail"];

$from="noreply@mysite.com";

$msg="Thank you for your message requesting a reservation at our lodge.\n";

$msg .= "A member of staff shall email you shortly with further information about your request.\n";

$msg .= "\nBest regards\nYour friends at My Site.";

$subject="Reservation request";

$ret=runner_mail(array('to' => $email, 'subject' => $subject, 'body' => $msg, 'from'=>$from));

if(!$ret["mailed"])

echo $ret["message"];


I hope this helps,

Alex