Hot File

How to Build a Newsletter System With PHP and MySQL

View: 1395    Dowload: 0   Comment: 0   Post by: hanhga  
Author: none   Category: MySQL   Fields: Other

9 point/4 review File has been tested

When starting any project, I like to layout the folders before I start coding, so lets do that now.

Introduction

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.

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:

# admin/config.php 
    <?php  
    // DB Settings 
    define('DB_SERVER', 'localhost'); 
    define('DB_USER', 'root'); 
    define('DB_PASSWORD', ''); 
    define('DB_NAME', 'nettuts_ns_demo'); 
  
    define('FROM_EMAIL', 'no_reply@ohyeahemail.com'); 
    define('FROM_NAME', 'oh yeah email!'); 
  
  
    session_start(); 
    require_once 'classes.php'; 
    $mini = false; 
    $nonav = false; 
    error_reporting(0);

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 ourclasses.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.

Now go ahead and create our classes.php file and add in:

# admin/classes.php 
    prepare($sql) or die('error'); 
      $stmt->execute(); 
      $meta = $stmt->result_metadata(); 
  
      while ($field = $meta->fetch_field()) { 
        $parameters[] = &$row[$field->name]; 
      } 
  
      $results = array(); 
      call_user_func_array(array($stmt, 'bind_result'), $parameters); 
  
      while ($stmt->fetch()) { 
        foreach($row as $key => $val) { 
          $x[$key] = $val; 
        } 
        $results[] = $x; 
      } 
  
      return $results; 
      $results->close(); 
      $link->close(); 
    } 
  
    function count_query($query) { 
      $link = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME) or die('There was a problem connecting to the database.'); 
      if($stmt = $link->prepare($query)) { 
        $stmt->execute(); 
        $stmt->bind_result($result); 
        $stmt->fetch(); 
        return $result; 
        $stmt->close(); 
      } 
      $link->close(); 
    } 
  
    function check_username_and_pw($u, $pw) { 
      $link = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME) or die('There was a problem connecting to the database.'); 
  
      $query = "SELECT * FROM users WHERE username = ? AND password = ? LIMIT 1"; 
      if($stmt = $link->prepare($query)) { 
        $p = md5($pw); 
        $stmt->bind_param('ss', $u, $p); 
        $stmt->execute(); 
        $stmt->bind_result($id, $username, $pw); 
        if($stmt->fetch()) { 
          $_SESSION['authorized'] = true; 
          $_SESSION['username'] = $username; 
          return true; 
        } else { 
          return false; 
        } 
        $stmt->close(); 
      } 
      $link->close(); 
    }

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.

Our next file we will work on is our layout.php file, so go ahead and create it. In our head section we simply declare our regular XHTML declarations. If you notice, we automatically add the title of the page to another string for our title. Then we have a stylesheet (go ahead and create that too in our media folder). After which we open our body tag, and check to see if we want a mini layout, and if so adds a class. Then we have our header, and then check to see if we want our navigation, and if we do, we show our tabs. I also added a way to add a current class for each tab. We also then have a logout link, and then have our container div. In this, we add an h3 tag with our title, and then echo our content.

# admin/layout.php 
     
     
         
             
  
             » my newsletter app 
             
             
         
         
            
 
            

</body

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

# admin/index.php 
        <?php 
        require_once 'config.php'; 
        $title = "Home!"; 
        $content = <<<EOF 
        

current stats

Our home page! EOF; include 'layout.php'; ?>

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:

# admin/media/style.css 
    /* reset */
    html,body,div,span,applet,object,iframe,h1,h2,h3,h4,h5,h6,p,blockquote,pre,a,abbr,acronym,address,big,cite,code,del,dfn,em,font,img,ins,kbd,q,s,samp,strike,strong,sub,sup,tt,var,b,u,i,center,dl,dt,dd,ol,ul,li,fieldset,form,label,legend,table,caption,tbody,tfoot,thead,tr,th,td{margin:0;padding:0;border:0;outline:0;font-size:100%;vertical-align:baseline;background:transparent}body{line-height:1}ol,ul{list-style:none}blockquote,q{quotes:none}blockquote:before,blockquote:after,q:before,q:after{content:'';content:none}:focus{outline:0}ins{text-decoration:none}del{text-decoration:line-through}table{border-collapse:collapse;border-spacing:0} 
    /* typography */
    body{font:13px/1.5 Helvetica,Arial,'Liberation Sans',FreeSans,sans-serif}a:focus{outline:1px dotted invert}hr{border:0 #ccc solid;border-top-width:1px;clear:both;height:0}h1{font-size:25px}h2{font-size:23px}h3{font-size:21px}h4{font-size:19px}h5{font-size:17px}h6{font-size:15px}ol{list-style:decimal}ul{list-style:square}li{margin-left:30px}p,dl,hr,h1,h2,h3,h4,h5,h6,ol,ul,pre,table,address,fieldset{margin:10px 0;}

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

# admin/media/style.css 
    #header {width:85%; margin:0 auto;} 
    #header h1 a {text-decoration:none; color:#000;} 
    #container {width:85%; background: #111; margin:5px auto; color:#fff; padding:10px;}

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:

# admin/media/style.css 
    /* tabs */
    #nav {margin:0 auto 2px;padding:10px;width:85%; background:#444;} 
    #nav a { padding:12px 10px; margin:0; color:#fff; text-decoration:none; text-align:center;} 
    #nav a.current, #nav a:hover {background:#111;} 
    #nav span.right {float:right;}

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.

# admin/media/style.css 
    body.mini #header {width:30%; text-align:center;} 
    body.mini #container {width:30%;} 
  
    /* form */
    form input.text {width:95%; font-size:16px;} 
    form textarea {width:95%; height:100%;} 
    /* table */
    table {width:98%; text-align:right; border:rgb(128,128,128); font-size:12px; margin:5px 10px; color:#000;background:#fff;} 
    table th {background-color: rgb(229, 229, 229); border:1px solid rgb(187, 187, 187); padding:3px 6px; font-weight:normal; color:#000;} 
    table tr td {border: 1px solid rgb(221, 221, 221); padding:3px 6px;} 
    table tr:hover {background-color: rgb(240, 240, 240);color:#000;} 
    /* a */
    a.large {padding: 5px; color:#000; background:#eee; text-decoration:none; margin:5px;} 
    a.large.right {float:right;} 
    a.large:hover, a.large.current {background:#444; color:#fff;} 
    /* messages */
    #message {margin: 5px 10px; padding: 4px; display:block;text-align:center;} 
    #message.error {background:#FFEBE8;border: 1px solid #CC0000;color:#CC0000;} 
    #message.success {border:solid 1px #349534; background:#C9FFCA;color:#008000;}

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.

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

# admin/login.php 
    <?php 
    require_once 'config.php'; 
    if(logged_in()) {header('Location: index.php');} 
    $title = "login"; 
    $nonav = true; 
    $mini = true; 
    if($_POST && (!empty($_POST['username']) ) && (!empty($_POST['password']))) { 
        validate_user($_POST['username'], $_POST['password']); 
    } 
    $error = $_SESSION['error']; 
    $content = <<<EOF 
    $error
    



EOF; include 'layout.php'; ?>

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:

# admin/logout.php 
    

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:

CREATE TABLE `users` ( 
       `id` int(10) AUTO_INCREMENT, 
       `username` varchar(50), 
       `password` varchar(32), 
       PRIMARY KEY (`id`) 
    ) ENGINE=MyISAM DEFAULT CHARSET utf8; 
  
    INSERT INTO `users` (`id`, `username`, `password`) VALUES  
    ('1', 'admin', '5ebe2294ecd0e0f08eab7690d2a6ee69');

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:

CREATE TABLE `newsletters` ( 
   `id` int(10) AUTO_INCREMENT, 
   `name` varchar(50), 
   `description` varchar(255), 
   `visible` varchar(10), 
   PRIMARY KEY (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET utf8;

So now that we have our newsletters table, we are going to create the pages for each action. Create four files named: newsletters.phpnewsletters_delete.php,newsletters_edit.php, and newsletters_new.php. First open up newsletters.php:

# admin/newsletters.php 
    <?php 
    require_once 'config.php'; 
    login_required(); 
    $title = "newsletters"; 
    $newsletters = query("SELECT * FROM newsletters ORDER BY id ASC"); 
    $tab = 'nl'; 
    $table = ""; 
    foreach($newsletters as $row) { 
        $dlink = 'delete'; 
        $elink = 'edit'; 
        if($row['visible'] == "1") {$visible = '';} else {$visible = '';} 
        $table .= "".$row['id']."".$row['name']."".$row['description']."$visible".$dlink." ".$elink."\n"; 
    } 
    $message = error_messages(); 
    $content = <<<EOF 
    new newsletter » 
    $message
     
        $table
    
name description visible
EOF; include 'layout.php'; ?>

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.phpfile to easily handle our error messages:

# admin/classes.php 
    // Render error messages 
    function error_messages() { 
        $message = ''; 
        if($_SESSION['success'] != '') { 
            $message = ''.$_SESSION['success'].''; 
            $_SESSION['success'] = ''; 
        } 
        if($_SESSION['error'] != '') { 
            $message = ''.$_SESSION['error'].''; 
            $_SESSION['error'] = ''; 
        } 
        return $message; 
    }

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:

# admin/newsletters_new.php 
    query($sql) or die($link->error); 
        $stmt->close; 
        $_SESSION['success'] = "Added newsletter.";  
        header('Location: newsletters.php'); 
    } 
    $title = "new newsletter"; 
    $content = <<

EOF; include 'layout.php'; ?>

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:

# admin/newsletters_delete.php 
    query($sql) or die($link->error); 
    if($link->affected_rows) { 
        $_SESSION['success'] = "Newsletter deleted."; 
    } else { 
        $_SESSION['error'] = 'Nothing deleted.'; 
    } 
    header('Location: newsletters.php');

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:
CREATE TABLE `templates` ( 
   `id` int(10) AUTO_INCREMENT, 
   `name` varchar(50), 
   `columns` tinyint(5), 
   `body` text, 
   PRIMARY KEY (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET utf8;

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.phptemplates_delete.php, and templates_preview.php. We are first going to work on our templates.php file, so open it up and paste:

# admin/templates.php 
    <?php 
    require_once 'config.php'; 
    login_required(); 
    $title = "templates"; 
    $tab = 'temp'; 
    $templates = query("SELECT id,name,columns FROM templates ORDER BY id ASC"); 
    $table = ""; 
    foreach($templates as $row) { 
        $plink = 'preview'; 
        $dlink = 'delete'; 
        $elink = 'edit'; 
        $table .= "".$row['id']."".$row['name']."".$row['columns']."".$plink." ".$dlink." ".$elink."\n"; 
    } 
    $message = error_messages(); 
    $content = <<<EOF 
    new template » 
    $message
     
        $table
    
name columns
EOF; include 'layout.php'; ?>

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:

# admin/templates_new.php 
    query($sql) or die($link->error); 
        $stmt->close; 
        $_SESSION['success'] = "Added template.";  
        header('Location: templates.php'); 
    } 
    $title = "new template"; 
    $content = <<

 

How to Build a Newsletter System With PHP and MySQL

How to Build a Newsletter System With PHP and MySQL Posted on 12-01-2016  When starting any project, I like to layout the folders before I start coding, so lets do that now. 2.25/10 1395

Comment:

To comment you must be logged in members.

Files with category

  • SQL Query to Find Nth Highest Salary of Employee with java

    View: 329    Download: 0   Comment: 0   Author: none  

    SQL Query to Find Nth Highest Salary of Employee with java

    Category: MySQL
    Fields: Other

    0/0 review
    This is a very common interview question if you are going for the role at junior level. Most of the people learn it character by character, but few understand how this query works.

  • World OOP With PHP and MySQL

    View: 1089    Download: 0   Comment: 0   Author: none  

    World OOP With PHP and MySQL

    Category: MySQL
    Fields: Other

    0.32142857142857/14 review
    Setting up the skeleton of our class is fairly simple once we figure out exactly what we need.

  • How to Build a Newsletter System With PHP and MySQL

    View: 1395    Download: 0   Comment: 0   Author: none  

    How to Build a Newsletter System With PHP and MySQL

    Category: MySQL
    Fields: Other

    1.125/4 review
    When starting any project, I like to layout the folders before I start coding, so lets do that now.

  • Top 20+ MySQL Best Practices that can not be ignored

    View: 1024    Download: 0   Comment: 0   Author: none  

    Top 20+ MySQL Best Practices that can not be ignored

    Category: MySQL
    Fields: Other

    1.6666666666667/3 review
    Database operations often tend to be the main bottleneck for most web applications today.

  • Create a PHP/MySQL Powered Forum from Scratch

    View: 1243    Download: 0   Comment: 0   Author: none  

    Create a PHP/MySQL Powered Forum from Scratch

    Category: MySQL
    Fields: Other

    1.5/3 review
    In this tutorial, we're going to build a PHP/MySQL powered forum from scratch.

  • Triggers Introduction to MySQL

    View: 361    Download: 0   Comment: 0   Author: none  

    Triggers Introduction to MySQL

    Category: MySQL
    Fields: Other

    1.6666666666667/3 review
    I would guess, even armed with this knowledge, that a good many of you are not taking advantage of triggers with MySQL.

  • How to Stored Procedures in MySQL 5

    View: 361    Download: 0   Comment: 0   Author: none  

    How to Stored Procedures in MySQL 5

    Category: MySQL
    Fields: Other

    2.25/2 review
    MySQL 5 introduced a plethora of new features - stored procedures being one of the most significant. In this tutorial, we will focus on what they are, and how they can make your life easier.

  • The strength of the PDO and. MySQLi: Which Should You Use?

    View: 368    Download: 0   Comment: 0   Author: none  

    The strength of the PDO and. MySQLi: Which Should You Use?

    Category: MySQL
    Fields: Other

    1.5/3 review
    When accessing a database in PHP, we have two choices: MySQLi and PDO. So what should you know before choosing one?

 
Newsletter Email

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd