m3rajk — Fri, 11 Jul 2003 00:27:54 +0000

well. i found the dates for regular zodiac. now i need to know the order for chinese zodiac. i know 79 was ram and 74 was tiger, and it's a 12 yr cycle, but that's all.once i know them all, the order and have a year to use as a calculating opint (hmmm... 1979?) then i can create a function for it like i did regular zodiac...

1900 works better since it's the earliest choice. infact, all i need then is to subtract 1900 from the year entered and then mod by 12.

only question is, how can i set the year to "please select" or something like that when it's not chosen while using the forloop i have? this function gets the chinese zodiac perfectly:

m3rajk — Sat, 05 Jul 2003 17:15:20 +0000

when i was writing it up a friend of mine linked me to a johnson & johnson page asking me which color contacts she should get. i figured most of them can be abbreviated.. and there was a wider selection than i'd have ever considered. but yeah, i see the point with eye color.

Mark Hensler — Sat, 05 Jul 2003 02:47:58 +0000

In the case of integer fields, the number in parenthesis does not effect the capacity of the field. That number simply indicates the maximum display size, which is really ignored unless you also define the field as ZEROFILL.

A tinyint is 1 byte. Unsigned, thats 256 max.
A smallint is 2 bytes. Unsigned, 65535.
(mediumint = 3 bytes, int = 4 bytes, and bigint = 8 bytes)

I changed `user_stats.city` from varchar(100) to varchar(50) because really, how many city names do not fit in 50 chars?

I changed `user_stats.religion` from varchar(100) to smallint(50) because I figured a select box would suffice for a list of religions. A 2 byte integer field will allow you to store the index of up to 65,535 different religions.

I noticed on your form, that you have a select box for hair color but not eye color. Why not for eye color? You could store that in an integer field as well. If you would settle for a 265 color selection, you could store the index in a tinyint field. That's 1 byte opposed to a possible 10 bytes with varchar(10).

I didn't change state/province to a integer field because I doubt everyone will live within the US. And allowing them to enter the text manually is easier than acquiring a list of all states/provinces world-wide.

I changed the tinytext fields in `user_interests` to text fields because tinytext is only 256 bytes, whereas text is 65,535 bytes.

m3rajk — Sat, 05 Jul 2003 02:15:15 +0000

there's a couple you have varchar(50) when i know at least one i use html to limit it to 100 characters.

i was wondering why the varchar is set to 50 there. and for weight i noticed you have tinyint(3) unsigned. even though i doubt anyone weighing over 1000 lbs would want to be on a site like this, since it is possible to have a 4 digit weight, should it not be tinyint(4) unsigned?

Mark Hensler — Fri, 04 Jul 2003 17:27:54 +0000

Quote: Originally posted by m3rajk
i'm looking at the others you've switched to ints to use index numbers and am wondering...

Which fields did you have questions on?

m3rajk — Fri, 04 Jul 2003 16:50:07 +0000

Mark- thank you. you're the first person at any of the three forums i've found that has people that do thisstuff to respond. also this happens to be the last one i found.

Quote: I've missed some other fields here and there. I either missed them, or didn't understand their use. I also didn't look over the forum tables. I'd suggest you acquire some forum software, and try to tie in your project

i've updated mocst of them based on what you said because i agree, i'm looking at the others you've switched to ints to use index numbers and am wondering on some if it might help you to see the sign up preview on the webspace i'll be losing at the end of july where which i've been using to create possible design and such based on what those i'm working with have been telling me.

we're going to use one of the versions of the preview 6 set of profiles. we haven't decided which one because everyone has a different idea for where the rating bar should go and we haven't had a chance to sit down and talk it over, but here's the profile as of now:
http://people.brandeis.edu/~m3rajk/JMT/preview/preview-profile6a.html
and here's the sign up with some debugging stuff as we're trying to get it to validate. it loses step when you get to step 4. for some reason it's not being passed. that has been happeneing since i added the max upload size (we haven't test uploads because we're waiting to get the validation to work. we're not sure we'd be able to on there).
http://people.brandeis.edu/~m3rajk/JMT/preview/phpprev/signup.php

the reason we're not downloading phpbb invasion or one of the other boards is because this is an attempt to show off what i was able to do with php/mysql. so i want the entire thing to be my creation.

getting the forums up is secondary to getting the profiles working. we also haven't talked much about the messaging system. we all agree we want one, but one likes the version with mail and pop up alerts, the other person i'm working with like seperate mail and "instant messages" that pop up. they both have advantages and disadvantages in practice. i'm leaning toward the mail and pop ups since i think it would be easier. but the priority we decided on was profiles, forums, messaging system. so i've put the messaging system off and started looking at forums since i've seen they are definitely the lifeblood of a site

Mark Hensler — Thu, 03 Jul 2003 19:17:09 +0000

