Newbie Help on my database and php code

[coldn1ght]

I have a problem with the database tables, I cant seem to link to table, I tried to click the primary key button in the phpmyadmin.

 

my db set up is like this:

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

db_name  - test

 

table person

 

username - clicked primary key button in phpmyadmin

 

password

 

table specifics

 

username - clicked the unique key button in php my admin

 

firstname

 

lastname

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

and then i clicked on the relational view and set the "username in specifics" to reference on person->username.

 

im not sure if what i did was right, but the program didn't work, so I assume I'm wrong.

 

so here is my php code for insert.php:

<?php
	$host = "localhost";
	$user = "root";
	$pass = "arfarf";

	$con = mysql_connect($host,$user,$pass);

		if(!$con)

		die("Could not connect".mysql_error());

	$select_db= mysql_select_db('test',$con);
		if(!select_db)
		{
		die("Failed to connect to database".mysql_error());
		mysql_close();
		}
	$username = $_REQUEST['username'];
	$password = md5($_REQUEST['password']);
	$firstname = $_REQUEST['firstname'];
	$lastname = $_REQUEST['lastname'];
	$query = mysql_query("SELECT * FROM person WHERE username = '$username' ");
	if (mysql_num_rows($query)!=1)
		{
		$sql= "INSERT INTO person(username,password) VALUES
		('$username','$password') ";
	if (!mysql_query($sql,$con))

		die ("failed to add record: ".mysql_error());

	else
	$sql= "INSERT INTO specifics(firstname,lastname) VALUES
		('$firstname','$lastname') ";

		if (!mysql_query($sql,$con))

		die ("failed to add record: ".mysql_error());

	else

		echo include("index.php");
		}
	else
		{

		echo "<font face='tahoma' color='red'> Username already exists. </font>";
		include("signup.php");

		}

mysql_close();
?>

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

 

this is the error I always get when I run the script:

 

Failed to add record: Field 'username' doesn't have a default value

 

note:

 

when i viewed my database after running the script, this is what happened.

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

the $username and $password that the user inputs is stored in persons table under username and password field, but when I opened the username field in the specifics table, its empty.

 

 

my problem is honestly im not sure what? but the way I see it, I think its on how to:

 

• link my two tables.
  
• how to set my username field  as primary key for "person" table and a foreign key for  "specifics" table.(using phpmyadmin)
  
• how to have the same data between  2 "username" in both field.
   
  

 

or please tell me where my problem really lies thank you.

 

please reply here or email me at miomerchie@gmail.com or contact me ymsgr korudekorude@yahoo.com

 

 

 

 

[coldn1ght]

??? wah help please <BUMP>...

[hitman6003]

It tells you what the problem is:

 

Field 'username' doesn't have a default value

 

In order to be a primary key the column has to be both unique and non-null.  You don't have to set a default value (and you shouldn't in this case), but you have to insert something into that column EVERY time you issue an INSERT statement against it.

 

Why are you using a text field as a primary key anyway?  There are times when it is appropriate, however this isn't one of them.  Because you are using it as a foreign key in another table, that means it must be duplicated many times over.  This leads to storage bloat and very large indexes.

 

Look at it this way..."username" is probably a VARCHAR, and probably ~ 30 characters in size.  This means that for every record in that table, the index uses 30 bytes to store it...in addition to the actual storage on disk for the tuple's data.  Now, when you use that column as a foreign key in another table, you (should) have an index on the foreign key column...another 30 bytes.  If it's an InnoDB table, each additional index stores a reference to the primary key (30 bytes x # number of indexes)...

 

Even if you only have the two indexes...primary key and foreign key...imagine how much RAM the indexes will consume if you have a million users (60 MB)...just for one table!

[coldn1ght]

wow thanks for the input.

 

never thought about bloating. now I know i have to consider storage memory.

 

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".

 

 

 

 

 

[coldn1ght]

bump ('").

[hitman6003]

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?

 

So long as it is an "auto-increment" column, the database will automatically insert a one-up value.

[DarkWater]

Also, it would be easier to have something like:

 

ID  Username  Password  Firstname  Lastname

 

All in one table. =X

[coldn1ght]

oh yeah, I'm just giving out an example so I will know how to establish a relation in both table.

 

since I'm making a registration form which inserts the value into the database and then outputs a members page which will also require to select data from database and output it.

 

which brings me to another question how can I set a foreign key using phpmyadmin?

 

do you guys have a sample code for a process.php(registration) and then (members.php)<< how to extract the data from different tables which are linked together with the primary and foreign keys table.

 

and if possible can you show me the database structure so I can understand more how the things work?