Hello friends, today i will show you how to create a newsletter subscription, how to show all subscribe email address, how to export those email address in a CSV file using PHP, Ajax, jQuery . Newsletter subscription is a very important part of our website. We can promote our website, our blog using newsletter. So lets start to build the newsletter subscription application.
Step 1: In our first step we create our database table to store user email address.
CREATE TABLE IF NOT EXISTS newsletter_signup
(
email_id
int(11) NOT NULL AUTO_INCREMENT,
email_address
varchar(50) NOT NULL,
PRIMARY KEY (email_id
)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
Setp 2: Now we create a subscription form where user put their email address. File name is index.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
<!DOCTYPE HTML> <html lang="en-US"> <head> <meta charset="UTF-8"> <title>Newsletter Signup Using PHP, Ajax, Jquery</title> <link rel="stylesheet" href="css/style.css" /> </head> <body> <div class="serch-wrap"> <div class="form-wrapper cf"> <form> <input type="text" class="abhijitscript" name="email_address" > <input type="submit" value="Subscribe" id="formNewLestter"> </form> </div> <a class="button-link" href="list_email.php" target="_blank">Show Subscribe Emails</a> </div> </body> <script type="text/javascript" src="js/jquery-1.11.1.min.js" ></script> <script type="text/javascript" src="js/addmail.js" ></script> </html> |
Setp 3: Now we have to style our subscription form. So we create a style.css file for that
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 |
*{margin:0;padding:0;} body { margin: 0; padding: 0; font-family: Georgia, "Times New Roman", Times, serif; font-size: 12px; } .autodropdown{position:absolute;top:56px;left:15px;} .abhijitscript{ padding:4px; border:1px solid #333; } .suggestresult{ width:339px; list-style:none; } .suggestresult li{ padding:5px; border:1px solid #333; border-top:0; cursor:pointer; background:#333; color:#fff; border-bottom:1px solid #666; } .suggestresult li:hover{ background:#666; color:#ffffff; cursor:pointer; } .cf:before, .cf:after{ content:""; display:table; } /*--------*/ .cf:after{ clear:both; } .cf{ zoom:1; } .form-wrapper { width: 450px; padding: 15px; margin: 50px auto 50px auto; background: #444; background: rgba(0,0,0,.2); border-radius: 10px; box-shadow: 0 1px 1px rgba(0,0,0,.4) inset, 0 1px 0 rgba(255,255,255,.2); } .serch-wrap{ width: 450px; margin: 0 auto; position:relative; } /* Form text input */ .form-wrapper input[type="text"] { background: none repeat scroll 0 0 #eee; /* border: 1px solid red; */ border-radius: 3px 0 0 3px; box-sizing: border-box; -moz-box-sizing: border-box; -webkit-box-sizing: border-box; float: left; font: bold 15px 'lucida sans','trebuchet MS','Tahoma'; height: 40px; padding: 10px 5px; width: 340px; } .form-wrapper input[type="text"]:focus { background: #fff; box-shadow: 0 0 2px rgba(0,0,0,.8) inset; } .form-wrapper input[type="text"]::-webkit-input-placeholder { color: #999; font-weight: normal; font-style: italic; } .form-wrapper input[type="text"]:-moz-placeholder { color: #999; font-weight: normal; font-style: italic; } .form-wrapper input[type="text"]:-ms-input-placeholder { color: #999; font-weight: normal; font-style: italic; } /* Form submit button */ .form-wrapper input[type="submit"] { overflow: visible; position: relative; float: right; border: 0; padding: 0; cursor: pointer; height: 40px; width: 110px; font: bold 15px/40px 'lucida sans', 'trebuchet MS', 'Tahoma'; color: #fff; text-transform: uppercase; background: #d83c3c; border-radius: 0 3px 3px 0; text-shadow: 0 -1px 0 rgba(0, 0 ,0, .3); } .form-wrapper input[type="submit"]:hover{ background: #e54040; } .form-wrapper input[type="submit"]:active, .form-wrapper input[type="submit"]:focus{ background: #c42f2f; } .form-wrapper input[type="submit"]:before { /* left arrow */ content: ''; position: absolute; border-width: 8px 8px 8px 0; border-style: solid solid solid none; border-color: transparent #d83c3c transparent; top: 12px; left: -6px; } .form-wrapper input[type="submit"]:hover:before{ border-right-color: #e54040; } .form-wrapper input[type="submit"]:focus:before, .form-wrapper input[type="submit"]:active:before{ border-right-color: #c42f2f; } .form-wrapper input[type="submit"]::-moz-focus-inner { /* remove extra button spacing for Mozilla Firefox */ border: 0; padding: 0; } .error{color:red;} .success{color:green} .button-link { padding: 10px 15px; background: #4479BA; color: #FFF; text-decoration:none; -webkit-border-radius: 4px; -moz-border-radius: 4px; border-radius: 4px; border: solid 1px #20538D; text-shadow: 0 -1px 0 rgba(0, 0, 0, 0.4); -webkit-box-shadow: inset 0 1px 0 rgba(255, 255, 255, 0.4), 0 1px 1px rgba(0, 0, 0, 0.2); -moz-box-shadow: inset 0 1px 0 rgba(255, 255, 255, 0.4), 0 1px 1px rgba(0, 0, 0, 0.2); box-shadow: inset 0 1px 0 rgba(255, 255, 255, 0.4), 0 1px 1px rgba(0, 0, 0, 0.2); } |
Step 4: Now we have to send user email address to a page. Here I use a js file to take email value from index.php and send the value using ajax post request to that page. So I create a addmail.js to do that.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
$(document).ready(function() { $('#formNewLestter').click(function(){ var email = $('.abhijitscript').val(); var filter = /^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$/; if (!(filter.test(email))) { $('.abhijitscript').css('border', '1px solid red'); $('.abhijitscript').before("<div class='error'><h3>Email Invalid</h3></div>"); $('.abhijitscript').focus(); return false; } $.ajax({ type: "post", url: "add_mail.php", data: {email_address:email}, success: function(data) { $('.abhijitscript').before(data); $('.error').remove(); } }); return false; }); $( ".abhijitscript" ).keyup(function() { var email = $('.abhijitscript').val(); if(email.length == 0) { $('.error').remove(); } }); }); |
Step 5: Now we create a connection file to connect php with mysql conn.php
1 2 3 4 |
<?php mysql_connect('localhost','root',''); mysql_select_db('test'); ?> |
Step 6: Now we create a php file which take the email value from addmail.js and insert the email address to our database table and return the status. So we create add_mail.php for that.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
<?php include_once('conn.php'); if(isset($_POST['email_address'])) { $email_address = mysql_real_escape_string(trim($_POST['email_address'])); $sql="SELECT count(*) as total FROM newsletter_signup WHERE `email_address` = '$email_address'"; $result=mysql_query($sql); $row=mysql_fetch_array($result); if($row['total'] != 0) { echo "<div class='error'><h3>You have already subscribed to this newsletter.</h3></div>"; } else { mysql_query("INSERT INTO newsletter_signup SET `email_address` = '$email_address'"); echo "<div class='success'><h3>You have successfully subscribed to this newsletter.</h3></div>"; } } ?> |
Step 7: In this file we fetch all email address from database table and show the list to administrator(just think). So we create list_email.php for that.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
<!DOCTYPE HTML> <html lang="en-US"> <head> <meta charset="UTF-8"> <title>Newsletter Signup All Email Address</title> <link rel="stylesheet" href="css/style.css" /> <script> function csv(){ document.print_csv.submit(); } </script> </head> <body> <?php include_once('conn.php'); $sql="SELECT * FROM newsletter_signup"; $result = mysql_query($sql); if (!$result) die('mySQL error: ' . mysql_error()); echo "<style> table { border:1px solid #ccc; border-collapse:collapse; padding:5px; margin-bottom:5px;width:400px} th { background:#eff5fc; border:1px solid #ccc; padding:10px; text-align:center; } td { padding:10px; border:1px solid #ccc;} </style>"; ?> <div class="serch-wrap"> <form name="print_csv" action="download_csv.php" method="post"> <input type="hidden" value="csv" name="download" /> <?php echo "<table>"; echo "<th>ID</th><th>Email Address</th>"; if (mysql_num_rows($result) == 0) { echo '<tr>'; echo "<td colspan='2'>No emails found.</td>"; echo '</tr>'; } else { $emails=array(); while($row=mysql_fetch_array($result)) { $emails[] = $row; } } foreach($emails as $email) { echo '<tr>'; echo '<td>'.$email['email_id'].'</td><td>'.$email['email_address'].'</td>'; echo '</tr>'; } echo "</table>"; ?> </form> <input name="downloadCSV" type="button" value="Download CSV" class="button-link" onClick="csv()" /> <a class="button-link" href="send_email.php" >Send Email</a> </div> </body> </html> |
Here I add a extra feature to export all listed email address. We can use this CSV file any where. So I create a download_csv.php file where I create the CSV from database table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
<?php if(isset($_POST['download']) && $_POST['download'] != '') { /* csv file generate */ $filename = tempnam(sys_get_temp_dir(), "csv"); $file = fopen($filename,"w"); $StoreField = array('Email No','Email Address'); fputcsv($file,$StoreField); include_once('conn.php'); $sql="SELECT * FROM newsletter_signup"; $result = mysql_query($sql); while($row=mysql_fetch_array($result)) { $StoreFieldValue = array($row['email_id'],$row['email_address']); fputcsv($file,$StoreFieldValue); } fclose($file); /* send file to browser */ header("Content-Type: application/csv"); header("Content-Disposition: attachment;Filename=allemail".time().".csv"); readfile($filename); unlink($filename); } ?> |
Demo
Download