Database construction help needed

[aclips]

I have a little big doubt with a database for a simple login form for personal accounts. Let me try to explain what i intend to make:

• I need to create a database (mysql) to work with a login for, the idea is to procide acess with redirect url for each user personal account, something like this:

User1 + Pass1 = accounts/user1.htm
User2 + Pass2 = accounts/user2.htm
User3 + Pass3 = accounts/user3.htm
...
Usern + Passn = accounts/usern.htm

To work with the following PHP login script:

<?php
session_start();

// Code starts here

if (isset($_POST['login'])){

foreach($POST as $key => $val)
{
$SESSION[$key] = $val;
}

if ($POST['username']=='' || $POST['password'] ==''){

$errors = "Please fill in both fields.";
header('Location:'.$_SERVER['PHP_SELF']);
}

$query = sprintf("SELECT * FROM users
WHERE username=%s AND password=%s",
quote_smart($POST['username']),
quote_smart(sha1($POST['password'])));

$result = mysql_query($query) or die(mysql_error());

$num_rows = mysql_num_rows($result);

if($num_rows ==0) {
echo "Please try again your <li>Username</li>
<li>Password</li> combination is invalid!";
} else {
while ($row = mysql_fetch_array($result)) {

$SESSION['username'] = $row['username'];
$SESSION['level'] = $row['level'];
$_SESSION['login'] = true ;
}

switch ($_SESSION['level']){ //compare access level

case 1:
header('Location: users1.php'); //redirect
break;
case 2:
header('Location: users2.php'); //redirect
break;
case 3:
header('Location: users3.php'); //redirect
break;
default:
header('Location: login.php'); //redirect
}

}
} else {

?>

<form method="post" action="">
<input type="text" name="username" size="20"
<?php if(isset($POST['username'])) {
echo "value=\"".$POST['username']."\"";

}
?>>
<br />
<input type="password" name="password" size="20">
<br />
<input type ="submit" name="login" value="Login">

</form>

<?php
}
?>

I hope i was clear enough and that there is someone out there that can really give me a hand on this issue.

Thanks

[rincewind456]

Well presumably you will be using some form of registration, so you will need to gather users info, so with that in mind this would seem like a good option

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `firstname` varchar(20) default NULL,
  `lastname` varchar(20) default NULL,
  `email` varchar(255) default NULL,
  `username` varchar(20) default NULL,
  `password` varchar(50) default NULL,
  `level` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  ;

Don't forget that you will need to add the quote_smart() function to the code you have posted, as the script will fail otherwise.

[aclips]

rincewind456 wrote:

Well presumably you will be using some form of registration, so you will need to gather users info, so with that in mind this would seem like a good option

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `firstname` varchar(20) default NULL,
  `lastname` varchar(20) default NULL,
  `email` varchar(255) default NULL,
  `username` varchar(20) default NULL,
  `password` varchar(50) default NULL,
  `level` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  ;

Don't forget that you will need to add the quote_smart() function to the code you have posted, as the script will fail otherwise.

Thanks for the reply rincewind456, in fact i dont need/want users to creat their on account, i want to grant them limited acess, with a specific uer/pass, always with the admin full control.

So Based in this i think the db_user will be limited to:

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `username` varchar(20) default NULL,
  `password` varchar(50) default NULL,
  `level` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  ;

I hope i just understood your mysql code posting :rolleyes:

rincewind456 wrote:

Don't forget that you will need to add the quote_smart() function to the code you have posted, as the script will fail otherwise.

Sorry for my ignorance, but i'm not getting your point, any help here? :p

[rincewind456]

If you look at your code you are using the quote_smart() function here

quote_smart($_POST['username']),
quote_smart(sha1($_POST['password'])

That function is not one of the built in PHP functions and will need to be added to the code you have posted.

The quote_smart() function is:

function quote_smart($value)
{
   // Stripslashes
   if (get_magic_quotes_gpc()) {
       $value = stripslashes($value);
   }
   // Quote if not integer
   if (!is_numeric($value)) {
       $value = "'" . mysql_real_escape_string($value) . "'";
   }
   return $value;
}

[aclips]

Thanks rincewind456 !!