TalkPHP - Is this a good query?

TalkPHP (http://www.talkphp.com/forums.php)

- MySQL & Databases (http://www.talkphp.com/mysql-databases/)

- - Is this a good query? (http://www.talkphp.com/mysql-databases/2318-good-query.html)

Aaron

02-23-2008 12:03 AM

Is this a good query?

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 (Post 11285)

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. :-D

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 (Post 11286)

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

All times are GMT. The time now is 09:01 AM.