Whats the difference when creating a table?

[Dark57]

So I was reading a tutorial on how to make a secure login script and it instructed me to use this script.

 

CREATE TABLE `members` (
`id` int(4) NOT NULL auto_increment,
`username` varchar(65) NOT NULL default '',
`password` varchar(65) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

 

That's the difference between that and something just like:

$sql = "CREATE TABLE members
(
id int,
username varchar(20),
password varchat(20),
)";

 

It has some other lines of code following the defnition's and some `s in it, but I don't see why I should use their example.

[teamatomic]

Lots of difference.

id...you dont auto increment so you need to control the issue of the numbers

username... 20 is a bit short but probably ok.

password...not long enough. a longer password will get trunicated and you will be left wondering why proper/correct passwords cant be matched. Think the length of an encrypted password

you have no primary key

and your sql statement will error due to the comma at the last entry.

 

Thats just a starter. You would be best to stick to the suggested example until you RTM enough to understand how to use mysql. But I would increase the int(4) to int(6) cause with it at 4 you top out at 9999 users. That includes current and removed. so if you have a well used site some time in the future you will have to change it anyways.

 

 

HTH

Teamatomic

[Dark57]

Yeah right now I'm just playing with scripts to increase my understanding, which is why I asked.

 

So what does NOT NULL Default ' ', do?  Is this to prevent people adding to the database without a username or password?  Also this tutorial doesn't really explain what everything does, whats the purpose of the Primary Key?

[DavidAM]

You are close.

 

NOT NULL means the column must have a value

 

DEFAULT '' means if a value is not provided, '' (an empty string) will be assigned to the column

 

Soap Box: I do not understand why mySql insists on having a DEFAULT value for a NOT NULL column.  This really defeats the purpose of specifying NOT NULL. I like to have NOT NULL columns, with no DEFAULT so if an attempt is made to insert data without a value, the insert FAILS.

 

As for the Primary Key, it is the main index for the table.  This allows quick access to any row if you know the value of its primary key.  If you don't put some kind of index on a table, every query will have to read EVERY row in the table to find anything.  And as the table gets bigger, the time to do this will take longer; along with the CPU use and memory use.  PRIMARY KEYS have to be unique but they can include more than once column if necessary.

 

[fenway]

Soap Box: I do not understand why mySql insists on having a DEFAULT value for a NOT NULL column.  This really defeats the purpose of specifying NOT NULL. I like to have NOT NULL columns, with no DEFAULT so if an attempt is made to insert data without a value, the insert FAILS.

That's because you're likely running in strict mode -- if you say something has to be a string, and you don't insert anything, and the default isn't a string, then that's considered data corruption.

[DavidAM]

Only if the INSERT is allowed to succeed.  What I am saying is that I should be able to specify a column as NOT NULL with no default.  If you perform an INSERT and do not specify a value, the server should try to leave the column NULL and the INSERT should FAIL because the column does not allow NULLs.  To me, that provides protection.  For instance, say I have a column for LastName and it is a required data element.  I want the column set to NOT NULL because a value is required.  However, an empty string is NOT a valid last name.  There is NO value you can use as a DEFAULT that makes sense.  If you are going to require a DEFAULT and use an empty string, then the NOT NULL is almost redundant.

[Dark57]

Ok, that makes sense actually.  Thanks for clarifying this up, I was confused as to why those lines of code where in the tutorial I was reading.

 

So in theory this code should work in creating a basic login table with appropriate sized fields and whatnot?

 

$con = mysql_connect("127.0.0.1","xxxx","xxxxxxxxx");
// Create table
mysql_select_db("test", $con);
$sql ="CREATE TABLE members
(
id int(6) NOT NULL auto_increment,
username varchar(25) NOT NULL default '',
password varchar(65) NOT NULL default '',
PRIMARY KEY (id)
)";

TYPE=MyISAM AUTO_INCREMENT=2;

// Execute query
mysql_query($sql,$con);

sql="INSERT INTO 'members' VALUES (1, 'Admin', '1234');"
// Execute query
mysql_query($sql,$con);

 

And one last question, I'm not sure what TYPE=MyISAM AUTO_INCREMENT=2; is doing.  It looks like its setting the auto_increment to 2 but I'm not sure why.

[jeffshen]

Type=MyISAM sets the storage engine to MyISAM

AUTO_INCREMENT=2 sets the autoincrement to start at number 2. If no number is specified, it will start at 1.

 

The script looks alright to me and it should work.

[DavidAM]

It looks ok, except the TYPE=myISAM belongs with the CREATE TABLE statement:

$sql ="CREATE TABLE members
(
id int(6) NOT NULL auto_increment,
username varchar(25) NOT NULL default '',
password varchar(65) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM AUTO_INCREMENT=2;";

[Dark57]

Oh thanks for catching that,  I was curious as to what MyISAM was and when I looked it up it said it was the default MySQL storage engine. If its the default, then why would I have to declare the type?  I also read that innodb can be a wiser choice at times.  I was reading up on the two and I'm curious as to when you would prefer one over the other.

[Mchl]

I would increase the int(4) to int(6) cause with it at 4 you top out at 9999 users.

 

There is no difference in capacity between INT(4) and INT(6). Both are shorthand for INTEGER which can hold 2^32 values. The number in parentheses is only used for column width when displaying data from database.

[Dark57]

Well I will be displaying User Id's so it does make a difference in this case.

[Mchl]

It doesn't, because you will be doing it through PHP, and not in MySQL console.

 

[Dark57]

Oh, well in that case I stand corrected.