mysql create a table

[nats01282]

Hi all,

Im new to php with mysql,

i have connected sucessfuly to my database,

But how do i go about creating a register script and login script?

I already have 10 page website created by me in html/php
But now i want to add a members area

i have tried with this code i found

<?php
$con = mysql_connect('xxx.xx.xxx.x.x, 'database name', 'database password') OR die("Error: ".mysql_error());
mysql_select_db('my database', $con) OR die("Error: ".mysql_error());

function protect($string){
    $string = mysql_real_escape_string($string);
    $string = strip_tags($string);
    $string = addslashes($string);

    return $string;
}
if(!$_POST['submit']){
    echo "<table border=\"0\" cellspacing=\"3\" cellpadding=\"3\" align=\"center\">\n";
    echo "<form method=\"post\" action=\"".$self."\">\n";
    echo "<tr><td colspan=\"2\" align=\"center\" bgcolor=\"#333333\"><font color=\"#ffffff\">Registration Form</font></td></tr>\n";
    echo "<tr><td>Username</td><td><input type=\"text\" name=\"username\"></td></tr>\n";
    echo "<tr><td>Password</td><td><input type=\"password\" name=\"password\"></td></tr>\n";
    echo "<tr><td>Confirm</td><td><input type=\"password\" name=\"passconf\"></td></tr>\n";
    echo "<tr><td>E-Mail</td><td><input type=\"text\" name=\"email\"></td></tr>\n";
    echo "<tr><td align=\"left\"><input type=\"submit\" name=\"submit\" value=\"Register\"></form></td><form action='login.php' method='POST'><td align=\"right\"><input type=\"submit\" value=\"Login\"></td></tr>\n";
    echo "</form></table>\n";
}else {
    $username = protect($_POST['username']);
    $password = protect($_POST['password']);
    $confirm = protect($_POST['passconf']);
    $email = protect($_POST['email']);

    $errors = array();

        if(!$username){
            $errors[] = "Username is not defined!";
        }

        if(!$password){
            $errors[] = "Password is not defined!";
        }

        if($password){
            if(!$confirm){
                $errors[] = "Confirmation password is not defined!";
            }
        }

        if(!$email){
            $errors[] = "E-mail is not defined!";
        }



        if($username){
            if(!ctype_alnum($username)){
                $errors[] = "Username can only contain numbers and letters!";
            }

            $range = range(1,32);
            if(!in_array(strlen($username),$range)){
                $errors[] = "Username must be between 1 and 32 characters!";
            }
        }

        if($password && $confirm){
            if($password != $confirm){
                $errors[] = "Passwords do not match!";
            }
        }

        if($email){
            $checkemail = "/^[a-z0-9]+([_\\.-][a-z0-9]+)*@([a-z0-9]+([\.-][a-z0-9]+)*)+\\.[a-z]{2,}$/i";
            if(!preg_match($checkemail, $email)){
                $errors[] = "E-mail is not valid, must be [e-mail]!";
            }
        }


        if($username){
            $sql = "SELECT * FROM `users` WHERE `username`='".$username."'";
            $res = mysql_query($sql) or die(mysql_error());

                if(mysql_num_rows($res) > 0){
                    $errors[] = "The username you supplied is already in use!";
                }
        }

        if($email){
            $sql2 = "SELECT * FROM `users` WHERE `email`='".$email."'";
            $res2 = mysql_query($sql2) or die(mysql_error());

                if(mysql_num_rows($res2) > 0){
                    $errors[] = "The e-mail address you supplied is already in use of another user!";
                }

        }

        if(count($errors) > 0){
            foreach($errors AS $error){
                echo $error . "<br>\n";
            }
        }else {
            $sql4 = "INSERT INTO `users`
                    (`username`,`password`,`email`)
                    VALUES ('".$username."','".$password."','".$email."')";
            $res4 = mysql_query($sql4) or die(mysql_error());
            echo "<font align=\"center\"><br><br>You have successfully registered with the username <b>".$username."</b> and the password <b>".$password."</b>!</font>";
        }
}
?>

when this code is ran i get a error saying

Table 'my database.users' doesn't exist

How do i solve this

[K0ns3rv]

You either create a database using some gui tool , like navicat or you excute a php script to create the table yourself.
You will also have to change the function on row 3 to correspond with your database name.

Navicat
Creating a table using sql query

The code for creating the sql query has to be in mysql_query() function to do anything ;D
For example

//Do connect stuff up here.

mysql_query(" CREATE TABLE example_innodb (
          id INT,
          data VARCHAR(100)
        ) TYPE=innodb;");

//k0ns3rv

[FlashCreations]

Well the first thing I see is you need an end quote in the first line (You also need to research the first function, mysql_connect: its host, username, password):

$con = mysql_connect('HOST', 'USERNAME', 'PASSWORD') OR die("Error: ".mysql_error());

You also have a problem with your protect function. Use the mysql_real_escape_string() function rather than stripslashes() . I would also not use pass-by-reference so you can modify the actual POST variable instead of creating a new variable for each field.

function protect(&$string) { $string = mysql_real_escape_string($string)); }

