PHP Mailer & Database Submit

[SalientAnimal]

Hi All....

 

I am in some desprate need of help. I have tried searching google for answers but just can't seem to find a solution that works. My problem is as follows:

 

1. I have a form that submits to multiple databases (Currently 4, but this number can become even more) WORKING

2. Each database has different action points (i.e. different users us and alter the information using an update form) WORKING

3. I need a "trigger" that will not only sumbit to the required database, but also send an e-mail to the parties that have an action to be performed. NOT WORKING  I have no idea how to do this.

4. Even though there is no data, besides the reference number to be submitted to a particular database, it is still submitting a entry with only the randomly generated reference number. Is there a way to prevent this from happening?

 

Thanks,

 

Code to my current Submit Page

<?php
$con = mysql_connect("localhost","root","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("databasename", $con);



$sql="INSERT INTO staff_churn
(username
,reference
,champ
,churn_type
,effective_date
,department
,exit_department
,position
,contract_type)

VALUES
('$_POST[username]'
,'$_POST[reference]_$_POST[lname]'
,'$_POST[fname] $_POST[lname]'
,'$_POST[churn_type]'
,'$_POST[effective_date]'
,'$_POST[department]'
,'$_POST[exit_department]'
,'$_POST[position]'
,'$_POST[contract_type]'
)";


if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }


  
  

$sql="INSERT INTO churn_access
(reference
,access_card
)

VALUES
('$_POST[reference]_$_POST[lname]'
,'$_POST[access_card]'
)";


if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  } 





$sql="INSERT INTO churn_drc
(reference
,organogram
)

VALUES
('$_POST[reference]_$_POST[lname]'
,'$_POST[direct_report]'
)";


if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }  
  
  
  
  
  

$sql="INSERT INTO churn_it
(reference
,champ_portal
,pc
,email
,phone
,lan
,g_folders
,h_folders
,distribution
,clarify_access)

VALUES
('$_POST[reference]_$_POST[lname]'
,'$_POST[champ_portal]'
,'$_POST[pc]'
,'$_POST[email]'
,'$_POST[phone]'
,'$_POST[lan]'
,'$_POST[g_drive] $_POST[drive_a] $_POST[drive_c] $_POST[drive_e] $_POST[drive_g]'
,'$_POST[h_drive] $_POST[drive_b] $_POST[drive_d] $_POST[drive_f] $_POST[drive_h]'
,'List: $_POST[list_a] $_POST[list_b] $_POST[list_c] $_POST[list_d] $_POST[list_e] $_POST[list_f] $_POST[list_g] $_POST[list_h]'
,'Clarify Level: $_POST[clarify_access]'
)";





if (!mysql_query($sql,$con))
{
  die('Error: ' . mysql_error());
}
  else
{
echo "<b><font color='white' face='segoe' size='2'>Your reference number is: $_POST[reference]_$_POST[lname]. Please record this number for future reference.</b>";
include "redirect_churn.html";
}
mysql_close($con)
?> 

 

Code to the Post Function

<form id="churnform" name="churnform" method="post" action="submit_churn.php" onSubmit="return validateForm(churnform);">

[btherl]

What do you mean by "trigger"?  What kind of conditions trigger the email?

 

For #4, you can check the data before submitting.  For example:

 

$validate_failed = false;
$validate_message = '';

if (trim($_POST['username']) == '') {
  $validate_failed = true;
  $validate_message .= "Username is missing<br>";
}

 

And so on for each variable that is required to be non-empty.  At the end of the validation you check if $validate_failed is true - if it is, then you display the message and the form again.  If it's false, then you submit the data to the database.  If you want you can do stricter checks, such as "username must contain letters and numbers only".

 

You could also do the validation in javascript before submission, which is more user friendly.

[SalientAnimal]

I have all the validations in place that insure that setain information is captured on the form, however the problem is not the validation part but rather that at times cerain areas may NOT have any tasks to action. My database structure as an example is:

IT_Access

General_Access

Userinfo

 

