How to Create a Newsletter from Scratch Using PHP & MySQL


Creating a newsletter that you could use for your website is actually not too difficult of a procedure at all. You can do this fairly easily using PHP and MySQL.

You use MySQL as a database storage system that stores the contact information of all the users who sign up for the newsletter. Once you have all the emails of the users who have signed up for the database, we will show how you can write PHP code so that you can write one email and mass deliver it to all the users in your MySQL database who have signed up for the newsletter.

For example, the following newsletter below asks for a user's first name, last name, and email to sign a user up.

Sign up for Newsletter

In actuality, you may ask just for email, or you may ask for the first and last name and email as below, or you may even ask for additional information. The choice is yours. However, we will use the above model to create the newsletter that asks just for these 3 pieces of information. Once you know the HTML, PHP, and MySQL code to create fields, you will be able to modify to add what you want.

To create this newsletter above, the HTML code to do is:


Above is the HTML code just to create the text boxes with their various names, which in this case are First Name, Last Name, and Email, along with the title, which is Sign up for Newsletter.

This HTML code, though, will be the basis for the PHP code which we are going to write. In order to extract data from this form, we need PHP to retrieve the data and place it in the database we will create. This data is the first name, last name, and email of whoever signs up.

If you look at the first line of the HTML code, the form tag specifies the action attribute, which is set to Newletter-signup.php. This is the separate PHP page that we'll have to create which processes the data that the user types in (his or her name and email) and places it into a database, which we can use to gather every email of everyone who signs up. After that, we just have to create one more HTML and PHP page, which sends out an email to everyone on the list.

MySQL Table Creation

Before we create the PHP code to retrieve the HTML form data and places them all in one table of a database, we have to create the MySQL table first so that it can be entered into that database.

In order to create a MySQL table, go into your web hosting account and find out where the resource is to create a MySQL table. Most web hosts offer MySQL database creation. If you have one, find out how to access this resource. If not, you will have to obtain a web host that offers MySQL or some other SQL server access. Having a database is crucial to keeping a list of all the people who sign up for the newsletter you want to create, so it's absolutely important to have one.

Once you create a database name, a username and a password, you then will have a host name for that account. This you need to enter into the PHP code. Then you'll just have to create a table. To create the table which we want, we will need to have 4 columns. The first column will serve as the primary key. This serves as a chronological list to make each entry into the table unique, so if we wanted to, we can definitely refer to each row. The 2nd, 3rd, and 4th columns serve as the columns to store the first name, last name, and email of each user.

The PHP code to create the Newsletter-signup.php page is below:

PHP Code

The PHP code above retrieves the data from all the fields from the HTML form, First Name, Last Name, and Email.

Let's go over each line so you can understand the PHP code. The first block of PHP code connects you to the MySQL Database which you want to gather all of the user's information. Basically, from this point, you have to create a MySQL database and then create a table in that database where you will store the user's information. In the first 4 lines in the above code, you must enter the username, password, host name, database name, and table name of that MySQL connection.

The next block of code, all using the superglobal array POST method, retrieves data from the HTML forms we created. We create a PHP variable for each data that we collect and then we set that variable equal to the $_POST method and the name of what we assigned the name attribute to in the HTML code. If you need help understanding this, check out the tutorial on How to Retrieve Data from an HTML Form Using PHP. Once you read and understand this article well, you will be brought easily to speed on the progress thus far.

The next line inserts the user's data into the 3 columns we have created in the MySQL table, named first_name, last_name, and email. The data which PHP retrieved from the HTML forms will be inserted into the MySQL table we have specified.

If everything has gone through successfully, the PHP code will tell the user once he or she has signed up that "You have been successfully added."

Now that we have the HTML code to create the newsletter-signup, the MySQL table where we will gather all the users' information, and the PHP code to retrieve the data the user enters and place all contact info in one table, all we need now is the HTML code where we can write an email and have it sent out to all user's on our newsletter-signup database.

This code is shown below:

HTML Code to Send Out Mass Email

