Inserting an e-mail field into already created database

Add email field to an already created DB

[brettxw]

Hi guys,

I currently have a "register" page where they can enter their first name, last name, user name, password, confirm password text fields. I would like to insert an e-mail field to this register page and also update the DB structure to show this field as well. Also, if its not to hard, how would I create an automated e-mail to be sent to that e-mail address?

My codes are below :

Register Page (Pretty sure I can just copy the code for the first name and change the text to E-mail and the ID as well):

<?php
if( isset($_SESSION['ERRMSG_ARR']) && is_array($_SESSION['ERRMSG_ARR']) && count($_SESSION['ERRMSG_ARR']) >0 ) {
echo '<ul class="err">';
foreach($_SESSION['ERRMSG_ARR'] as $msg) {
echo '<li>',$msg,'</li>'; 
}
echo '</ul>';
unset($_SESSION['ERRMSG_ARR']);
}
?>
<form id="loginForm" name="loginForm" method="post" action="register-exec.php">
 <table width="300" border="0" align="center" cellpadding="2" cellspacing="0">
 <tr>
  <th>First Name </th>
  <td><input name="fname" type="text" class="textfield" id="fname" /></td>
 </tr>
 <tr>
  <th>Last Name </th>
  <td><input name="lname" type="text" class="textfield" id="lname" /></td>
 </tr>
 <tr>
  <th width="124">Login</th>
  <td width="168"><input name="login" type="text" class="textfield" id="login" /></td>
 </tr>
 <tr>
  <th>Password</th>
  <td><input name="password" type="password" class="textfield" id="password" /></td>
 </tr>
 <tr>
  <th>Confirm Password </th>
  <td><input name="cpassword" type="password" class="textfield" id="cpassword" /></td>
 </tr>
 <tr>
  <td>&nbsp;</td>
  <td><input type="submit" name="Submit" value="Register" /></td>
 </tr>
 </table>
</form>

My Reg-Exec page:

<?php
//Start session
session_start();

//Include database connection details
require_once('config.php');

//Array to store validation errors
$errmsg_arr = array();

//Validation error flag
$errflag = false;

//Connect to mysql server
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$link) {
die('Failed to connect to server: ' . mysql_error());
}

//Select database
$db = mysql_select_db(DB_DATABASE);
if(!$db) {
die("Unable to select database");
}

//Function to sanitize values received from the form. Prevents SQL injection
function clean($str) {
$str = @trim($str);
if(get_magic_quotes_gpc()) {
$str = stripslashes($str);
}
return mysql_real_escape_string($str);
}

//Sanitize the POST values
$fname = clean($_POST['fname']);
$lname = clean($_POST['lname']);
$login = clean($_POST['login']);
$password = clean($_POST['password']);
$cpassword = clean($_POST['cpassword']);

//Input Validations
if($fname == '') {
$errmsg_arr[] = 'First name missing';
$errflag = true;
}
if($lname == '') {
$errmsg_arr[] = 'Last name missing';
$errflag = true;
}
if($login == '') {
$errmsg_arr[] = 'Login ID missing';
$errflag = true;
}
if($password == '') {
$errmsg_arr[] = 'Password missing';
$errflag = true;
}
if($cpassword == '') {
$errmsg_arr[] = 'Confirm password missing';
$errflag = true;
}
if( strcmp($password, $cpassword) != 0 ) {
$errmsg_arr[] = 'Passwords do not match';
$errflag = true;
}

//Check for duplicate login ID
if($login != '') {
$qry = "SELECT * FROM members WHERE login='$login'";
$result = mysql_query($qry);
if($result) {
if(mysql_num_rows($result) > 0) {
$errmsg_arr[] = 'Login ID already in use';
$errflag = true;
}
@mysql_free_result($result);
}
else {
die("Query failed");
}
}

//If there are input validations, redirect back to the registration form
if($errflag) {
$_SESSION['ERRMSG_ARR'] = $errmsg_arr;
session_write_close();
header("location: register-form.php");
exit();
}

//Create INSERT query
$qry = "INSERT INTO members(firstname, lastname, login, passwd) VALUES('$fname','$lname','$login','".md5($_POST['password'])."')";
$result = @mysql_query($qry);

//Check whether the query was successful or not
if($result) {
header("location: register-success.php");
exit();
}else {
die("Query failed");
}
?>

And this is my MySQL file that I used to import (this is just to show how the structure looks:

#
# Table structure for table 'members'
#

CREATE TABLE `members` (
 `member_id` int(11) unsigned NOT NULL auto_increment,
 `firstname` varchar(100) default NULL,
 `lastname` varchar(100) default NULL,
 `login` varchar(100) NOT NULL default '',
 `passwd` varchar(32) NOT NULL default '',
 PRIMARY KEY (`member_id`)
) TYPE=MyISAM;



#
# Dumping data for table 'members'
#

INSERT INTO `members` (`member_id`, `firstname`, `lastname`, `login`, `passwd`) VALUES("1", "Test", "Test2", "testname", "ba018360fc26e0cc2e929b8e071f052d");

With the code given above, how would I go about adding an email field to this DB..and possibly create an automated e-mail to be sent to that e-mail address?


Appreciate the help!

[Matthew1980]

Hello brettxw,

Well wrt to your sql query, you may get a good answer from the guys in the databases forum, but you need to look at the ALTER instruction from the manual [dev.mysql.com]. This should explain things well for you.

Then in theory, you can just on completion of that sql query, use the email address to send an email to that address.

Only one thing I will comment on, and that is the use of @ on trim() and mysql_query(), surely you want to know about EVERY possible fault within your software (@ acts as an error suppressor and php themselves only advocate this method on a handful of functions :)), best thing I can suggest is that you use error reporting and set it to strict if you want to know as your coding to a decent standard, though this won't tell you if your code is elegant and robust, but you get the idea!

So the top of your php file would look like this:-

<?php
//set error reporting
error_reporting(E_ALL|E_DEPRECATED|E_STRICT);
//this handles old functions not supported any longer too :)

//Start session
session_start();

//Include database connection details
require_once('config.php');
.
.

Hope that makes sense anyway.

Cheers,
MRb

[rocknbil]

You really shouldn't alter table in a public process, public users shouldn't have alter or drop privileges. You should really either -

1. Have the field present for inserts, if it's empty, well then, leave it empty in your script.

alter table add email_address varchar(150) not null default '';

2. Have a joined table for email addresses.

create table email_addresses(id int(11) primary key auto_increment,user_id int(11) not null default 0, index(user_id),email_address varchar(150) not null default '', index(email_address(6)));

#2 would be a bit of overhead for just an email address field in a members table, I would use #1.

[brettxw]

Ah, thank you guys for the help. I apologize for posting it in here, I failed to look at the database forum on here. Thank you for the input here and will use these inputs for what im looking to do. Thank you again.