General Access will only have to perform a taks when the user needs an access card as an example. However if the user is only updating their details they won't need a access card. In this example when completing the form I only want the entry to be written to the IT_Access and Userinfo tables, the General_Access should not have ay data written to it at all. Currently the reference number is being written to all table each time the form is submitted. Reason is I'm using the Reference number as the common field to join my tables at a later stage in my queries.

 

 

By Trigger I basically mean that when I hit the form SUBMIT button, the data needs to be written to the individual tables as required and then at the same time to send an e-mail to the areas that need to action a request... So as in the above example...

 

If General Access Administrators has no actions they should not recieve an e-mail, only IT Access and Userinfo Administrators should recieve and e-mail advising them of a task that needs to be completed.

 

In the event that all Administrators have an action to complete they should all recieve and e-mail containign the reference number.

 

Hope I'm making sense?

[SalientAnimal]

I managed to get this working to a certain extent. Not exacty the way I want it to, but its a fix for now.

 

I fixed it by adding this befor executing the query to the database:

 

$to = 'email@domain.com,email2@domain.com';
$subject = "New Change Request - Reference Number $_POST[reference]_$_POST[lname]";
$message = "
New Churn Request - $_POST[churn_type].

A Churn request has been logged for $_POST[fname] $_POST[lname].
Please log onto the churn management system via the link http://localhost and action the request accordingly.
";
mail($to, $subject, $message);

[btherl]

That makes sense.. it sounds like all you need to do is check who needs an email sent to them and send it?  How to do that depends on what your rules are for sending emails and where those rules are stored..

[SalientAnimal]

Hi there,

 

Yes, my mailing is now working. The problem I have now is identify who actually needs to recieve the e-mail and when.

 

Eg. email2@domain.com needs to receive the e-mail only when criteria matching them needs to be actioned, i.e systems access for example. In this case if any of the fields from query below would be selected in the form they need to get an e-mail, however if none of these fields were selected in the form then only the areas that had fields selected in the form would need to get the mail.

 

$sql="INSERT INTO churn_it
(reference
,champ_portal
,pc
,email
,phone
,lan
,g_folders
,h_folders
,distribution
,clarify_access)

VALUES
('$_POST[reference]_$_POST[lname]'
,'$_POST[champ_portal]'
,'$_POST[pc]'
,'$_POST[email]'
,'$_POST[phone]'
,'$_POST[lan]'
,'$_POST[g_drive] $_POST[drive_a] $_POST[drive_c] $_POST[drive_e] $_POST[drive_g]'
,'$_POST[h_drive] $_POST[drive_b] $_POST[drive_d] $_POST[drive_f] $_POST[drive_h]'
,'List: $_POST[list_a] $_POST[list_b] $_POST[list_c] $_POST[list_d] $_POST[list_e] $_POST[list_f] $_POST[list_g] $_POST[list_h]'
,'Clarify Level: $_POST[clarify_access]'
)";

 

Not sure if I am making any sense here?

 

Then the person who completes the form should also get an e-mail notify them that they have successfully logged a request. The users e-mail address is stored in a seperate database that is linked to their username used to log in.

 

Thanks again to everyone who is helping here. Really want to get this one working 100%

[btherl]

Yes that makes sense.  What you should do is find two types of tutorials and complete them:

 

1.  A tutorial on validating form data in PHP

2.  A tutorial on using MySQL from PHP

 

Once you experience with those two topics, you will be able to do what you want.

[SalientAnimal]

Thanks,

 

Can you perhaps recommend a palce where I could get these two tutorials from?

[btherl]

If you google for "php form tutorial" and "php mysql tuturial" you will find many.  I suggest you open up several of them, read through a bit of them and choose one that you like the style of.

[SalientAnimal]

Great, so I have managed to get the mailer to do the validation after quite a bit of reading and trial and error. So I have two more questions on this thread that I would like assistance with. I have the mailer working for two of my options, but the thrid isn't working... I think the reason for this is because I am trying to validate the mailer on a wildcard function (%) as the field here is a free text field.

 

 

My Code:

else if
	(
	$_POST['organogram'] == "%"
	)
{
$to = 'MYMAIL@MYDOMAIN.COM';
$subject = "New Change Request - Reference Number $_POST[reference]_$_POST[lname]";
$message = "
New Churn Request - $_POST[churn_type]. Action Required

  A Churn request has been logged for $_POST[fname] $_POST[lname].
  Please log onto the churn management system via the link http://localhost and action the request accordingly.
";
}  

 