This will bring up an interface such as shown below:

The above email form does not have functionality.

You can use this interface to send out emails to everyone on your list.

All that is needed now is one more PHP page, which we call send-mail.php. The PHP code before retrieved the user data from the HTML text boxes and placed them into the MySQL table. This PHP code takes all the emails from the list and sends the email out to all users on the list.

The PHP code to do this is below:

The above PHP code again connects us to the table of the MySQL database which has all the user contact info of the people who signed up for the newsletter.

We make $from variable equal to the email address which we want our email sent from. This can be our personal email, if we want our personal email to be the from email of the email or our website address email, or any other emails we would it sent from.

In the next block of code with the $_POST superglobal arrays, we retrieve the data from the HTML subject and body which we created, the interface which we use to send out emails.

We then connect to our database. If there is an error, the line "Error querying datbase" will be given. If not, the code has been executed successfully. The block of code with the while loop goes through each of the email contacts we have, extracting all the user's information, first name, last name, and email address.

The next block of code then delivers the message using the mail function. Realize that you can modify the $msg variable to anything you want. In this example, we start it over addressing each person with "Dear first_name last_name and then skipping a line and giving them the body of the email. However, you may choose to do it a different way. If you want to, just modify the $msg variable.

If everything has gone successfully, you will get the screen with the words "Email Sent to: " followed by all the email contacts we have on our database.

Unsubscribe Option

Of course, you may have to add one more functionality to this email, a link that you can put on emails so that subscribers can unsubscribe if they decide they no longer want emails about your service.

This isn't difficult, but also requires a separate HTML and PHP page of its own.

The HTML code to delete a subscriber by him entering his email is shown below.

This produces the following, which is the page that you can redirect a user to if s/he links on the unsubscribe link at the bottom of the emails that you send out:

Please enter the email for which you want to unsubscribe:

The PHP Code which then looks up the email that the user types in and then searches the database, looking that user account, is:

When the user enters his or her email and has been successfully unsubscribed (deleted from the list), the line will appear, You have successfully unsubscribed from this newsletter.

This is how a newsletter could be created from scratch from HTML, MySQL, and PHP. It isn't very complicated if you understand the basics of all languages or if you don't, it could present some challenges. The best advice is to read and study the basics of all the concepts presented from all the 3 languages, then you'll have a better feel to do this on your own.

There are many advantages to creating your own newsletter rather than buying an available one online including:

  1. Saving Money- Most newsletter service programs charge monthly fees. Do away with this if you learn how to create your own
  2. Having More Control- When you create your own newsletter from scratch, you know every element of how it operates including the MySQL tables and all the HTML and PHP coding to create it. If you want at any time, you can modify any element of your newsletter. Say, your newsletter asks for only the email but you decide you also want their city or country of residence, maybe to guage geographically the places where people who are living subscribe to your site. Modifying this is simple once you know the coding involved.
  3. Can Lose Your Newsletter- If the newsletter program which you subscribe for goes down or out of business, you can lose all the contacts in your newsletter. If you do your own and have confidence in the web host provider you have, the chances of this happening is much less.

The following lists all the information you need to create a newsletter, but if you want to know this inside out, I can walk you through all the above steps and have a newsletter set up on your site in a half an hour or less. I'll make sure that you successfully create your own database on your web host that you current have, create the table where you want to store all the contacts of people who sign up for your newsletter, and help you build all the HTML and PHP code to create the newsletter. And I'll do this all for a one-time fee of $9.99. Hopefully this will not be necessary, as I'm hoping everything above is self-explanatory, but if you want personal help with each level of code and want to know how to do it yourself, we can set something up where I can teach you how this is done so that you can get your newsletter up and running quickly and gather visitor contact info and start sending out emails.

Thank you for reading.

Related Resources

How to Create a Search Engine Using PHP

How to Upload Images to a Website Using PHP

How to Upload Files to a Website Using PHP

How to Create a Register and Login Page Using PHP

HTML Comment Box is loading comments...