create table when user creates account?

[teambiohazard]

hello all,

how can I add a user to a table in a mysql database without datagridview ?

I already have this:

Dim conn As MySqlConnection
        'connect to DB
        conn = New MySqlConnection()
        conn.ConnectionString = "server=localhost; user id=root; password=test; database=test_addtable"
        'see if connection failed.
        Try
            conn.Open()
        Catch myerror As MySqlException
            MsgBox("Error connecting to database!")
        End Try
        'sql query
        Dim myAdapter As New MySqlDataAdapter

        Dim sqlquery = "SELECT * FROM user WHERE username = '" + registerform.usernametextbox.Text + "'"

        Dim myCommand As New MySqlCommand()
        myCommand.Connection = conn
        myCommand.CommandText = sqlquery
        'start query
        myAdapter.SelectCommand = myCommand
        Dim myData As MySqlDataReader
        myData = myCommand.ExecuteReader()
        'see if user exists
        If myData.HasRows = 0 Then

            conn.Close()
            conn.Open()
            Dim registerfinal As New MySqlDataAdapter

            sqlquery = "INSERT INTO user (Username, Password, Email) VALUES ('" + registerform.usernametextbox.Text + "','" + registerform.passwordtextbox.Text + "','" + registerform.emailtextbox.Text + "')"
            myCommand.Connection = conn
            myCommand.CommandText = sqlquery
            'start query
            registerfinal.SelectCommand = myCommand
            myData = myCommand.ExecuteReader()
            MsgBox("New user has been added to the database!")
            'add close
        Else
            'already exists
        End If

where to add the create table??

I need a table which contains the folllowing data:

CREATE TABLE  `test_addtable`.`username**` (              **must be username
`id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Name` VARCHAR( 45 ) NOT NULL ,
`Downloadlink` VARCHAR( 45 ) NOT NULL ,
`Description` TEXT NOT NULL
) ENGINE = INNODB;

[/LIST]

how to solve this ??
Thanks in common!

Cheers!

[hericles]

Firstly, do you really want to create a new table for every user just to record their name, link and description? If your site/app becomes even reasonably popular that amounts to a lot of tables. That data can easily be kept in one table for everyone.
If you need to create the table (I'm assuming in the ELSE part of the loop) it the same as executing any other scalar operation on the database. Set your SQL string and use the command object as you already have done to insert or extract the data.

[adam_k]

I totally agree with hericles. It's bad practise to create a table per user. Add a username field and use that to relate to the users table. This way you'll be able to join way easier, have way less tables to manage (ie what happens if in a year from now you decide to add a column? You have to query every single table and verify the table name against the users table...... you get the picture).

[teambiohazard]

I want to do this because of a simple friend list thing, or is there another way to achieve this?

[adam_k]

Can you please explain the simple friend list thing, or even better what you are hoping to achieve with your approach?

Usually when we want to record something to a database we create a table with a specific "job" (ie a table to hold user info or a table to hold transaction history) and then add as fields all the necessary info around that "job" (ie user that did the transaction).
We never create a table to hold a specific users transaction. That could be a function (that accepts a username and returns all transactions around a user) or something similar.
By creating a table with the username as table name you've set the username as part of the design and not of the data.

PS: I know I've cut a lot of cornerns when describing the usual way of creating tables. Other vars come in play like the size of a table and the frequency each field will be used (who needs the address of an order when checking stock for a very rough example)

[teambiohazard]

What I want to achieve is:

- when a user creates a account it will make a table with "username"_friends
- it needs to hold the following data:
- id
- friend
- some aditional data ( when I want to, I can add it.
and I know that there is a way around it as in like not to create a table. but creating one seems the most easy way to me.

[adam_k]

In a scenario that your db holds like 1000 users how are you going to add the additional fields the easy way?
And how are you going to join the table to the user? You'll have to go dynamic the whole way, which would probably mean a lot of back and forth for a pretty easy query and making some everyday ones a total nightmare to achieve (in example display username, count of friends for all users in db).

The easiest way possible is to create a table like:
id
username
friends_username
whatever

this way you can (if the above table is friends)

select * from users left join friends on users.username = friends.username

and be done with it.
This structure with a join will be all you're going to need. You can find all friends for a user with a left join (having users in the left) or find all users that are friends with somebody with a right join(having users in the left) or count the number of users or do whatever you want with it.