MySQL user database

[sysop]

Ok, I would like to build a web form thar users can submit some things into a database. The catch is I would like them to login on a page before hand, setting what user logs into mysql on the submit page.

I would like this user database use the username as the key, and also have a password that is randomly generated and emailed to the user supplied email address in the database, for varifacation, and that the user can change at a later time....

What kind of table setup am I looking at for this database?

[kid_c]

there are a lot fo specifics you are missing for me to give you a full database schema...so i'll try to give you something generic to start you off:

CREATE TABLE users (
uid int(8) not null auto_increment primary key,
users_username varchar(12) not null,
users_password varchar(12) not null,
users_email varchar(80) not null
);

of course the way you create this table would depend on what DBMS you are using (MySQL,PostgreSQL,etc...)

you could probably add more fields in users table with things like phone number, name, etc....depends on what you are looking for...

i recommend using a uid (integer auto_increment) as a primary key in place of a (slower) text field.

as for verifying their email, just write a simple function to generate a random password, when they signup, email them it, and insert it into the DB with the rest of their info.....when they goto your confirmation/verification page and enter their username/random password, if its good give them the option to then enter a new password. (and update it in the D😎.

If you need anymore help, please provide more specific details =)

--
Keith Bussey
[e-mail]
Programmer - WISOL.com
[phone] ext. 225

[sysop]

Hrmm, I thought the Topic said it, MySQL database....

Ok, I guess I could use the int auto_increment as the key, and also have a feild for the user.

How would you suggest I fill the password feild to begin with? I don't want to allow nulls there, so fill with 0's?

Just for specific's, this will be MySQL, PHP4, and Apache on a Redhat 7.1 box.

[kid_c]

sorry, hadn't slept in 3 days guess I missed the mysql in the topic =)

well, you shouldn't have to worry about having NULL or 0s in your password field....it will either be the random password OR their chosen password....never anything else

when you insert into to create their user record, password will be the random one generated.

when they change it, it will update to whatever they chose.

So I don't see you having to fill the password field to beign with

--
Keith Bussey
[e-mail]
Programmer - WISOL.com
[phone] ext. 225

[sysop]

Hehe, no worries.

I'll login to the database and start creating these feilds.

What would the syntax be?

I want a feild for uid (int auto incramented), username, email, password, and a timestamp of when they registered.....

[kid_c]

CREATE TABLE $tablename (
uid int(8) not null auto_increment primary key,
$tablename_username varchar($length_user) not null unique,
$tablename_password varchar($length_pass) not null,
$tablename_email varchar($length_email) not null unique,
$tablename_timestamp int(12) not null
);

$tablename = whatever you want to call the table
$length_user = max length for your usernames
$length_pass = max length for passwords
$length_email = max length for emails

few tips:

• index all fields that you'll ever use in a WHERE clause
• store timestamp as unixtime (ie. time() function in PHP)

That's it!

--
Keith Bussey
[e-mail]
Programmer - WISOL.com
[phone] ext. 225