Is this a good query?

Aaron · 02-23-2008, 12:03 AM

PHP Code:


			
CREATE TABLE `users` (

`id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,

`name` VARCHAR( 30 ) NOT NULL ,

`password` VARCHAR( 30 ) NOT NULL ,

PRIMARY KEY ( `id` )

);

is that a good query for a member table?

DeMo · 02-23-2008, 12:23 AM

Well.. that depends on what you're building. If for this system you need only the user's name and password then it's ok.

About the password, how do you plan to store them?
If you're gonna store flat passwords (kinda insecure) 30 characters are ok, but if you're going to store the md5 hash you're gonna need 32 chars. For a sha-1 hash you need 40 chars.

SOCK · 02-23-2008, 01:41 AM

I agree with DeMo, you need to consider the `password` field and how it relates to what hash you're storing there.

In addition, make sure you add a UNIQUE index on the `name` field.

SMALLINT is probably enough for your application, but using INT isn't going to hurt anything.

Aaron · 02-23-2008, 02:43 AM

Thanks for your help. I didn't think that more than 4 people would have an account on this, so I just made it a smallint.

SOCK · 02-23-2008, 04:59 AM

Good call on the ALTER statement to create a UNIQUE index. In cases like this where you want to avoid users having the same username, the easiest way to implement it (and have the benefits of using an index) is with UNIQUE.

Quote:

Originally Posted by Aaron

Thanks for your help. I didn't think that more than 4 people would have an account on this, so I just made it a smallint.

Well, that will certainly hold 4 accounts.

TINYINT (unsigned) is up to 255.
SMALLINT (unsigned) will run up to 65,535.
INT (unsigned) is up to 4,294,967,295 (yes, that's trillion).

In your case, especially for test tables or small user groups, TINYINT(2) UNSIGNED (to 99) will usually suffice. Just one byte per value.

MySQL Manual : Data Types | Numeric types

Salathe · 02-23-2008, 06:20 AM

Quote:

Originally Posted by SOCK

TINYINT(2) UNSIGNED (to 99)

TINYINT(2) UNSIGNED still has the normal range of values (0-255) of plain jane TINYINT UNSIGNED. The (2) is only a padding value if the column is declared with ZEROFILL.

To put clearer, if you try to insert a value of 100 into a TINYINT(2) column then it'll work fine. If you try to insert 256, then MySQL will throw back a warning "Out of range value adjusted for column 'id' at row 1" and adjust the value to 255.

SOCK · 02-23-2008, 06:32 AM

salathe> Thanks for the clarification. I guess I should read the links I post completely, eh?

Aaron · 02-23-2008, 07:52 PM

Why doesn't this work?

PHP Code:


			
<?php
@require_once("config.php");

//create a table
mysql_query(CREATE TABLE `testusers` 
    (
    `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
    `name` VARCHAR(40) NOT NULL,
    `password` VARCHAR(40) NOT NULL,
    PRIMARY KEY(`id`)
    );
) || die(mysql_error());
?>

Salathe · 02-23-2008, 08:09 PM

Your SQL isn't wrapped in string delimiters (single quotes).

Aaron · 02-23-2008, 08:33 PM

Okay...
How do I connect to the database when my username doesn't have a password?

Alan @ CIT · 02-23-2008, 09:31 PM

Just use "" in place of the password in mysql_connect()

Alan