Can jsp be used in SQL statements?

[styx218]

I am developing a fantasy football application.When the user makes choice of players, i need them to be stored in a seperate table,that applys to only them,so that when the user is logged in i can retrieve the values from the table and display in a bean.

So it will be titled whatever the username is at the time.. So it will be something like

CREATE TABLE `//username goes here` (



im using jsp but i would imagine to insert the username as the title of the sql i would need a javascript function??

my table will look something like this,although i may have to alter it,as the values of the players may need to saved also


`goalkeeper` char(100) default NULL,
`leftback` char(100) default NULL,
`centreback1` char(100) default NULL,
`centreback2` char(100) default NULL,
`rightback` char(100) default NULL,
`wingleft` char(100) default NULL,
`centmid1` char(100) default NULL,
`centmid2` char(100) default NULL,
`wingright` char(100) default NULL,
`striker1` char(100) default NULL,
`striker1` char(100) default NULL,

PRIMARY KEY (`//not defined yet`)
) TYPE=MyISAM;

Any help would be greatly appreciated with this

 

[chrissie1]

What happend to normalization??

Add the username as a field and you can have all the users players in one table.

Christiaan Baes
Belgium

"My old site" - Me

 

[styx218]

Hi Chrissie,

I am storing all the players in a single table, i just need each table name to be specific to the user name,there will be a different user name each time a team is registered,how do i integrate this into my sql statement

regards,
styx218

 

[timw]

Don't have a table per user. That's a bad design. There is a Many-to-Many relationship between users and players. Each user can have many players, and each player can be part of the fantasy team of many users.

So you want a table for users (holding just their user details, things like name, username, encrypted password etc) and a table for the football players. Have a single BIGINT as the primary key for these tables. Then you'd have a third table called a 'join-table' which relates the users and players together. Eg. if user 1 has players 2,5,7 and 9 then the join table will contain records holding 1 and 2, 1 and 5, 1 and 6, 1 and 9.

There must be thousands of hits in Google for 'How to do SQL many to many join tables'. Have a read around.

Tim

 

[styx218]

hi tim ive just had a read around and this is defintley the right approach,however the problem is that i have 5 tables of players (goalkeepers,fullbacks,midfielders etc)

At the moment i have primary key as "Player_Id". If i was to change primary key in each of those tables to "Goalkeeper_Id,Fullback_Id", How would i store these together in a join table? Beacause theoretically i could have user_id 1 with players 4,4,5,5 etc (duplicate values).Or would i create a join table for each of the tables?

Sorry if my question seems silly im just on a steep learning curve here

thanks and regards,
styx218

 

[ca8msm]

Why have a table for each type of player? Simply have one table with all players and have a field where you store what type of player they are.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.