Working with MySQL. New user registration in the database.
In that article I’ll show you how to add new account by means of PHP. User registers in the database; there is created the database that coincided with user’s login; after that he gets all privilege for it. Only that user (and administrator) has the access to that database.
So, let’s create the registration form:
<?php
$script="register.php";
print_header2 ();
?>
<p align=right>| <a href="<?php echo $script ?>">Enter to database</a> |
<p><h2 align=center><font color="ff0000">Registration.</font></h2>
<p><font face="serif" size=2> Please,fill in the form below.
<p>You may use only english chars in your name,and name"s length should be not less than 3 and
not more than 15 characters.
<br>Remember:all form fields are case sensitive.It means the names <font face="arial" size=2
color="0000ff">name</font> and <font face="arial" size=2 color="0000ff">Name</font>
are different.
<p>Password"s length should be not less than 6 characters.<br>
Don"t forget to enter your e-mail address,you may need it if you"ll forget your password.
<p><FORM ACTION="<?php echo $script ?>" METHOD="POST" name="reg">
<p align=right><a href="<?php echo $script ?>">Home</a>
<center><TABLE BGCOLOR="bfbfbf">
<tr><td colspan=2> </td></tr>
<TR><TD><b>Login:</b><TD><INPUT TYPE="text" NAME="login" SIZE="20" maxlength="15" >
<TR><TD><b>Password:</b><TD><INPUT TYPE="password" NAME="pass" SIZE="20"
onFocus="is_ValidLogin (this.form.login.value)";return true>
<TR><TD><b>Verify password:</b><TD><INPUT TYPE="password" NAME="verpass" SIZE="20"
onFocus="is_ValidPass (this.form.pass.value)";return true>
<tr><td colspan=2><input type="hidden" name="action" value="register">
<tr><td colspan=2><input type="hidden" name="script" value="<?php echo $script ?>">
<tr><td colspan=2><input type="hidden" name="submit" value="ok">
<TR><TD colspan=2><p><center><INPUT TYPE="submit" VALUE="Submit"
onMouseOver="verPasswd (this.form.verpass.value)";return true></center>
<tr><td colspan=2> </table>
</form>
print_header2 () function used in the first string includes the header and JavaScript that checks the correctness of the input data.
function print_header2 () {
?>
<html><head><title>MySQL administrator</title>
<script language="javascript">
<!--
function is_ValidLogin (entry1) {
if (entry1.length==0 || entry1.length < 3) {
alert ("You must enter login name not less than 3 characters!");
document.reg.login.focus ();
}
}
function is_ValidPass (entry2) {
if (entry2.length==0 || entry2.length < 6) {
alert ("You must enter password not less than 6 characters!");
document.reg.pass.focus ();
}
}
function verPasswd () {
if (document.reg.verpass.value !=document.reg.pass.value || document.reg.verpass.value==0) {
alert ("Error!This field must be the same as password field!");
document.reg.verpass.select ();
document.reg.verpass.focus ();
}
}
//-->
</script>
</head><style> A:link {font-family:arial;font-size:10pt;text-decoration:none;color:#000080;}
A:hover {font-family:arial;font-size:10pt;text-decoration:none;color:red;}";
A:visited {font-family:arial;font-size:10pt;text-decoration:none;color:#808080;}
BODY {background-color:#e6e8fa;font-family:arial;font-size:10pt;color:#333300;}
TD {font-family:arial;font-size:10pt;color:#0000ff;}
</style><body>
<p align=right><font face="Impact" size=7>
<font color="bfbfbf">F</font><font color="aaaaaa">O</font><font color="959595"
>R</font><font color="7f7f7f">U</font><font color="aaaaaa">M</font>
</font>
<?php
}
If the input information is correct, form sends it to the handler.
if ($action=="register") {
if ($submit) {
//Connect to the database
mysql_connect("localhost","root","password") OR die("Incorrect username or password");
mysql_select_db("mysql") OR die ("Access denied !");
//If the user with such login already exists print the error.
$query="select * from user where user="$login"";
$result=mysql_query ($query) or die (error ($script,$login,$pass,$db));
if (mysql_num_rows ($result)) {
print_header ();
?>
<h2>Error!</h2>
<p>This name already exists in database.
<p>Please, go back and choose another name.
<p><center><a href="javascript:history.back()">Back</a></center>
<?php
}
Another important thing we have to do is to check the presence of the single quote and “%” symbol in the user’s input because it can cause disastrous effects (for example database break-in).
elseif (ereg ("%",$login) || ereg (""",$login) || ereg ("%",$pass) || ereg (""",$pass)) {
print_header ();
?>
<h2>Error!</h2>
<p>Unallowed symbol in login name or password. You cannot use <b><font color="ff0000">%</font></b>
or <b><font color="ff0000">"</font></b>.
<p>Please,go back and choose another name.
<p><center><a href="javascript:history.back()">Back</a></center>
<?php
}
After that you can enter the information about user to the database. It is executed by the MySQL database where only root has access to.
else {
//Insert data to the user table.
$query="insert into user (host,user,password) values ("localhost","$login",password ("$pass"))";
$result=mysql_query ($query) or die (error ($script,$login,$pass,$db));
//Create the database with name that coincides with user’s login
$query="create database $login";
$result=mysql_query ($query) or die (error ($script,$login,$pass,$db));
//Give user all privileges except grant.
$query="grant all privileges on $login.* to $login identified by "$pass"";
$result=mysql_query ($query) or die (error ($script,$login,$pass,$db));
//Now we have to delete all logs where host="%", if any
$query="delete from user where host="%"";
$result=mysql_query ($query) or die (error ($script,$login,$pass,$db));
$query="update db set host="localhost" where user="$login"";
$result=mysql_query ($query) or die (error ($script,$login,$pass,$db));
//Print the information to the page.
print_header ();
?>
<p align=right>| <a href="<?php echo $script ?>">Enter to database</a> |
<p><h2>Hi,<font size=4 color="0000ff"><?php echo $login ?></font>!</h2>
<p>You were successfuly registered in database.
<p>Your login:<?php echo $login ?>
<br>Password:<?php echo $pass ?>
<br>Database:<?php echo $login ?>
<?php
}
We also have to show the listing of the print_header () and error () functions.
function print_header () {
?>
<html><head><title>MySQL administrator</title>
<script language="javascript">
<!--
function fill () {
name="guest";
passwd="111";
document.form.login.value=name;
document.form.pass.value=passwd;
}
//-->
</script>
</head><style> A:link {font-family:arial;font-size:10pt;text-decoration:none;color:#0000ff;}
A:hover {font-family:arial;font-size:10pt;text-decoration:none;color:red;}";
A:visited {font-family:arial;font-size:10pt;text-decoration:underline;color:#000080;}
BODY {background-color:#f6f8fa;font-family:arial;font-size:10pt;color:#333300;}
TD {font-family:arial;font-size:10pt;color:#333300;}
TH {font-family:arial;font-size:10pt;color:#000080;font-weight:bold;}
H2 {text-align:center;color:blue;}
H4 {text-align:center;color:#000080;}
H5 {text-align:center;color:#000080;}
</style><body>
<p align=right><font face="Impact" size=7>
<font color="bfbfbf">M</font><font
color="aaaaaa">Y</font><font color="959595">S</font><font
color="7f7f7f">Q</font><font color="aaaaaa">L</font><font
color="bfbfbf">A</font><font color="aaaaaa">D</font><font
color="959595">M</font><font color="7f7f7f">I</font><font
color="aaaaaa">N</font>
</font>
<?php
}
//error () function is used for showing the connecting to the database error
function error ($script,$login,$pass,$db) {
?>
<h4>Error!</h4>
<p><b><font size=3 color="0000ff">Message from MySQL received:</font><br><font color="ff0000">
<?php echo mysql_error (); ?></font>
<p><center><?php echo home ($login,$pass,$db,$script) ?></center>
<?php
}
That’s it. Registration is completed.