I would also suggest cleaning up your variable checking. So fixed you code should look like this:

<?php
function protect(&$string){ $string = mysql_real_escape_string($string); }

mysql_connect('HOST', 'USERNAME', 'PASSWORD') or die("Could establish a connection");
mysql_select_db('my database') or die("Could not connect to the database");

if($_POST['submit']!="Register")
{
    echo "<table border=\"0\" cellspacing=\"3\" cellpadding=\"3\" align=\"center\">\n";
    echo "<form method=\"post\" action=\"".$_SERVER['PHP_SELF']."\">\n";
    echo "<tr><td colspan=\"2\" align=\"center\" bgcolor=\"#333333\"><font color=\"#ffffff\">Registration Form</font></td></tr>\n";
    echo "<tr><td>Username</td><td><input type=\"text\" name=\"username\"></td></tr>\n";
    echo "<tr><td>Password</td><td><input type=\"password\" name=\"password\"></td></tr>\n";
    echo "<tr><td>Confirm</td><td><input type=\"password\" name=\"passconf\"></td></tr>\n";
    echo "<tr><td>E-Mail</td><td><input type=\"text\" name=\"email\"></td></tr>\n";
    echo "<tr><td align=\"left\"><input type=\"submit\" name=\"submit\" value=\"Register\"></form></td></tr>\n";
    echo "</form></table>\n";
} 
else 
{
    //Protect $_POST Variables
	protect($_POST['username']);
    protect($_POST['password']);
    protect($_POST['passconf']);
    protect($_POST['email']);
	
	//Define errors array
    $errors = array();

    //Validate POST Variables
	if(empty($_POST['username'])) $errors[] = "Username is not defined!";
	if(!ctype_alnum($_POST['username'])) $errors[] = "Username can only contain numbers and letters!";
	if(strlen($_POST['username'])>=1 && strlen($_POST['username'])<=32) $errors[] = "Username must be between 1 and 32 characters!";
    if(empty($_POST['password'])) $errors[] = "Password is not defined!";
	if(empty($_POST['passconf'])) $errors[] = "Confirmation password is not defined!";
	if(!empty($_POST['password']) && !empty($_POST['passconf']) && $_POST['password'] != $_POST['passconf']) $errors[] = "Passwords do not match!";
    if(empty($_POST['email'])) $errors[] = "E-mail is not defined!";
	if(!empty($_POST['email']) && !preg_match("/^[a-z0-9]+([_\\.-][a-z0-9]+)*@([a-z0-9]+([\.-][a-z0-9]+)*)+\\.[a-z]{2,}$/i", $_POST['email']))
		$errors[] = "E-mail is not valid!";
	if(!empty($_POST['username']) && mysql_num_rows(mysql_query("SELECT username FROM users WHERE username='".$_POST['username']."'"))!=0)
    	$errors[] = "The username you supplied is already in use!";
	if(!empty($_POST['email']) && mysql_num_rows(mysql_query("SELECT username FROM users WHERE email='".$_POST['email']."'"))!=0)
		$errors[] = "The e-mail address you supplied is already in the use of another user!";
	
	//Display any errors
    if(count($errors)>0) foreach($errors as $error) echo $error."<br>\n";
	//Else register the user
    else 
	{
    	mysql_query("INSERT INTO users (username,password,email) VALUES ('".$_POST['username']."','".$_POST['password']."','".$_POST['email']."')");
        echo "<p>You have successfully registered the username <strong>".$_POST['username']."</strong>!</p>";
	}
}
?>

Now about your problem, you simply need to create a users table with the fields username, password, and email.