brokenhope Posted March 21, 2006 Share Posted March 21, 2006 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, userfavoritesNow, 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 usernameuserpass will contain a md5'ed version of the persons passworduserjoindate will contain a timestamp of the date they joineduserpageviews will contain a numerical value of the number of pages they have vieweduserlastlogin will contain a timestamp of the date they last logged inuserlevel 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 avataruserfavorites will contain an array, or a string (to be "explode()"'ed into an array) of the users favorite pages on the siteNow 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. Quote Link to comment Share on other sites More sharing options...
XenoPhage Posted March 21, 2006 Share Posted March 21, 2006 [!--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 usernameuserpass will contain a md5'ed version of the persons passworduserjoindate will contain a timestamp of the date they joineduserpageviews will contain a numerical value of the number of pages they have vieweduserlastlogin will contain a timestamp of the date they last logged inuserlevel 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 avataruserfavorites 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.. Quote Link to comment Share on other sites More sharing options...
brokenhope Posted March 21, 2006 Author Share Posted March 21, 2006 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 on0 | 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.