Phases

Ok cool, here is what I'm doing now. Made a function with the suggested array solution at top of page: function makeDropdown ($value, $value2) { $result = mysql_query("SHOW COLUMNS FROM `user_profile` LIKE '$value'") or die('ooops'); $row = mysql_fetch_array($result); $type = $row['Type']; preg_match('/enum\((.*)\)$/', $type, $matches); $vals = explode(',', $matches[1]); echo '<select name="'.$value.'">'; foreach ($vals as $val) { $val = trim($val, "'"); echo '<option value="'.$val.'"'; if ($value2 == $val) echo 'selected'; echo '>'.$val.'</option>'; } echo '</select>'; } Then where ever I need a dropdown I call the function and pass whatever I need: makeDropdown ('sign', $sign); It works great! Starting point: http://stackoverflow.com/questions/2350052/how-can-i-get-enum-possible-values-in-a-mysql-database ..and then: http://php.net/manual/en/functions.variable-functions.php The only big I don't quite understand yet is the preg_match(); but I'll just read up on it. Thanks for the big help guys. I feel much better now. Side note, if I mark this as solved that does it close the thread or leave it open for more discussion?

Ok so to expand.. I'd make an array using each enum value (the 12 signs plus the blank '') - I can do that, right? Then just do like a.. (and this will be ugly but tryin to be fast) $somethings[] =some array I just made from enums foreach $something in $somethings.. echo '<option value=".$something; if ($sign == '$something') echo 'selected'; ?>>$something</option> ...(yes code is ugly and wrong but is my logic right?