CREATE TABLE users (
    `uid` int(10) unsigned NOT NULL auto_increment,
    `username` varchar(15) NOT NULL,
    `password` varchar(16) NOT NULL,
    `email` varchar(50) NOT NULL,
    `access_mask` smallint(5) unsigned NOT NULL default '0',
    `approved` tinyint(3) unsigned NOT NULL default '0',
    `last_login` datetime NOT NULL default '0000-00-00 00:00:00'
    PRIMARY KEY  (`uid`),
    UNIQUE KEY `username` (`username`)
) TYPE=MyISAM

CREATE TABLE sessions (
    `session_id` varchar(40) NOT NULL,
    `session_ip` varchar(15) NOT NULL,
    `uid` int(10) unsigned NOT NULL default '0',
    `login` datetime NOT NULL default '0000-00-00 00:00:00',
    `last_action` datetime NOT NULL default '0000-00-00 00:00:00',
    `last_location` varchar(50) NOT NULL,
    KEY `uid` (`uid`),
    KEY `session` (`session_ip`,`session_id`)
) TYPE=HEAP;

CREATE TABLE user_stats (
    `uid` int(10) unsigned NOT NULL,
    `dob` date NOT NULL default '0000-00-00',
    `gender` char(1) NOT NULL,
    `sex_pref` char(3) NOT NULL,
    `marital` char(1) NOT NULL,
    `smoker` char(1) NOT NULL,
    `drinker` char(1) NOT NULL,
    `origin` varchar(50) NOT NULL,
    `height` tinyint(3) unsigned NOT NULL default '0',
    `waist` tinyint(3) unsigned NOT NULL default '0',
    `eyes` varchar(10) NOT NULL,
    `hair` varchar(10) NOT NULL,
    `weight` smallint(5) unsigned NOT NULL default '0',
    `body` tinyint(3) unsigned NOT NULL default '0',
    `edu` tinyint(3) unsigned NOT NULL default '0',
    `employment` tinyint(3) unsigned NOT NULL default '0',
    `religion` smallint(5) unsigned NOT NULL default '0',
    `ethnic` smallint(5) unsigned NOT NULL default '0',
    `city` varchar(50) NOT NULL,
    `state` varchar(30) NOT NULL,
    `self_cat` smallint(5) unsigned NOT NULL default '0',
    `aim` varchar(16) NOT NULL,
    `icq` int(5) unsigned NOT NULL default '0',
    `mirc` charchar(20) NOT NULL,
    `msn` varchar(50) NOT NULL,
    `yim` varchar(20) NOT NULL
    PRIMARY KEY (`uid`),
    KEY `dob` (`dob`),
    KEY `gender` (`gender`),
    KEY `sex_pref` (`sex_pref`),
    KEY `marital` (`marital`),
) TYPE=MyISAM

CREATE TABLE user_bio (
    `uid` int(10) unsigned NOT NULL,
    `bio1` text NOT NULL,
    `bio2` text NOT NULL,
    `bio3` text NOT NULL,
    `bio4` text NOT NULL,
    PRIMARY KEY (`uid`),
) TYPE=MyISAM

CREATE TABLE user_interests (
    `uid` int(10) unsigned NOT NULL,
    `movies` text NOT NULL,
    `books` text NOT NULL,
    `music` text NOT NULL,
    `pets` text NOT NULL,
    `sports` text NOT NULL,
    `other` text NOT NULL,
    PRIMARY KEY (`uid`),
) TYPE=MyISAM

CREATE TABLE user_friends (
    `uid` int(10) unsigned NOT NULL,
    `fid` int(10) unsigned NOT NULL,
    `comment` tinytext NOT NULL,
    PRIMARY KEY (`uid`,`fid`),
) TYPE=MyISAM

TINYTEXT is only 256 characters. That's not big enough for several of the places where you indicated use. A TEXT field is 64KB, and a MEDIUMTEXT is 16MB.

I've substituted numeric fields where you had character fields in several areas. An example is `users.approved`. You indicated 3 switches. Instead of using a character field, wich may take up to 3 bytes to store your data, consider using a single byte tinyint field. 1 byte has 8 bits. Thats 8 switches. So, byte zero = 'entire profile', byte one = 'bio', and byte two = 'pics'.

I only have one `dob` field. This is because you can store everything in one field. If you want to query only the month, you can use MySQL functions in your query to return only the month.

`user_stats.gender` can be indicated by a single characer: m,f

`user_stats.height` can be stored in one field by converting feet to inches before storing in the database. This will also make it easier to select a range later.

`user_stats.body` only needs to hold the select menu's index number, not the text.

I completely removed the 'chinese zodiac' and 'regular zodiac' because knowing the user's DOB, you can come up with it.

ICQ numbers don't fit in smallint fields. They actually take a 4 byte integer.

I've missed some other fields here and there. I either missed them, or didn't understand their use. I also didn't look over the forum tables. I'd suggest you acquire some forum software, and try to tie in your project.

MySQL Docs:
6.2 Column Types