Build a Newsletter System With PHP and MySQL

Today, we are going to be building a newsletter system using PHP with a MySQL background. The tutorial will cover building a system that allows for multiple newsletter lists and the sending of messages to specific lists.

Hey guys, so we are going to be building a pretty complex newsletter system, so lets get started! You are going to need two resources for the project.

Step 1: Create the Application Skeleton

When starting any project, I like to layout the folders before I start coding, so lets do that now. First, create the overall project directory. Next, create a folder named admin within your project folder. Then inside the admin folder, add two folders names media and swift. Finally, create a folder named images inside the media directory. Also, you can place the Swift lib folder inside the swift folder we created. You can also copy over the six silks icons we will be using:

• bullet_green.png
• bullet_red.png
• delete.png
• email_go.png
• find.png
• page_edit.png

I am going to structure the rest tutorial on creating the CRUD: create, read, update, and delete for four of our six models. One other will be edited by other actions, and the sixth we will not be creating the CRUD actions.

Now let's first create our database, and our tables we be created progressively later. We are now going to start coding. Also, as a word of note, I will assume that we will be working with files in our admin folder, unless I otherwise specify, as most of code be in this folder.

Step 2: Application Configuration

Every application will have some type of configuration file, and we are going to create ours now. Go ahead and create a file named config.php and add the following:

