2. PHPRunner General questions
  3. [SOLVED]   Send email to emailaddress from different Table
This topic is locked
[SOLVED]

  Send email to emailaddress from different Table

 		6/30/2012 10:51:11 AM
PHPRunner General questions
S
 shoppy author

I am working on this problem for some time now.
In the Table _Klant is a field called Reminder.

This field is connected to the table _Gebruikers (in the Visual Editor) and works with a pull down menu with all the users in _Gebruikers.
The Table _Gebruikers has the following fields:

GebruikersID

Gebruiker (the name of the user)

Gebruikersemail

Gebruikerspassword
Every night I run a cronjob on a php script that emails all the jobs for that day to one emailaddress.

But I want the emails to be send to the user that made the reminder.
I thought this code would work but it doesn't

$ontvangerEmail = "select Gebruikersemail from _Gebruikers where Gebruiker='".$_SESSION["GebruikersID"]."'";


If I use $ontvangerEmail = 'my@email.com'; it works fine.
Can someone help me please?
John
C
 		cgphp 6/30/2012

Are you sure the field in the where clause is Gebruiker and not GebruikerID? If the field in the where clause is an INT type, don't use the single quotes.
S
 		shoppy author 7/1/2012



Are you sure the field in the where clause is Gebruiker and not GebruikerID? If the field in the where clause is an INT type, don't use the single quotes.


Thanks Cristian but no change.

The script says that it has send the email but it doesn't.
C
 		cgphp 7/1/2012

Try to echo your query before to send the email message.
S
 		shoppy author 7/1/2012



Try to echo your query before to send the email message.


Can you give me a line how to do so?
C
 		cgphp 7/1/2012
echo "select Gebruikersemail from _Gebruikers where Gebruiker='".$_SESSION["GebruikersID"]."'";

exit();


and try also to echo the result of the query



$rs = CustomQuery("select Gebruikersemail from _Gebruikers where Gebruiker='".$_SESSION["GebruikersID"]."'");

$record = db_fetch_array($rs);

echo $record['Gebruikersemail'];

exit();
S
 		shoppy author 7/1/2012


echo "select Gebruikersemail from _Gebruikers where Gebruiker='".$_SESSION["GebruikersID"]."'";

exit();


and try also to echo the result of the query



$rs = CustomQuery("select Gebruikersemail from _Gebruikers where Gebruiker='".$_SESSION["GebruikersID"]."'");

$record = db_fetch_array($rs);

echo $record['Gebruikersemail'];

exit();



It doesn't echo anything.

I posted the whole thing to the Demo Page and will ask Jane to look at it.
Thanks Cristian
S
 		shoppy author 7/2/2012

I will show you how I build it.

The fields in Table _Gebruikers is shown above.

(Gebruiker means user in Dutch)


The script I call on is 'reminder.php'



<?php
$server = 'localhost';

$gebruiker = '???';

$wachtwoord = '???';

$database = '???';
global $conn;
(option 1) //$ontvangerEmail = 'my@email.nl';

(option 2) //$ontvangerEmail = "select Gebruikersemail from _Gebruiker where GebruikerID='".$_SESSION["GebruikersID"]."'";

(option 3) //$ontvangerEmail = "select Gebruikersemail from _Gebruikers where Key=".$values["GebruikersID"];
if (mysql_connect($server,$gebruiker,$wachtwoord)) {

if (mysql_select_db($database)) {

$query = "SELECT KantID, Naam, Opmerking, Reminder, datum FROM `_Klant` WHERE datum <= NOW()";

//$query = "SELECT KantID, Naam, Opmerking, Reminder, datum FROM `_Klant` INNER JOIN `_Gebruikers` ON `_Klant`.Reminder = `_Gebruikers`.Gebruiker WHERE datum <= NOW()";

$result = mysql_query ($query);
if ($result && mysql_num_rows($result) > 0){
require ('mail.class.php');
while($row = mysql_fetch_assoc($result)) {

$email = new email ('mailform.php');





$email->setMailServer ('localhost');



$email->setSubject (' (' . $row['Reminder'] . ') ' . 'Taak: ' . $row['Naam']);



$email->dynamicContent ('[[datum]]', $row['datum']);

$email->dynamicContent ('[[Reminder]]', $row['Reminder']);

$email->dynamicContent ('[[Naam]]', $row['Naam']);

$email->dynamicContent ('[[Opmerking]]', nl2br($row['Opmerking']));
if ($email->sendMail ('my@email.nl', $ontvangerEmail)) {

echo "Send ";

} else {

echo "Wrong ";

}

}

}else{

echo '<p>Sorry, I found no tasks!</p>';

}

}else{

echo '<p>Sorry, no connection with the database table!</p>';

}

}else{

echo '<p>Sorry, no connection with the databa seserver!</p>';

}
?>


As you can see I tried several options including an INNER JOIN option in the query.
If I use (option 1) $ontvangerEmail = 'my@email.nl'; the script works fine but all the emails are ofcourse send to just one emailaddress.

I like it to be send to the emailaddress of the one in the field 'Reminder' in _Klant.
John
C
 		cgphp 7/2/2012

Why don't you use PHPrunner built-in functions? There are a lot of examples in the user guide: http://xlinesoft.com/phprunner/docs/send_email_to_email_addresses_from_user_table.htm
S
 		shoppy author 7/2/2012



Why don't you use PHPrunner built-in functions? There are a lot of examples in the user guide: http://xlinesoft.com/phprunner/docs/send_email_to_email_addresses_from_user_table.htm


Because this one has to be called outside PHPRunner.

The email has to be sent when the user is not online.
C
 		cgphp 7/2/2012

You can use db functions outside PHPrunner. You need to include the dbcommon.php file to your external php scripts.
S
 		shoppy author 7/2/2012

I found the right query with some help ofcourse:



$ontvangerEmail = 'my@email.nl';


if (mysql_connect($server,$gebruiker,$wachtwoord)) {

if (mysql_select_db($database)) {
$query = "SELECT klant.KantId, klant.Naam, klant.Opmerking, klant.Reminder, klant.datum, gebruikers.Gebruiker, gebruikers.Gebruikersemail FROM `_Klant` as klant LEFT JOIN `_Gebruikers` as gebruikers ON (gebruikers.Gebruiker = klant.Reminder) WHERE klant.datum <= NOW()";
$result = mysql_query ($query);



if (! $result) {

echo mysql_error ();

}
if (mysql_num_rows($result) > 0){
// De class invoegen in het huidige bestand

require ('mail.class.php');
while($row = mysql_fetch_assoc($result)) {

// Een email object aanmaken.

// Door het object te koppelen aan een variabele (in dit geval $email) kun je er iets mee doen.

$email = new email ('mailform.php');



// De ontvanger instellen:

if (! empty ($row['Gebruikersemail'])) {

$ontvangerEmail = $row['Gebruikersemail'];

}


This script tries and to find the user email and if not it sends it to a general emailadres given in $ontvangerEmail.
For all those who can use it.
I run this via a Cronjob every morning, this way everybody gets an email with the task for this day.