awjudd

Which column? The amount columns? ~juddster

It may be a bit of a pain if you have lots of data in your database already to do the migration. But if you don't then it shouldn't be too bad. ~juddster

Until you get down to the list 1 then what would you do? Keep it at 0? Kinda pointless. mysql has the LIMIT keyword built-in DELETE FROM Buildings WHERE UserID = 1 AND ColonyID = 1 AND BuildingTypeID = 1 LIMIT 5 That would delete the top 5, there are several other ways you could do the delete as well. ~juddster

The first issue that comes to mind if you stored the number of a specific building type / blueprint in a column is when there is 1 of the type remaining. You need to code in extra business logic (either into your PHP code, or as a trigger) in order to actually remove the record (i.e. either do a DELETE rather than an UPDATE, or an UPDATE followed by a DELETE). Whereas with it broken out, the logic will always be the same. Relational databases are meant to have 1 : many relationships in it. By collapsing the data so that it does not, not only does it feel wrong on so many levels but you are also masking some of these features. I don't think I explained this well at all ... but yeah ... ~juddster

Yes, you will have 1 row for every building that the user makes. It may take a small amount more resources but at the end of the day, your database will actually be maintainable. The extra amount of resources is minimal. ~juddster

Indexes are your best friend. The database doesn't scan through each and every row. It attempts to strategically jump between rows based on the key values. ~juddster

None of the count / total data need to persisted (i.e. stored in the database as they can all be calculated by a simple query). The sample structure I provided this morning will satisfy exactly that you are asking for if you let it. Aside from not having the 'amount' column which you keep insisting it must have, what can you not do? ~juddster

Yes, there are FKs defined there (on all of the like-named columns). I wasn't sure if blueprints were one time use or not, but since they do not appear to be from your previous statement, I would make put a primary key on the UsersBluePrints table for the composite key ( UserID, BluePrintID ). It is this table that maps who has access to which blueprints. Everything you query should be based off of the IDs when possible. Not only are you going to gain speed, but the auto_increment IDs shouldn't change, but your text fields (i.e. username and stuff may). As well as if you use a username instead you are wasting space due to data duplication. If you must go based off of the username, then just simply add a join in onto the Users table in order to restrict it. The proposed structure records how many iron mines are in each colony through the relationships. For example, in order to find out how many iron mines are in each colony you would do something like this: SELECT u.Username, c.ColonyName, COUNT(b.BuildingID) AS BuildingCount FROM Users u JOIN Colonies c ON u.UserId = c.UserId JOIN Buildings b ON c.ColonyID = b.ColonyID JOIN BluePrints bp ON b.BluePrintID = bp.BluePrintID WHERE u.Username = 'foo' AND bp.BluePrintName = 'Iron Mine' GROUP BY u.Username, c.ColonyName ~juddster

You never actually store the number of buildings in a colony, you will calculate it based on the relationship between a colonies and buildings (i.e. by using COUNT). /* How many buildings are in a colony? */ SELECT u.Username, c.ColonyName, COUNT(b.BuildingID) AS BuildingCount FROM Users u JOIN Colonies c ON u.UserId = c.UserId JOIN Buildings b ON c.ColonyID = b.ColonyID GROUP BY u.Username, c.ColonyName /* How many colonies does a user have? */ SELECT u.Username, COUNT(c.ColonyID) AS ColonyCount FROM Users u JOIN Colonies c ON u.UserId = c.UserId GROUP BY u.Username Spreadsheets are bad because they are unmaintainable and lead to bigger problems when trying to query the database. There are many reasons why that you can find on google. With a relational database, you gain so much more than you would get out of the spreadsheet type. For example, you can now compare two different or all of the different user's colonies in order to get rankings and things. Sample: http://my.safaribooksonline.com/book/databases/database-design/0201694719/bad-design-hat-not-to-do/230 ~juddster

Agreed! ~juddster

It sounds like you are wanting to make a pivot table. There are a few topics online about it if this is the case (http://en.wikibooks.org/wiki/MySQL/Pivot_table). ~juddster

You don't want your data in a spreadsheet format ... that is just bad and will lead to bad other bad things. Your database should be relational. I have made a simple structure from that at first glance should accommodate what you are looking to do: Does this help you at all? ~juddster

$query_Recordset2 = "SELECT prices.price FROM prices where prices.width <= '".$_POST['width']."' and prices.drop <= '".$_POST['drop']."' ORDER BY prices.price DESC LIMIT 1"; Or whatever field you want to sort by? ~juddster

Order your returned results by the last value (i.e. ORDER BY prices.price DESC) and then add a LIMIT 1 to grab the last row. ~juddster

Is this what you want? WHERE MONTH(reviewdate) = MONTH(CURDATE()) ~juddster

Your foreach loop is incorrect. foreach ( $_SESSION [ 'cart' ] as $id ) { echo $id; } ~juddster

If you are storing it in the database or somewhere you could always just use PHP's strtoupper (http://php.net/strtoupper) or if you want to maintain the user's input (the correct thing to do) you could just use CSS to make it uppercase (text-transform:uppercase) when displaying it. ~juddster

After cleansing your data (i.e. don't trust what is actually coming in from the URL, or anywhere for that matter), you would add it to your cart by doing something like this: // Check if we selected an action if ( isset ( $_GET [ 'action' ] ) && $_GET [ 'action' ] == 'add' ) { // Since this is likely an auto-incremented ID, it should be an INT $id = intval($_GET['id']); // Validate that the item comes from the db (i.e. select from the table you pulled it from) // Validation successful, add the id to the cart $_SESSION['cart'][] = $id; } ~juddster

Those values are coming in through the query string (i.e. get parameters). They are accessible through $_GET. ~juddster

There is no code there that adds to the cart ... ~juddster

Maybe we weren't able to provide you with an alternate solution because you never really explained what was actually being stored. Both a dynamic number of columns as well as a table for each user are very horrible solutions because Pikachu said, you are trying to make your database like a spreadsheet which it is not. ~juddster

Then what was the issue? ~juddster

Please mark this thread as resolved. Thanks, ~juddster

Why give each player their own table instead of just having a UserID field within the table that will be used to decide what the user's colonys are and which aren't? ~juddster

Was it what we said (i.e. you had 'local host' somewhere?)? ~juddster