How do I get the wildcard function to work here?

[btherl]

What are you trying to test there?  Do you want to see if there is any text in $_POST['organogram']?  That it's not empty?

[SalientAnimal]

Yes, I think that would be the best way to explain it. If the text field is empty then the owner of the organogram should not get an e-mail, however if it does have text in it then they must get and e-mail.

 

I spent the whole day yesterday working on the mailing code validations, and I think I might have too much code, but I will post that once I get this component working. Taking baby steps here :-).

[Rage]

Yes, I think that would be the best way to explain it. If the text field is empty then the owner of the organogram should not get an e-mail, however if it does have text in it then they must get and e-mail.

 

I spent the whole day yesterday working on the mailing code validations, and I think I might have too much code, but I will post that once I get this component working. Taking baby steps here :-).

 

You should probably be using javascript to check these but when the forum is submitted, before it mails them it checks if the variable has any value if it does it continues on and checks the next one

[SalientAnimal]

The Javascript validation is being done on the form on the required fields. The check I am now looking at doing is once all the form information has been submitting and is being processes by the PHP script. Basically what happens here is the following:

 

1. Data is being captured to 4 different tables in a  given database, also dependant on which fields were selected on the form...

2. Depending on the fileds selected/completed on the form only certain parties need to recieve the e-mail.

 

If I work through everything that btherl has been saying I know I'm on the right track, and the other functions appear to be working. The problem is because the organogram field is a free text field and not a predefined field so the validation determaining the e-mail doesn't have a set entry to look for, hence why I would need to use some kind of wildcard check.

[boompa]

Are you looking for something like

$organogram_is_set = isset($_POST['organogram']) && !empty($_POST['organogram']);

or are you saying you're trying to check if there's an email address in the organogram field? It's early, I'm a bit easy to confuse after only one cup of coffee.

[SalientAnimal]

Are you looking for something like

$organogram_is_set = isset($_POST['organogram']) && !empty($_POST['organogram']);

or are you saying you're trying to check if there's an email address in the organogram field? It's early, I'm a bit easy to confuse after only one cup of coffee.

 

I have to be honest, I'm not entirely sure how to use what you have suggested. Sorry, everything I have done here is self taught and a lot of trial and error so getting everything to wrok together is a bit of a challenge.

[SalientAnimal]

Hi

Are you looking for something like

$organogram_is_set = isset($_POST['organogram']) && !empty($_POST['organogram']);

or are you saying you're trying to check if there's an email address in the organogram field? It's early, I'm a bit easy to confuse after only one cup of coffee.

 

Basically what I am checking for in the PHP code is if there is any value captured in the Organogram field. If there is and kind of text captured in this field the "Owner" of organogram must be notified via e-mail that an action has been been logged for them to attend to. If however it is left blank, then they must not recieve anything. And the e-mailer must only notify the area's that have actions to attend to.

[redarrow]

do you mean something like this?

 

you would need to include this at the top of index page.php.

 

the code will always go off and find all the ones in the database then delete the 1 and put back as a 0 and then send a email to the users 1 it just changed.

 

might be bugs is that what you want?

<?php

$num=1;

if($num=="1"){

$db=bd_connection("localhost","username","password");

mysql_connect("database name",$db)or die.mysql_error();

$organogram=$_POST['organogram']);

if($organogram){

$select="Select table where organogram = '1'";

myslq_query=($select)or die.mysql_error(); 

while(delete_entry=mysql_fetch_assoc()){


$to      = '$delete_entry['email_address'];
$subject = 'There activity with your account';
$message = 'hello please check your account';
$headers = 'From: webmaster@example.com' . "\r\n" .
    'Reply-To: webmaster@example.com' . "\r\n" .
    'X-Mailer: PHP/' . phpversion();

mail($to, $subject, $message, $headers);

$update="table_name set organogram="0" where organogram = '$delete_entry'";

myslq_query=($update)or die.mysql_error();  

}
} 
  }
?>