So the first section sets up our database variables, so make sure you edit it so it firsts your local configuration. Our next blurb sets up some email attributes we will use later. The last section starts up our session so we can access it, requires our classes.php file (we'll create that in just a second), sets the defaults for some layout options, and then sets error reporting to 0 to stop annoying warnings. However, if you seem to be having trouble, try commenting out this line.

That is a huge chunk of code, and I'll go through it. Our first function handles when the user posts our login, and then sends the data to our check_username_and_pw function. Our logged_in function simply returns whether a user is logged in. Our login_required function checks to see if we are logged in, and if not, sends us to the login page.

The next function simply named query() preforms a query on our DB, and was created by Jeffrey Way. I added the link creation and close functions to make it even easier. Our next function I created especially so we can easily run COUNT SQL queries, and our check_username_and_pw function checks to see if we can find a user with the same email and MD5 hashed password, and if so, sets our session variables.

Step 3: Application Layout

Now, we are going to create our index.php page just so we can style. Open up index.php and add:

So in this, we require our config file, set up our title, and then sets up our content variable, and then last requires our layout file. When you first open it up it should look like:

Now open up our style sheet. I like to use the 960.gs reset and typography styles, compressed in TextMate. So the top of my CSS file looks like:

Let's first style our main elements, so add the following styles:

Now your page should look like:

Now if we style the tabs with a nav background and then a hover background on each of the links and you should see:

Now, while we are working on the file, go ahead and add the following styles for our mini layout, form inputs, tables, large links, and our error & success messages.

Those are some styles that I like to use across all my projects. Now that we have finished the layout, we are going to continue onto authentication.

Step 4: Authentication

We are going to be working with a very simple authentication system. Create a login.php and place the following inside:

I'll explain each part of the code. First we require our config file. Next we check to see if we are logged in, and if we are, we redirect home. Next we set the title, and our layout options. Then we check to see if we have a POST and if the POST had a username and password, and if so, we call the validate_user function from our classes file. We next set the variable error to our session errors, and then we set up our form and output any errors. Now we are going to create our logout page, so create logout.php and put the following in it:

We again require our config file, set our session to an empty array, then destroy our session and redirect to our login page. Now that you have done all this, your login page should look like:

We are also going to create a user record (and our table) so that we can add in the authentication logic and you can view the pages with the code up ahead. To create a user with the username of admin and a password of secret. To add this, run this SQL:

I engineered this application to be very flexible. I wanted you (the user) to be able to create and manage as many newsletters as you want. So, first, we need to create our database table. Here is the SQL code from the export on my demo application:

So this file has the same basic feel of our login page. We require our config, make sure we are logged in, set our title. Next we use our query() function to perform a SELECT query to find all of our newsletters. We then set the current tab for our layout. After we loop through the array returned by our query, and create the the table layout. Then we call a yet-unknown function, and create our page. Before you will be able to view the page, you will need to add the following to your classes.php file to easily handle our error messages:

While you probably will not have any data, when you have a few records it will look like: (though a bit less squished)

Now we are going to work on our new action, so open up newsletters_new.php and add the following:

So I hope you have noticed the pattern at the top of each of our files. We first require our config.php file, then make sure we are logged in, and then set our current tab, and we then add some extra logic for handling POSTs and then we set our title, our content, and then render the page. The POST section is fairly simple to understand so I'll explain it quickly.

We first check to see if an item with the name of submitted was sent. This is the hidden field we have after the submit button. Next, we create a link to our database using the variables from our config file. Next we create our SQL insert query, using our POSTed variables. Next we query (not our function) the database, and if an error is raised, we show the error returned. Next, we close the query, then we set our success message, and then redirect to the listing page. Your page should look like:

Next we are going to work on our edit page and add the following:

Just like all of our files, we start off with the same block. After our POST block (which I will talk about in a second), we set id to our requested id, making sure it is an integer. We then use our query function to find the newsletter we are working with, and sets a few variables to the returned results. You may ask why we put a [0] before we requested each value, and the reason is the query function returns an array of all the records, and each record is an array, so we need to access the first array in our results variable. The line where we set the variable visible ifs actually a compressed if/else statement. the if part is the == 1, then if that is true, the variable is set to checked, else to nothing. Then we have our form.

Our POST block is very similar to our new page, and will always start the same way across the rest of our pages. We then check to see if the check box was check, and set a variable here again. Then we have our UPDATE query, again run the query, set our success message, and then redirect home. THis is how the form looks and the message seen after editing:

The last page for this section is the easiest, as it is the delete page. Open up the file and paste to following:

In this block we require our config, make sure we are logged in, then save the requested id to a variable, create a MySQL connection, setup our SQL query, then execute the query. We next check to see if a row was affected, and sets a message appropriately. We then redirect to the newsletters page. Congrats, you have finished the first of four CRUD sections. Next, we will work on the CRUD for our templates.

Step 6: Templates

Our application will also allow multiple templates, all stored in the database. First, lets create our database table:

We have an auto-incrementing id column, a name column, a columns column to save the number of columns (the application only scales to 2 columns, which should be enough, but can easily be expanded. This could also be used for different sections.), and our body. Just like last time, we need to make each of our pages, and we will have a templates.php page, as well as templates_new.php, templates_edit.php, templates_delete.php, and templates_preview.php. We are first going to work on our templates.php file, so open it up and paste:

Again we start with the basics. We then preform a query to find all of our templates. We then loop through each array in templates and we create a table. We then get our error (and success) messages, and then our content. Your page should look something like:

Now, moving on to our our new page, paste the following:

So, again, we have our same header. Our POST again creates a MySQL connection, then we create our query, and using mysql_real_escape_string to allow any characters into the record, and then we execute our query, set our success message, and redirect to our templates listing. If you look at our form, I also ask for variables to be inserted for our content, and I will show you how this comes into play later when we match a message with a template. Your page should look like:

Next we are going to work on our edit page. If you have noticed, much of this code is copy and paste across all of the same actions, so make it easy on yourself.

We start with the same intro, then our POST block, our title, then our requested id. After, we try and find the template we are working with, then sets three variables so that we can inset them into our content block. We also convert all of the tags we stored to HTML characters so everything will display. When looking at our POST block, you will notice we create our link, then our query and again use mysql_real_escape_string to save everything, execute our query, and then set our message, and redirect to our templates list. Your edit page (with a sample record) should look like:

Now we will create another delete page, so open up our delete page and paste in:

I hope you have picked up the pattern here, this is a very simple page. Now we are going to work on an extra page that is not part of the CRUD spectrum; we are going to create a preview page. The binoculars in the action part on the table is the link for each one (in a new window). So open up our preview page. The page is very simple, we find our template and echo the data, and then append a javascript close button. The code looks like:

And an example preview looks like:

Now we have finished with our Templates, we are ready to move onto the next step!

Step 7: Subscribers

So now we are going to work with our subscribers! We are going to create two tables. The first:

Refers to each subscriber, and the second:

Creates a table for our many-to-many relationship with our newsletters. A subscriber can have multiple subscription to newsletters, so they can subscribe to multiple ones, and each newsletter can have many subscribers. To create the most dynamic solution, we have a linking table.

Let's first create our files. The files we are going are going to have are subscribers.php, subscribers_delete.php, and subscribers_edit.php. Our create action will be created later for the front-end. First open up subscribers.php and paste in:

We have basically the same listing pages as before, except this time we will be finding our subscribers. Your page (with some sample data) should look like:

Now we will move on to our edit page:

This page is fairly different so I will explain each part. The header is the same we have been using. I'll skip the POST block and come back to it. We then continue the same code. We next find our current subscriber. Next we find all newsletters (this includes ones that are not visible - visible mean visible to the public) and then all of the subscriber's subscriptions. We next loop through every newsletter record returned, we next reset some values. Next, we loop through every subscription the user has, and if the subscription's newsletter_id is equal to the current newsletter we are looping we set $s true, and$subid equal to the subscription id. We then set the variable $checked equal to either checked or nothing depending on whether a subscription was found for this subscriber and the current newsletter in the loop. Next we create the checkbox form area, with a lot of hidden fields. First, we have the actual checkbox with a name that will create an array for each checkbox. We then have our label, and next we output wether or not the subscription exists, the newsletter_id and then the subscription_id for when the subscription exists. After which we have our normal content. Now, if we move on to our POST block. We first get the id posted from our hidden field at the bottom. We next create our MySQL link. Next we have our first SQL query where we update the subscriber record. Next we loop through every newsletter checkbox. The first conditional statement checks to see if the POSTed data says we do not have an existing subscription, and the user wants to subscribe to the newsletter. To handle this, we are going to perform a SQL INSERT into our subscriptions table where our subscriber_id is the same as the user_id we are editing, and newsletter_id equal to the 'nlid' value POSTed by one of our hidden fields. We then execute that SQL INSERT query. The elseif conditional statement says that if our subscription exists, but the checkbox was unchecked so we unsubscribe, we need to delete the subscription. We handle this with a SQL DELETE query. To form our query we set$subid equal to the posted value for our 'subid'. We then create our query by deleting the record where the subscription id equals our variable of \$subid. Next we execute the query, set our session success message, and then redirect back to our subscribers page. Your final edit page should look like: (filled with sample data)

We have one last page to work on for the subscribers part of the backend: the delete page. Just like before, this page is very simple:

Step 8: Messages

I will tell you upfront, this section has the most pages. We will be working with seven now, and creating one more in Step 10. First, we are going to create our messages table with this SQL:

Next, we'll create the files for this step. Creates seven files, each named messages.php, messages_delete.php, messages_edit.php, messages_new.php, messages_new_step2.php, messages_new_step3.php, and messages_preview.php. Let's first open up messages.php and make it look like:

This is our routine table, except we now have 4 'extra' links now, one to send the message, one to preview, one to edit, and one to delete. Your page should look like:

Now we are going to start work on our new pages. The first page is where all messages start out, and then you can progress on and enter the actual message on the next page. The reason for this is because we first need to create the initial message in the DB and so we can find information about the template. The step2 page is basically the edit page (there are six line differences according to FileMerge). Open up our new file and paste the following:

This page is very similar to what our other new pages look like, but this one was one change in the POST block. Right after we perform the SQL INSERT, we find the most recent insert id. Now this is not the perfect solution, but I prefer it to performing another SQL query to find a row using unindexed columns. This step should look like:

We then redirect to step2, so let's open up the file:

Hopefully you have gotten the gist of all the pages and understand the page above. We have our normal heading. we then set our id from our GET request. Next we find the message we are working with, then we find all the templates and construct a drop down. We also use this look to define whether we will have one or two textareas. Next we have our POST block, which creates the link, then checks to see if we are working with one or two columns and creates the appropriate SQL query. After that we have our form. Your form should look like:

Now we will continue onto step 3, so open up the file and paste:

The page is very simple and is the end of creating a message. It offers us a few links. The first is a link to preview the message. The next offers to take us back home. The third offers to take us to send the message (Step 10). The page looks like:

Now we are going to continue on to our edit page. I will not explain it as it is the same file as messages_new_step2.php, so you can refer there.

The page will look almost identical to our step 2, but the textarea will have content. Now we will create the delete page with:

That page should also look familiar. The final page we are going to work on in this step is our preview page, so open it up and place:

This file is somewhat different than you have seen, so I'll walk you through it. First have have our normal heading. Next we find the current message we are working with, and set a few variables to the results. Next we find the template we are working with and set a variable equal to the body. Next, we have a conditional statement that checks to see the number of columns the template has. If it has we use the PHP function str_replace to replace the %content% tag we have with our actual content. Otherwise, we first perform a str_replace for the left column, and then on the result of that we perform str_replace again for the right column. Now we are ready to continue onto the front-end.

Step 9: The Front-End

We have finally reached the front-end! For this step and this step only, I will assume the files we are working with are in the root of the project (so not the admin folder, the one containing it). We are going to be working with four files here, so go ahead and create index.php, preferences.php, subscribe.php and a style.css file. First open up our index.php file and paste:

I'll explain the page first and then we will get to the picture so-far and the page styling. In the PHP section at the top we require our config.php file (now in the admin directory), then find all of our publicly visible newsletters and create a check box array. You will notice that we are not handling the POSTed data here, and I chose to do that in our subscribe.php, and we will get to that, but first let's style the page. The page should currently look like:

First I added the 960.gs reset file like I did in our other stylesheet. Then I added the three following styles to make the design look like:

So now that we have a clean and simple page, we are going to continue on and work on our subscribe.php file. Go ahead and open the file and paste:

This page is very much like our edit subscribers page, but no DELETE SQL queries happen here. We simply check to make sure we have all POSTed data. We then set a few variables to our POSTed data, and then create and perform a SQL INSERT query to add the person to our subscribers table. After which we perform a SQL query to find that just created subscriber (insert_id was not working this time for me). We then loop through all of the POSTed newsletters and check to see if we want to subscribe to them, and perform SQL INSERTs when needed. If all goes to plan, you see a nice screen like the one below:

We have on last page here to work on, and that is the preferences.php file. This is where a user can edit their email subscription. I am going to split the page in two. First we have our PHP block:

In this block, a lot is going on. First, we include our config file. Next, we check for a POST, and if we have one, we update our database. This portion is copied exactly from our subscribers_edit.php file so you can look there for a bit more explanation. Next depending on if we have a get request, we set our variable (this variable is used in the HTML section of the page). We then look for a subscriber with that email, and if one exists or we are showing the find portion, we continue, otherwise we are redirected home. Next we find all of our newsletters, and all of the subscriber's subscriptions, and then create our checkbox form. The HTML portion looks like:

In our HTML block we have two forms and some PHP to choose which one to display. The top form is the form the user sees if a record in the database has been found. The second form is for entering your email and having the system find it. The second form looks like:

And the first looks like:

And the second form after we saved our preferences:

Now that we have finished the front-end, we have one last step: sending the emails!

Step 10: Sending Messages

Our last step is to work on the page to send our message. We will be working in the admin directory, and only one file will be created. Go ahead and create our messages_send.php file and place the following in it:

The first part we again require our config, then make sure the user is logged in, then our title, make sure our id is an integer, and then set our tab. Next we have our complicated post block. First we set up our base query, and running just that would return 0 records, which is good because that means no users will be send the newsletter. Next we loop through every newsletter that we want to send to, and find all of the subscriptions for that newsletter. We then create a string that will be appended to our original SQL query so that we can find every subscriber. Now, we run that query and create an array where the keys are the emails and the name is the value, and this helps us use names when the user looks at the email in their mail application, showing the TO: as their name. We next find the message we are working with, and set the subject, message, and template id to variables. We then find our template and set the body to a variable. Then we use the same code from the message preview to replace the strings inside the template the the parts of the message. Then we call our yet-to-be-created-function send_email_to_mass and then redirect home.

Leaving our POST block, we create the same checkbox list of newsletters so the admin can pick which one(s) he wants to send the message to. Then we have a simple form that looks like:

Now, open up our classes.php file and add the following function:

So first, we have our function declaration, and it expects four variables to be passed to it, from, recipients, body, and subject. Next we require the sqift_required.php file of our Swift Mailer Library. Next we create a new Mail Transport (this uses the PHP mail function, so it would be sending from your local machine, for the documentation on the three transport types, see the documentation). Next we create a mailer using that transport. Then we create a new message from our subject, then set our from, to, and body. Then we use the batch_send function so that each recipient only sees themselves on the email, and no one else.

There is one possibly downside of doing it the way I have, and that is if you are sending many messages, the page may take forever to load. A solution to tthis would be running a Javascript AJAX request to send each and every message, but I won't cover that here. Now that we have finished working on sending messages, we are going to spice up the home page and then we will be done!

Step 11: The Homepage

When you load the admin index, the page does not really do much. I want to have some 'stats' on our homepage, and we will finally use the count_query function. Open up the admin index file and change it to look like:

The page is very simple. We require our config, make sure we are logged in, then we perform six count queries, one for each of our tables and then output that. This is what the final page look like:

Conclusion

Congratulations, you have finished! I hope you enjoyed the tutorial and it did not confuse you too much :)