database assisstance

[coldn1ght]

I'm not sure if it belongs here, but i'll just post it incase you guys have the answer.

 

 

i have more  newbie question regarding the database.

 

Situation:

Database---

 

(table-1)

primary key id_num, number, not null,autoincrement,

username,not null

 

(table-2)

lastname

firstname

(id_num) foreignkey

 

 

 

so everytime I issue an insert command to' username'

 

does the database auto-creates a new id_num(uservalue)  which is id_num+1 or I have to issue a php code to create a new one together with the insert code? <<<--- 1st question

 

if the case is I have to issue a php.code createnew id_num(uservalue) for a new user, should I issue a php code to store "id_num(uservalue)"  in both table1 and table2? <<<--- 2nd question

 

If possible can you give me a sample_code and a sample_db so I can figure out, how it works.I apologize for such a newbie request-- "So PHP/mySQL noob here".

[br0ken]

The AUTO_INCREMENT field does exactly what it says... it automatically increments meaning that when ever you create a new record, the ID is automatically filled in for you. This answers both of your questions. Here is a sample INSERT query for table-1.

 

INSERT INTO table-1 (username) VALUES ('user1984')

[coldn1ght]

hmm,

 

so i don't have to issue insert into u_id, right? since the mysql makes a new u_id record everytime I issue an Insert code. correct?

 

 

if thats the case is my u_id in table 2 is also updated like that of u_id table 1?

 

because i want to access lastname,firstname in table2 and uses the u_id(foreign key) as WHERE.

 

another question:

 

how can I set u_id(table 2) as a foreign key that refers to u_id(table1) as the primary key.? using phpmyadmin.

 

 

 

 

[br0ken]

Firstly your tables are slightly wrong. This is a corrected version. The definitions below are not valid SQL but should just be used as a guide for ceating the tables properly.

 

tblUser
id INT AUTO_INCREMENT PRIMARY KEY
username VARCHAR(60)

tblProfile
id INT AUTO_INCREMENT PRIMARY KEY
firstName VARCHAR(60)
surname VARCHAR(60)
userID INT FOREIGN KEY(tblUser.id)

 

So to join these two tables together to retrieve user id 17 you would use the following query:

 

SELECT u.id, u.username, p.firstName, p.surname FROM tblUsername u, tblProfile p WHERE u.id = p.userID and u.id = 17

 

 

All auto_increment fields are incremented automatically when ever you create a record in that table.

[coldn1ght]

thanks i think i get the point now .

 

more help is still welcome.

[br0ken]

More help regarding what?

[coldn1ght]

tblUser
id INT AUTO_INCREMENT PRIMARY KEY<<----  how to properly set a primary key in phpmyadmin?
username VARCHAR(60)

tblProfile
id INT AUTO_INCREMENT PRIMARY KEY
firstName VARCHAR(60)
surname VARCHAR(60)
userID INT FOREIGN KEY(tblUser.id) <<---- how do I do this using phpmyadmin and establish a relation to tbluser?

 

1. Im quite lost the values of "id" in both tables are the same as well as the value  in "userID" right?

 

2. Why do I have to make a primary key "ID" in tblProfile when I already got UserID as a foreign key? wouldn't be UserId  enough to establish a relation?

 

3. if id=1810 when it auto increments does the next value for id=1811 or some random number generated by mysql?

 

 

sorry for being so noob it's kinda confused me.

[br0ken]

1. The userID field in tblProfile will correspond to the ID field in tblUser. The ID field in tblUser and tblProfile are not linked what so ever.

2. If you're going to have a one-to-one relationship between the tables (meaning that one user will only ever have one profile) then you might as combine the tables into one big table which would solve this problem.

3. Yes, AUTO_INCREMENT will increase the value by 1 each time.

 

I recommend doing some reading on basic SQL skills, it will help you out a lot.

 

http://www.w3schools.com/sql/default.asp