The best way to set up a database table

[Trium918]

Hi to all, I was wondering could someone explain to me the

best and secure way to set up a database with the below.

 

memberid

username

email

fname

lname

password

address

city

state

zip

gender

contact #

[Trium918]

Any Help?

[bubblegum.anarchy]

field names and database security have little to do with one another.

[clown[NOR]]

the security comes first when you are starting to add, read and basicly open up your db... what I've learned from own experience, that time your db needs most security is when users adds info to your table... make sure you strip for "bad" html tags.. one bad html tag is <script>.. i dont have much experience ... so this is the most usefull info i can give you...

 

but you should wait for someone else to reply... i know most people in here will be able to give better advice than this... but i advice you to use strip_tags()

[bubblegum.anarchy]

Convert all HTML form data into the appropriate data type... especially string to numbers where they apply - consider incorporating the php settype function in your HTML post data type validation.

 

When using something like "SELECT * FROM table WHERE id = ".$_GET['id']

 

the $_GET['id'] value should be wrapped in a type conversion so you would have something more like "SELECT * FROM table WHERE id = ".ToNumber($_GET['id']) and the ToNumber() function would incorporate the PHP settype function to restrict anyone replace the url query string value to an query UPDATE statement - any string will be returned as 0 with the settype function, simple and effective.

[Trium918]

field names and database security have little to do with one another.

 

What are the advantages and disadvantages with the stucture

of this database I am attempting to build?

create database members_super;

use members_super;

create table members_info(members_id int unsigned not null auto_increment primary key,
-> user_name varchar(25) not null, first_name varchar(25) not null, 
-> last_name varchar(25) not null, gender varchar( not null,
-> contact_number int(10) not null, email_address varchar(100) not null  );

create table members_address(address_id int unsigned not null auto_increment primary key,
-> members_id int unsigned not null, street_address varchar(50) not null, 
-> city varchar(25) not null, state varchar(10) not null, postal_code int(5) not null  );

 

 

 

 

[bubblegum.anarchy]

Depends on the application and how large the database may be... for starters you can save a little = space by defining `gender` as char(1) - M or F and you should also add an index on members_id in members_address, and also consider adding some default values for all those not nulls.

[Trium918]

Depends on the application and how large the database may be... for starters you can save a little = space by defining `gender` as char(1) - M or F and you should also add an index on members_id in members_address, and also consider adding some default values for all those not nulls.

 

What do you mean by default value? I donnot understand.

[bubblegum.anarchy]

A default value is inserted into a column for a record when the column value in the insert statement is not specificied... say you have a table definition such as this:

 

CREATE TABLE table_name (

    id INT(10) UNSIGNED NOT NULL,

    info VARCHAR(255) NOT NULL DEFAULT 'My shoe is blue'

)TYPE=MyISAM;

 

when you perform an insert like this:

 

INSERT INTO table_name SET id = 1;

 

and then SELECT * FROM table_name would result in:

 

id      info

1        My shoe is blue

[Trium918]

Would it be more like this?

 

create database members_super;

use members_super;

create table members_info(members_id int unsigned not null auto_increment primary key,
user_name varchar(25) not null, first_name varchar(25) not null, 
last_name varchar(25) not null, gender tinyint( not null,
contact_number varchar(10) not null, email_address varchar(100) not null  );

create table members_address(address_id int unsigned not null auto_increment primary key,
members_id int unsigned not null, street_address varchar(50) not null, 
city varchar(25) not null, state varchar(10) not null, postal_code varchar(5) not null,
index(members_id) );

[bubblegum.anarchy]

Definately a good start... let's see what others will suggest.