[SOLVED] mysql tables

[asmith]

can i make a table in mysql which can had variable field numbers ?

for example :

    username      password      e-mail

1 :  abc            ddd              [email protected]

2:  qwe            aaa            [email protected]        added field here

hmm hard to explain like this ,

 

i want to make a table which row 1 has 3 fields , and for example row 2 , have 5 fleds,  if needed row 3 would have 10 fields ...

is that possilble ?

[~n[EO]n~]

Yes it is, if there is no value it will be NULL.

[asmith]

so how can i add fileds just to some rows ?

got any special codes?

[~n[EO]n~]

When you create your table keep as many fields you require, later you can ADD column using ALTER

ALTER TABLE `tablename` ADD `another_field` VARCHAR( 25 ) NOT NULL ;

 

There is no special codes. Just keep blank '' e.g.

INSERT INTO table (
`id` ,
`email` ,
`password`
)
VALUES (
NULL , '[email protected]', ''
);

 

See this code will write nothing in password field it will remain blank.

You should try yourself before asking and see what happens.

 

Download MySQL Reference Manual and you will find all by yourself.

[asmith]

nonono ! i want to have for example these 3 fields, and add a 4th fields only to one row!

 

[rajivgonsalves]

You cannot achieve that in a database you have to have a field, If that is your requirments store them as deliminated values in one field not advisible tho

[BenInBlack]

As was stated this can't be done if you are trying to add fields, but if you rethink this, a sql database is row centric not column centric, what I mean is the sql language is designed to handle almost infinite rows but finite columns

 

so flip your thinking to this

 

set up a table with 4 columns

 

create table UserInformation (

uid int(11) autoincrement  not null - primary key,

Id int(11) not null,

Fact varchar(40) null default '',

Value varchar(255) null default '')

 

now lets take 2 persons with diff number of facts

person 1 has name, userid, password & email

person 2 had name, userid, password, email, homephone, ext, birthdate

 

so we add the data so that we use the id column as a grouping for the facts

Id Fact Value

---- ------------- -----------------------

1      name          mr. person 1

1 userid person1

1 password pass1

1 email [email protected]

1      name          mrs. person 2

2 userid person2

2 password pass2

2 email [email protected]

2 homephone 5551212

2 ext 123

2 birthdate 1/1/1960

 

you can see that with a grouping column you can now have any number of fact for an individual

and just as long as you make sure that the Fact column uses values that are the same across groups you can do a lot of cool thing because the where clause in sql is design to work with rows.

 

so lets say you want to get a list of all the persons names

 

select ID,Value from UserInformation where Fact = 'name'

 

results in

ID    Value

---    -------

1      mr. person 1

2      mrs. person 2

 

you can see that you can get a list of people in the table along with there grouping code or ID

 

so then you could go

 

select Fact,Value from UserInformation where ID = 2

and you can loop thru the results.

 

You be the judge, but I think by just turning your head to the side you can see the world a little differently ;-)

[asmith]

ah got the whole point ! great answer ! thanks !