campaings and email , logs tables design

[mangel.murti]

Hi all,

i attached my campaing field with img.i want to design tables such as when adding a campings and by trigger mode selected by user based on that email should be fire frm db.

i want guidance how to design or process it . confusing my side....

any help appre..

thnx

[rotten69]

You need to be more specific with the thing you need help with.

[mlesniak]

As Rotton69 said, you need to ask a specific question, otherwise you will not get any answers. I guess that you wish to store information about a number of campaigns and that when a new campaign is created, you would like the database engine to send an email. I think that this is possible. I think you would need to have tables with the 'New Contact Types" and Triggers in so that you can populate the Combo box and list box controls you appear to have in your snapshot.
I'm not familiar with emailing from MySqQL but I know that you can email on inserting a new record in Microsoft SQL Server, by creating a trigger.

[mangel.murti]

You need to be more specific with the thing you need help with.

acctu , i have tables for campaings below
see attache files , i need some guidance for table desing..

1 add_campaign---where camps add
2.crm_type_campaign---> 1.Action Plan 2. Drip Campaign
3.crm_triggers_type_campaign--->disable mode , bydate , by contact

disable mode--->
By "disabling" the trigger you will have to manually add subscribers to your campaign. The campaign start date will be 1 day after you subscribe your contact to this campaign.
i need guidance for tables designs....how its gonaa work....

bydate- >>>
By selecting "by date" as your trigger you still have to manually add subscribers to this campaign; however you will be first prompted to enter a date before this campaign is scheduled. This is ideal for campaigns that are time sensative and must happen on a specific date. For example if you have a campaign that is based on a "Closing Date" then you will enter "Please enter closing date" below. When you subscribe a contact to this campaign they will be asked to enter the closing date and all actions will be scheduled according to that date.

--->bycontact
By selecting "by contact type" as your trigger, a newly added contact will be automatically subscribed to your campaign as soon as they are added to the database. Please note that the new contact type(s) must match ALL the types you select below. For example you can select Prospect & Buyer below and if you add a new contact to your database and check both Prospect and Buyer as the type they will be subscribed to this campaign as soon as you add them to the database.

thnx

[mlesniak]

Firstly, I do not think that MySQL can generate email messages, so that functionality will have to come from elsewhere. If you have access to Microsoft SQL Server then that would be a good place to go. If you want to go the low cost route then you could try using PHP on a web server to send the emails.
You can get MySQL to do certain things automatically by creating a trigger.
The trigger could call a stored procedure. So what you would do is create a stored procedure such as SendMessage to which you would pass the various items of data to include in the mail message. Example procedure:-

-- Create Procedure NewCampaign
delimiter //
CREATE PROCEDURE NewCampaign (OUT param1 VARCHAR(30))
	BEGIN
		SELECT CURRENT_USER() INTO param1 FROM Campaigns;
	END;
	//
	delimiter ;

You would then create a trigger on the table that is fired AFTER a row has been inserted. This trigger would then call the SendMessage procedure. Example trigger:-

-- Create a trigger
CREATE TRIGGER newcamp AFTER INSERT ON campaigns
	CALL NewCampaign(@result);

I am not saying that my examples will work. They are just a guide line as to how I would go about it.
It would be a good idea to create a mailing table with the following columns in:-
From email address
To email address
Subject of the email
Body text of the email.
The table does not have to be permanent.
I'm not sure how you would go about interfacing to PHP to send the message but I'm sure you can ask on the PHP forum. My PHP knowledge is not very extensive.
If you can generate a text file like the following, you should be most of the way there:-

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
	<title>EmailTest01</title>
</head>

<body BGCOLOR=#0099FF>

<!-- This script is designed to be a simple email test -->

<?PHP 
print ("<CENTER><h3>Test email using PHP.</h3></CENTER>");

?>

<?PHP
$MailTo = "[e-mail]";
$Subject = "A test message";
$Body = "This message was sent automatically using PHP.";
$MailFrom = "[e-mail]";

		if($MailTo)
		{
			if(mail($MailTo, $Subject,$Body,"From: $MailFrom"))
			{
				print("<B><CENTER><FONT COLOR=BLUE>Your email has been successfully sent!</FONT></CENTER></B>\n");
			}		
		}
		else
		{
			print("<B><CENTER><FONT COLOR=RED>Please enter the recipient's mail to address!</FONT></CENTER></B>\n");
		}

?>

</body>
</html>

Save the file with a php extension and invoke it on a web server.
You may be able to do it more simply from the command line, I'm not sure.