Well that's not bad.. I feel stupid - I do this sorta logic all over the place, why didn't it hit me? You the man (or woman?)! I mean, at least this way I'm really only writing each dropdown list once, I just have to put a <?php echo if......echo 'selected'; ?> in each option. So I can go from this: <td><select name="sign"> <?php if ($sign == '') { ?> <option value="" selected></option><option value="Aries">Aries</option><option value="Aquarius">Aquarius</option><option value="Cancer">Cancer</option><option value="Capricorn">Capricorn</option><option value="Gemini">Gemini</option><option value="Leo">Leo</option><option value="Libra">Libra</option><option value="Pisces">Pisces</option><option value="Sagittarius">Sagittarius</option><option value="Scorpio">Scorpio</option><option value="Taurus">Taurus</option><option value="Virgo">Virgo</option></select></td> <?php } if ($sign == 'Aries') { ?> <option value=""></option><option value="Aries" selected>Aries</option><option value="Aquarius">Aquarius</option><option value="Cancer">Cancer</option><option value="Capricorn">Capricorn</option><option value="Gemini">Gemini</option><option value="Leo">Leo</option><option value="Libra">Libra</option><option value="Pisces">Pisces</option><option value="Sagittarius">Sagittarius</option><option value="Scorpio">Scorpio</option><option value="Taurus">Taurus</option><option value="Virgo">Virgo</option></select></td> <?php } if ($sign == 'Aquarius') { ?> <option value=""></option><option value="Aries">Aries</option><option value="Aquarius" selected>Aquarius</option><option value="Cancer">Cancer</option><option value="Capricorn">Capricorn</option><option value="Gemini">Gemini</option><option value="Leo">Leo</option><option value="Libra">Libra</option><option value="Pisces">Pisces</option><option value="Sagittarius">Sagittarius</option><option value="Scorpio">Scorpio</option><option value="Taurus">Taurus</option><option value="Virgo">Virgo</option></select></td> <?php } if ($sign == 'Cancer') { ?> <option value=""></option><option value="Aries">Aries</option><option value="Aquarius">Aquarius</option><option value="Cancer" selected>Cancer</option><option value="Capricorn">Capricorn</option><option value="Gemini">Gemini</option><option value="Leo">Leo</option><option value="Libra">Libra</option><option value="Pisces">Pisces</option><option value="Sagittarius">Sagittarius</option><option value="Scorpio">Scorpio</option><option value="Taurus">Taurus</option><option value="Virgo">Virgo</option></select></td> <?php } if ($sign == 'Capricorn') { ?> <option value=""></option><option value="Aries">Aries</option><option value="Aquarius">Aquarius</option><option value="Cancer">Cancer</option><option value="Capricorn" selected>Capricorn</option><option value="Gemini">Gemini</option><option value="Leo">Leo</option><option value="Libra">Libra</option><option value="Pisces">Pisces</option><option value="Sagittarius">Sagittarius</option><option value="Scorpio">Scorpio</option><option value="Taurus">Taurus</option><option value="Virgo">Virgo</option></select></td> <?php } if ($sign == 'Gemini') { ?> <option value=""></option><option value="Aries">Aries</option><option value="Aquarius">Aquarius</option><option value="Cancer">Cancer</option><option value="Capricorn">Capricorn</option><option value="Gemini" selected>Gemini</option><option value="Leo">Leo</option><option value="Libra">Libra</option><option value="Pisces">Pisces</option><option value="Sagittarius">Sagittarius</option><option value="Scorpio">Scorpio</option><option value="Taurus">Taurus</option><option value="Virgo">Virgo</option></select></td> <?php } if ($sign == 'Leo') { ?> <option value=""></option><option value="Aries">Aries</option><option value="Aquarius">Aquarius</option><option value="Cancer">Cancer</option><option value="Capricorn">Capricorn</option><option value="Gemini">Gemini</option><option value="Leo" selected>Leo</option><option value="Libra">Libra</option><option value="Pisces">Pisces</option><option value="Sagittarius">Sagittarius</option><option value="Scorpio">Scorpio</option><option value="Taurus">Taurus</option><option value="Virgo">Virgo</option></select></td> <?php } if ($sign == 'Libra') { ?> <option value=""></option><option value="Aries">Aries</option><option value="Aquarius">Aquarius</option><option value="Cancer">Cancer</option><option value="Capricorn">Capricorn</option><option value="Gemini">Gemini</option><option value="Leo">Leo</option><option value="Libra" selected>Libra</option><option value="Pisces">Pisces</option><option value="Sagittarius">Sagittarius</option><option value="Scorpio">Scorpio</option><option value="Taurus">Taurus</option><option value="Virgo">Virgo</option></select></td> <?php } if ($sign == 'Pisces') { ?> <option value=""></option><option value="Aries">Aries</option><option value="Aquarius">Aquarius</option><option value="Cancer">Cancer</option><option value="Capricorn">Capricorn</option><option value="Gemini">Gemini</option><option value="Leo">Leo</option><option value="Libra">Libra</option><option value="Pisces" selected>Pisces</option><option value="Sagittarius">Sagittarius</option><option value="Scorpio">Scorpio</option><option value="Taurus">Taurus</option><option value="Virgo">Virgo</option></select></td> <?php } if ($sign == 'Sagittarius') { ?> <option value=""></option><option value="Aries">Aries</option><option value="Aquarius">Aquarius</option><option value="Cancer">Cancer</option><option value="Capricorn">Capricorn</option><option value="Gemini">Gemini</option><option value="Leo">Leo</option><option value="Libra">Libra</option><option value="Pisces">Pisces</option><option value="Sagittarius" selected>Sagittarius</option><option value="Scorpio">Scorpio</option><option value="Taurus">Taurus</option><option value="Virgo">Virgo</option></select></td> <?php } if ($sign == 'Scorpio') { ?> <option value=""></option><option value="Aries">Aries</option><option value="Aquarius">Aquarius</option><option value="Cancer">Cancer</option><option value="Capricorn">Capricorn</option><option value="Gemini">Gemini</option><option value="Leo">Leo</option><option value="Libra">Libra</option><option value="Pisces">Pisces</option><option value="Sagittarius">Sagittarius</option><option value="Scorpio" selected>Scorpio</option><option value="Taurus">Taurus</option><option value="Virgo">Virgo</option></select></td> <?php } if ($sign == 'Taurus') { ?> <option value=""></option><option value="Aries">Aries</option><option value="Aquarius">Aquarius</option><option value="Cancer">Cancer</option><option value="Capricorn">Capricorn</option><option value="Gemini">Gemini</option><option value="Leo">Leo</option><option value="Libra">Libra</option><option value="Pisces">Pisces</option><option value="Sagittarius">Sagittarius</option><option value="Scorpio">Scorpio</option><option value="Taurus" selected>Taurus</option><option value="Virgo">Virgo</option></select></td> <?php } if ($sign == 'Virgo') { ?> <option value=""></option><option value="Aries">Aries</option><option value="Aquarius">Aquarius</option><option value="Cancer">Cancer</option><option value="Capricorn">Capricorn</option><option value="Gemini">Gemini</option><option value="Leo">Leo</option><option value="Libra">Libra</option><option value="Pisces">Pisces</option><option value="Sagittarius">Sagittarius</option><option value="Scorpio">Scorpio</option><option value="Taurus">Taurus</option><option value="Virgo" selected>Virgo</option></select></td> <?php } ?> to this: <option value="" <?php if ($sign == '') { echo 'selected'; } ?>></option><option value="Aries" <?php if ($sign == 'Aries') { echo 'selected'; } ?>>Aries</option><option value="Aquarius" <?php if ($sign == 'Aquarius') { echo 'selected'; } ?>>Aquarius</option><option value="Cancer" <?php if ($sign == 'Cancer') { echo 'selected'; } ?>>Cancer</option><option value="Capricorn" <?php if ($sign == 'Capricorn') { echo 'selected'; } ?>>Capricorn</option><option value="Gemini" <?php if ($sign == 'Gemini') { echo 'selected'; } ?>>Gemini</option><option value="Leo" <?php if ($sign == 'Leo') { echo 'selected'; } ?>>Leo</option><option value="Libra" <?php if ($sign == 'Libra') { echo 'selected'; } ?>>Libra</option><option value="Pisces" <?php if ($sign == 'Pisces') { echo 'selected'; } ?>>Pisces</option><option value="Sagittarius" <?php if ($sign == 'Sagittarius') { echo 'selected'; } ?>>Sagittarius</option><option value="Scorpio" <?php if ($sign == 'Scorpio') { echo 'selected'; } ?>>Scorpio</option><option value="Taurus" <?php if ($sign == 'Taurus') { echo 'selected'; } ?>>Taurus</option><option value="Virgo" <?php if ($sign == 'Virgo') { echo 'selected'; } ?>>Virgo</option></select></td> That's MUCH better. And I have even bigger dropdowns than that. So, this is really going to help me stay sane. I just feel bad seeings how simple this was and how I certainly should have done this from the start, I don't know why I brain farted on this one. Thanks for your time, big help!

There is bound to be a better way to do this and I'm ashamed to say it's not hitting me. I'm serving up previously submitted profile information to users and letting them edit it by submitting it back. Therefore of course, I what they already have submitted to be default for all fields. For dropdowns, I'm sadly doing this: <td> <font size="2"><strong>Gender:</strong></font></td> // get values from database up there ^^ and set $gender to whatever they've previously selected, if anything <td><select name="gender"> <?php if ($gender == '') { ?> <option value="" selected></option><option value="Male">Male</option><option value="Female">Female</option></select></td> <?php } if ($gender == 'Male') { ?> <option value=""></option><option value="Male" selected>Male</option><option value="Female">Female</option></select></td> <?php } if ($gender == 'Female') { ?> <option value=""></option><option value="Male">Male</option><option value="Female" selected>Female</option></select></td> <?php } ?> Well. This works great. Unfortunately it also comes with the added benefit of being a pain in my rear. Sure, no big deal with a male/female dropdown - but imagine something like astrological sign or days of the month. Can someone point me in the right direction?

Cool thanks. I had decided to do all in one DB with prefixes in the meantime, because I was itching to get started. Figured I could break out what I needed later. I've only actually set up about 12 user tables though, so at this point I could easily go either way. Login system is nearly done but that's it. Thanks again! My next concern is how to deal with .. keywords (kinda like tags). But that comes later and I'll maybe have it figured out by then - if not ,I'll ask.

Thanks for the reply! And I'm sorry if I repeat myself here but, I just want to be comfortable before I begin. This issue is one of the two problems bugging me, and I'd like to begin tonight. So: Yeah, i'm debating prefixes vs separate project db's and a separate users db. The reason I'm concerned is because I suspect a single project to potentially be huge, huge enough for me to be concerned about about scaling. To add the other four right on top of it? Scary. Also, I'm factoring in the possibility that one of them may, for example, be sold one day and need to be pulled out. I guess as long as its easy to export database sections based on prefixes it doesn't really matter. I could all in one db, users prefix and separate site prefixes, and if I need to break a site out I could export its prefix and the users prefix, then import that back into a new database. Similarly, if the DB gets too big (or simply run outa storage) and I want to break each sites database (prefixes) onto its own server, I could just export each prefix set, import on their respective new servers, then just use the one users db (the only prefix left on original db) as shared for all. Am I correct in assuming this is no problem? So, put all in one DB, uses prefixes for each site a a users prefix (like last reply showed) and adjust accordingly later. The other option, 6 DBs, one for each site and one users, but then each site has to connect to two databases for nearly ever query made. Terrible idea right? Or no biggie? Thanks again for the back and forth, I just like being able to talk to someone about it.

Further, there would be other aspects of the whole thing that would need to be shared in the one database. PM system, maybe something like reporting of users, paid services, notifications, etc. Part of the reason I need this mapped out well at the beginning is to ensure I can break the sites apart easily later if I want to. So, would it be best to have each site on its own DB but sharing a single seperate userDB, or have each site all have their own user tables and update all every time as needed (seems like too much), or have it all in one big database with prefixes to distinguish the sites. In regards to #3, 1. Is exporting a db based on prefixes easy? Say I have a db with tables: users_user users_userprofile users_PM_stuff users_other_shared_stuff site1_table1 site1_table2 site2_table1 site2_table2 site3_table1 site3_table2 ...if I wanted or needed to down the line, would it be easy to export (users_* AND site2_*) for importing into a new db for just that site? 2. Suppose the five sites on 1 db caused the db to hit it's limit one day. I assume it's easy to cluster/scale? Any insight appreciated..

Really hoping for some helpful insight from someone. I'm a little surprised I've got no help. Anyway, I guess I'm not so concerned with the OP questions as I am the following: Imagine five web sites. They all share the same USER information. Do I want to: - have five separate databases databases and every time user tables are changed (get a new user, username changes, etc) , i update all five of them at once. - have six separate databases, one for each site and then a single user DB that they all share, and try to pass user_id through sessions so I only have to reference the six DB on login/out and UPDATE - or, have all five sites in a single DB with table prefixes. ..and why? Or does it really matter? I'm trying to learn and have been googling for a while now, reading about thread locking and everything else - so really looking for some help understanding what's best.

I have not. I'm mid-researching MyISAM vs InnoDB - if I need to worry about foreign keys, backup/restore/move differences, etc. I'm still mapping out tables in a spreadsheet. Also, How to handle combining five of these projects (that are more like mini projects) that will effectively all be the combined into one of a much larger scale. if it'd be best to give them each their own database, or even their own mysql instance period. How common is it to be on a website that connects to several different databases all from the same webpage. This is all pretty new to me. I've done a few things with php/sql but never anything put into production, and long ago. Arguably, something of this size isn't something someone of my experience level (that of very little) should even attempt. As you've probably figured out. But hey, where do you get if you don't grab the bull by the horns? I'm a pretty fast learner and am willing to bring on a partner who basically has the same skill set as me, willingness to learn, and ambition. At the very least I'll learn some stuff I can apply later.

Yeah, we're sitting around 1400 registrations a day. I remember back when there were 50 members and there were no mods or guides, heh.

androidforums.com

Also: MySQL server version : 5.0.51a-3ubuntu5.4-log

I've found some documentation but I'm having a hard time wrapping my brain around some of it, so hopefully you guys don't mind if I just ask straight forward here, and hope for some help. 1. Is there a limit I should be concerned about as far as how many rows in a table, or how many rows in a DB period. My concern, with what I'm planning, is that I may have 100's of millions of rows or more at one point. Imagine the scenario: - 100,000 users. Each user has 500 items, and each item has 10 keywords associated with it that I want to track, some will be the same, some will be different. - Supposing it is best to keep each record in a single table. So, rows might look like: username1 | Item1 | keyword1 username1 | Item2 | keyword2 username1 | Item3 | keyword1 username1 | Item4 | keyword3 username2 | Item5 | keyword4 username2 | Item6 | keyword5 username2 | Item7 | keyword2 username2 | Item8 | keyword6 .. Suppose 100,000 usernames, all posting up to 300 items each, with up to 10 keywords each. That's a potential 300million rows - with (if this project was a success) a potential of 10x that. 2.Would it be better to keep all those records in one table, or try to break to multiple? Very often: Display a users items to them with all the keywords associated. Allow them to quickly and easily change keywords associated with their items and which items. Somewhat often: Let a user search ALL keywords and return the item(s) associated with them, while comparing to their own item's keywords. The problem I'm having trouble with is.. image them wanting to list their keywords and what items they are associated with. It would be much easier to query one table and return all results with that keyworld && username, rather than search 40 databases (if i were breaking keywords into tables by first letter or number/symbol for example. Imagine the nighttmare of changing keywords around various items. Then toss in searching for other Item's out there that share the same keywords, from other users. Seems considerably easier to do from one table. 3. Scalability. If I get full to burst, due to whatever limitation (server, mysql) and need to expland - is this a concern? Can I.. you know, share tables or databases so to speak, so I can basically have a cluster or use a couple as if they were one? Just a simple "yes people do that all the time, worry about that in a couple years", or "that's hard and requires proper planning up front". will be fine.. No need to really elaborate if you don't want. Hope all this makes sense, any insight appreciated. Thanks!

Hey guys. Been a long time since I've done much of anything (php/sql) and I'm about to dust off the 'ol server and get moving on a project. So, since I already have a question that I'm having a hard time finding a straight answer to, I figured I'd join up and say hi. Let's see. I'm 32, married with 2.5 kids (hard to explain). Work in IT (network/server/desktop support, project management) and also run a few websites - most notable being an Android forum with about 10k people on at any given (day) time. I've done a few small sql/php projects over the years but nothing huge. So, I'm not band spanking new but I'm darn sure not an expert. I suspect I'll be spending a lot of time here. Anyway, thanks