Getting Started Again.

[brokenhope]

I use to really be into PHP and such around a year ago, and I took a big break, and im trying to get back into it again, I used to be pretty much an expert at it, and I had done it for over 2 years... but now im really rusty.

The thing I need help with really, is helping me with a concept I never quite grasped... dealing with mysql, and how to set up the database properly.

Now im making a member script right now, and heres a rough outline of the 'members' table.

userid, username, userpass, userjoindate, userpageviews, userlastlogin, userlevel, useravatar, userfavorites

Now, the userid is easy, I will set it to int, auto_increment. The thing's that I dont know how I should set is pretty much everything else, usually I would set them all to Text...

username will contain the persons username
userpass will contain a md5'ed version of the persons password
userjoindate will contain a timestamp of the date they joined
userpageviews will contain a numerical value of the number of pages they have viewed
userlastlogin will contain a timestamp of the date they last logged in
userlevel will contain a numberical value to the id of their userlevel (corresponding in the userlevel/ privilages table)
useravatar will contain a url, maybe an id to their avatar
userfavorites will contain an array, or a string (to be "explode()"'ed into an array) of the users favorite pages on the site

Now what would be the ideal settings for those? Most of those will be limited and wont get to big, but userfavorites could get decently big, because I dont think I will put in a limit to how many pages they can add to their favorites. One last thing, should I name those like that? Would that be professional-like? Or should I use a 1 letter prefix? or none?


The next thing would just be a very helpful thing, but isnt needed, I could do some research and figure it out, but I just need some examples for querys... basicaly the more complex ones... you dont even have to use real names, just used like Table1, Table2, Table3, Row1, Row2, Row3, *, etc.

Like:
$select = mysql_query("SELECT Row1, Row2 From Table1 Where Row1 = 'Bah'");
$fetch = mysql_fetch_array($select);
$num = mysql_num_rows($select);

Thats the first query ive written in about a year, it could be messed up... but using 3 variables is how I usually did it, actually most times I didnt have the $num, so it was just 2...



Thanks for reading... please help.

[XenoPhage]

[!--quoteo(post=357094:date=Mar 21 2006, 04:13 PM:name=brokenhope)--][div class=\'quotetop\']QUOTE(brokenhope @ Mar 21 2006, 04:13 PM) [snapback]357094[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Now, the userid is easy, I will set it to int, auto_increment. The thing's that I dont know how I should set is pretty much everything else, usually I would set them all to Text...

username will contain the persons username
userpass will contain a md5'ed version of the persons password
userjoindate will contain a timestamp of the date they joined
userpageviews will contain a numerical value of the number of pages they have viewed
userlastlogin will contain a timestamp of the date they last logged in
userlevel will contain a numberical value to the id of their userlevel (corresponding in the userlevel/ privilages table)
useravatar will contain a url, maybe an id to their avatar
userfavorites will contain an array, or a string (to be "explode()"'ed into an array) of the users favorite pages on the site
[/quote]

Well, don't just set then for text unless you have no interest in growth .. :)

I'd do the following :

[code]

CREATE TABLE mytable (id   INTEGER UNSIGNED  AUTO_INCREMENT,
                                    username  CHAR(15) NOT NULL,
                                    password   CHAR(32) NOT NULL,
                                    joindate     DATETIME NOT NULL,
                                    pageviews  INTEGER DEFAULT 0,
                                    lastlogin     DATETIME NOT NULL,
                                    level          TINYINT NOT NULL DEFAULT 0,
                                    avatar        INTEGER,
                                    PRIMARY KEY (id));

CREATE TABLE avatar (id   INTEGER  UNSIGNED  AUTO_INCREMENT,
                                  location  CHAR(255) NOT NULL,
                                  PRIMARY KEY (id));

CREATE TABLE favorites (id  INTEGER  UNSIGNED  AUTO_INCREMENT,
                                     url  CHAR(255) NOT NULL,
                                     userid INTEGER UNSIGNED NOT NULL,
                                     PRIMARY KEY (id));
[/code]

Now, what I've done above is how I'd structure it based on what you told me.. I definitely wouldn't be placing the favorites into an imploded array.. Let SQL do the work for you..


As for queries, that depends on what you need..

[brokenhope]

Alright, thanks, I have a question though, actually 2.

With the time, does mysql automaticaly fill it in or do I? And when I go to display it will I be able to format it to like March 21, 2006, or 03/21/06, the only way I know to do that is when you have a numerical timestamp, like theres 12+ numbers, I dont quite remember but PHP has a function for it, and a function to render the date/time from that.

Second, under attributes, whats the diffrence between having it blank, binary, unsigned, and unsigned zerofill?


Oh and a third thing, for my level table, pretty much any power is listed, like any admin type power, and any mod type power, and theres a column for each, and each row for each level has a 0 or 1 filled in, is that bad? There could be up to 20 columns, but theyre filled with a 1 or a 0 across the rows... heres a short example of what I mean (slimed down):

(pageaccesslevel will be an int of any 2 digit number)

id| name | pageaccesslevel | register | login | updinfo | etc... they will go on
0 | guest | 1 | 1 | 1 | 0 |
1 | loggedin | 2 | 0 | 0 | 1 |
2 | mod | 3 | 0 | 0 | 1 |
3 | admin | 10 | 0 | 0 | 1 |

They are designed that way purposely, so that if you want to disallow registrations, just change it to 0 in the guest userlevel (default for everyone), if you dont want to allow anymore logins or have a banned group just change them to 0, pageaccesslevel is for pages that require you to have a certain level of access to view them.