Ok i am having a hard time understanding how to set up a database right,
lets say i have a login table and a profile table.

Now when someone signs up php sends a query to the database to see if the username that the person chooses is in use if it is not it will insert the form information into the login table now how will mysql know to make that person a profile table of their own without aanything being put into the profile table?

Sorry if this is not explained good but hopefully someone will understand what i am talking about.

mysql won't "know" how "to do it" YOU have to design the schema, the tables and the queries to handle the data

That's what being a DBA is all about.

What exactly would i do? would i set up the signup form so that when someone signs up they send info to all the tables that i need their information associated with?

Exactly!

Lets say i have a signup for that asks for username , password and email what part would i send to other tables the username? or would something else be better?

You would store each variable in one table.

Table = Users

Field1=Username
Field2=Password
Field3=Email.

How would i make the other tables know that they are associated with the user table then?

MySQL tables don't "think". You are free to design relational tables (which hold information about other tables), but you will need to use a programming language to create, then interpret, the association between them.

I understand that part but what i am asking is how would i do that? would i do it with a username a id, i understand i have to link them some way i just need to know if their is a right way to do it or if i just do it anyway that works?

Originally Posted by Knight13

I understand that part but what i am asking is how would i do that? would i do it with a username a id, i understand i have to link them some way i just need to know if their is a right way to do it or if i just do it anyway that works?

This article will help you with this step of database normalization:

http://dev.mysql.com/tech-resources/...alization.html

Regards

Thanks UNIXy i will read that tomorrow when i have time.

Originally Posted by Knight13

How would i make the other tables know that they are associated with the user table then?

Let's say you have a table which holds basic user information, such as the user-name, password, and an auto-incrementing number which we call "user_id". "user_id" would be the primary key, because it will always be unique.

Now, lets say we want to keep profile information about that user. We create another table, which contains various fields, but which also has a one field named "user_id", which tells us which user the information belongs to.

Although this isn't a direct answer to your question (I'm not even sure what your question is, to be honest), you can perhaps see from this example how tables can "know" what other tables are associated with it.

If I have a bunch of tables that are associated, I will sometimes build one relational table. Here's one I dumped from one of my pet projects:

  image_id int(11) NOT NULL auto_increment,
  page_id int(11) NOT NULL,
  live_title_id int(11) NOT NULL,
  content_id int(11) NOT NULL,
  image_library_id int(11) NOT NULL,
  last_edited timestamp NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (image_id)

This table is about images, it is used to remember which image belongs where, inside of a content management system. It only has information about other tables, but it tells me exactly where a particular image is located. This way I don't need to repeat information like the page name, image path, etc, because all of that information is reusable and shouldn't be using extra space.

I know this is a little bit off subject, but I hope it helps you understand how data can be structured. I'm not the database expert around here, but maybe my intermediate level of understanding can help you.

I understand what you are saying wayfarer07 but what i don't understand is how i get the information into 2 tables at once.

When a person signs up do i send a query to the database and tell it to insert lets say the username into all the tables that i need to join together or is their a certain way that it is done?

You're just going to have to get your hands dirty on this one. Write separate queries for each time you need to store information. Break apart your needs into chunks of logic, then work out exactly what you will need to do in order to put what data where, and read it.

I recommend you read up on MySQL a bit more. There is a fairly good tutorial at W3schools: http://www.w3schools.com/PHP/php_mysql_intro.asp

what i don't understand is how i get the information into 2 tables at once.
When a person signs up do i send a query to the database and tell it to insert lets say the username into all the tables that i need to join together or is their a certain way that it is done?

The idea behind a relational database is not to save the same data in multiple place.
You save it in 1 place, and make relations to it from other places.

If I imagine that you have a "users" table, and a "profile" table that looks like this:

create table users (
  id integer not null auto_increment,
  username varchar(100) not null,
  password varchar(255) not null,
  signupDate timestamp not null default now()
);

create table profile(
  userid integer not null,
  sex char(1),
  birthdate date,
  email varchar(255)
);

When a user hit the "submit" button of the signup form, you need to do 3 things:
1) insert a row in the table users
2) get back the id of the primary key of the row created
3) insert a row in the "profile" table, with a link to the user id

The first point is easy:

PHP Code:

$user=trim(addslashes($_POST['username']));
$pass=my_super_encryption(trim(addslashes($_POST['password'])));
$query="insert into users (username, password) values ('$user', '$pass')";
$res=mysql_query($query); 


This insert the row in the "users" table.
Now, as the id column have an autoincrement attribute, we don't specify it, but we need to get it back:

PHP Code:

$userId=mysql_insert_id(); 


And there it is.
And now, for the last point:

PHP Code:

$sex=(isset($_POST['isMale']) && ($_POST['isMale']==1))?1:0;
$birth=(isset($_POST['birthdate']))?trim(addslashes($_POST['birthdate'])):'null';
$email=trim(addslashes($_POST['email']));
$query="insert into profile ( userId, sex, birthdate, email) values ($userId, '$sex', $birth, '$email')";
$res=mysql_query($query) 


And there you are.
You have inserted datas in 2 tables and created a link between the 2 rows by the user id

Thank you tripy that is exactly what i wanted to know, thank you to able you guys told me exactly what i needed to know.