[INSERT With Indeterminate Number of Columns]

Which column? The amount columns?

 

~juddster

[INSERT With Indeterminate Number of Columns]

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

[INSERT With Indeterminate Number of Columns]

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

[INSERT With Indeterminate Number of Columns]

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

[INSERT With Indeterminate Number of Columns]

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

[INSERT With Indeterminate Number of Columns]

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

[INSERT With Indeterminate Number of Columns]

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

[INSERT With Indeterminate Number of Columns]

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

[INSERT With Indeterminate Number of Columns]

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

[PHP SQL string query using WHERE AND commands - Going nuts!]

Agreed!

 

~juddster

[Joining Several FKs to Single Row]

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

[INSERT With Indeterminate Number of Columns]

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

[PHP SQL string query using WHERE AND commands - Going nuts!]

$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

[PHP SQL string query using WHERE AND commands - Going nuts!]

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

[DATE column is within current month?]

Is this what you want?

 

WHERE MONTH(reviewdate) = MONTH(CURDATE())

 

~juddster

[Trying to get something to add to a SESSION]

Your foreach loop is incorrect.

 

foreach ( $_SESSION [ 'cart' ] as $id )
{
    echo $id;
}

 

~juddster

[How do I arrange, that only capital letters are written into my HTML form?]

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

[Trying to get something to add to a SESSION]

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

[Trying to get something to add to a SESSION]

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

 

~juddster

[Trying to get something to add to a SESSION]

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

 

~juddster

[INSERT With Indeterminate Number of Columns]

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

[not understanding]

Then what was the issue?

 

~juddster

[Nede help with Select WHERE]

Please mark this thread as resolved.

 

Thanks,

~juddster

[INSERT With Indeterminate Number of Columns]

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

[not understanding]

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

 

~juddster