Best way to create new users? "CREATE USERS" or a separate table?

[thomasjon]

Hi,

 

  I'm pretty new to PHP, having only written code for one web site. I'm planning my next project and in this project there will be a set of users who will be registered by an "admin" user. The admin may add or delete new users through time. Each user will have account information and, of course, a password and username.

  The problem is, I'm not clear on what is the best way to establish users of a website with PHP. Is one of the following approaches cleaner or is there some other suggestion you can offer me?

 

1) Using PHP to send the SQL statement "CREATE USER <user_name> PASSWORD <password>" to create my users for my MySQL db

 

. . . or . . .

 

2) Creating in my MySQL db a table (e.g. Table "Users") for storing new users, their passwords, etc.

 

  Thanks in Advance,

 

  J.T.

 

[cooldude832]

It sounds like you haven't ever tried mysql.  If that is the case I'll give you a bit of an explanation for you, but you should really look up tutorials on mysql so you understand how to use it right. 

 

Basicalyl you will want to have 1 table with at least the following 3 fields

UserID (primary key auto inc.)

Username (textual represntation of the UserID use for logging in)

Password (secondary piece of information to prevent unwanted account access)

 

I explained them because a lot of people misunderstand the purposes of usernames/passwords.

 

For adding users just make a form that says username,password and what ever else you want in it and on the process page design a query similar to

$q = "Insert into `users` (Username, Password, OTHER FIELDS) Values('".$_POST['username']."','".$_POST['password']."')";

 

Since you are adding user names no worry for injection which you should read up on

 

Then for login just simply design a form with the username/password fields and query like

<?php
$q = "select FIELDS YOU WANT from `users` where Username = '".$username."' and Password= '".$password."'";
$r = mysql_query($r) or die(mysql_error());
if(mysql_num_rows($r) ==1){
//Right login info
}
else{
//wrong info
}
?>

this is a very basic idea and you will need to build the table off php or use phpmyadmin which will save you a lot of headaches in design.

[btherl]

The typical setup is that you use a single MYSQL user account, and have a users table to keep track of your application level users.  It's much cleaner that way.  That's the approach cooldude is showing.

 

Since you are adding user names no worry for injection which you should read up on

 

This is true ONLY if you check your input first, and remove any unsafe charactes.  Same with the password.

[thomasjon]

Thanks cooldude832 & btherl, I think my question has been answered. I actually did in my one and only PHP project use MySQL to store & retrieve to a couple tables in a database, but I haven't used much SQL statement in the past. Hence, I wasn't clear whether I should be using the SQL statements like "CREATE USER" to manage my users or what. But it sounds like its a lot cleaner to work with one user and manage the users out of a table.

 

Thanks!

 

J.T.

[cooldude832]

If you can figure out how to add phpmyadmin (or already have it) it will help you a  lot in building a table/adding fields to it.  There are a lot of small add-ons you can apply to this once you get your simple registration/login system built.  Such as using md5( ) to protect users passwords, injection protection, password recovery systems (with md5 in place you can only replace it and email the user their password as md5 is a one-way encryption technique), capctcha on the login script to prevent bot spaming your database and many other things.  I don't know if you have thought about this yet so I will address it for you so you don't have to post a secondary question.  Handling logins is a simple step really using that script I showed you, but what to do after that? I will fill in those ifs now

<?php
$q = "select FIELDS YOU WANT from `users` where Username = '".$username."' and Password= '".$password."'";
$r = mysql_query($r) or die(mysql_error());
if(mysql_num_rows($r) ==1){
while($row = mysql_fetch_array($r)){
$_SESSION['UserID'] = $row['UserID'];
}
}
else{
die("Invalid Login Info");
}
?>
then on protected pages at the top say (session_start() must be above any output/headers or it fails)
[code]
<?php
session_start();
if(empty($_SESSION['UserID'])){
die(include("login.html"));
}
else{
//Show the protected stuff and requery as needed since we only stored that userid as a session
}
?>

As to storing only the UserID as a session there is a balancing act to be made between storing data in sessions/requerying.  On a very large server where hundreds of users could be logged in at any time storing a lot of data in sessions can kill your server, so it be best to limit it to a single piece of data that can contain all the other data (the userID), however this runs a second delima because you will have to run a query on every page load to show any relative data to the user (which might not be a problem because the data could be so dynamic that it chagnes on each page (such as table being pulled from or specific fields needed)  There is really no problem running a couple of queries on each page as long as you optimize them (use joins/inner joins where needed and don't use the * as the fields, but select all fields you want instead)  Also i know this is a simple task, but when designing effective scripts majority of the issues on speed start when you try and over store data in your tables.  Every time you go to add a row to a table that contains data that isn't an ID linking to another table ask your self should I put this here.  If it makes more sense to be only in 1 table instead of being in 10 different tables as text, add it to the one then reference it via that row's id number in the tables you need it.  MySQL is an art at best in both querying and structure that makes it such a powerful databasing language, plus its opensourceness makes it a very attractable choice for a low budget project.[/code]

[btherl]

Cooldude, PARAGRAPHS please   One big long stream of text may be easy to write, but it's very difficult to read.