60 replies to this topic

[Caseyweederman]

Okay, what? I don't understand these. As far as I can tell I can:
grab specific stuff from one table and also
grab everything from the next table and cram it into the same result and also
AND means WHERE now.

I guess?
Is there a way to only select certain columns in the joined tables?

While I'm on the topic...

Let's say I've got a thing I want to add to the table. If the row exists, I only want to change one cell - the quantity cell. If the row doesn't exist I need to create it.
Is there a better way to do this than to double the number of necessary queries and quadruple the lines of code? (does this row exist? No? Make a new row. Yes? Don't make a new row but change one that exists already.)

[Caseyweederman]

Oh sweet. I figured it out. When declaring which columns you want, you need to prefix each one with the table it is in. The wildcard can still modify these.
$query = "SELECT table1.*, table2.id, table2.name, FROM table1, JOIN table2 ON table1.id = table2.id"

I didn't intuit this because I wouldn't have tried using a table name as a prefix for a table that hasn't even been joined yet, and I wouldn't have expected to need the first table's name as a prefix when that is the table that is selected prior to any joins.
Also AND doesn't mean WHERE.
Thanks for your help, me!
You're welcome.

[Drackir]

I started writing a big long explanation and then got distracted, moved to my desktop and now it's sitting on my laptop so I didn't get a chance to finish it. I'll give you a full explanation with examples and caveats even when I get a chance. Although, I am glad to see you figured out most of it.

[Drackir]

Nevermind. My computer decided to restart itself to install updates so I lost what I wrote.

Basically, yeah, you have to give the tables aliases and then reference the columns you want from each table using its alias. You did it without aliasing the tables by just prefixing their name. Using table aliases is easier since you can use shorter, customized names.

SELECT t1.Col1, t2.Col2
FROM tblData AS t1
INNER JOIN
tblDataLink AS t2
ON t1.ID = t2.ID

The ON clause determines how the two tables in the previous JOIN statement are linked. In this case, they are joined based on the ID column of each table. So, for every row in the second table, that has a row in the first table, a row will be returned. Note that with INNER joins, rows will only be returned if the ID exists in BOTH tables. With a LEFT JOIN or RIGHT JOIN, rows from the table on the left or right (respectively) will always be in the result set. If a matching row is missing in the second table, the values will be NULL for columns from that table.

[Caseyweederman]

Ohh, yeah, I think I was falling into that at one point. There was no match in the second table so it just ignored those rows.
Thanks for the help!
Now I just need to put down Skyrim and apply this knowledge.

[Drackir]

Haha. That's the trick.

[Caseyweederman]

Any chance there's a bit of MySQL code that determines, in the middle of a query, whether or not that entry exists, and if so, to only change one field of the current entry?

So far I've got this:
Hit the table to see what items the player already owns.
If item exists, send a query to add one to the quantity field.
If item does not exist, send a query to write in a whole new row.

On the one hand, the more items I add the sillier that first query looks, as I'd be grabbing the whole load of items. I can't narrow that to only items that are dropping at that moment, because it has to get several for multiple people who might all be getting different items, and the code needs to do this whole dance for each of them.

On the other, the whole not_exists thing will only ever be applicable once, because once that player has at least one of every item, there is absolutely no need to check the table first.

I could seed the item table on character creation, list all the items in the game and quantify them at zero, but there could potentially be thousands of items and hundreds of players and...

Okay, here's another model.

Run an individual query for each player and only return rows that will be affected by the query that will add the items.
This will only run once for each winning player in battle, and then there will be two more queries, one to create the new rows that need to be created, one to only add to the quantity fields of the rows that already exist.


So it seems to me I'm overcomplicating this. Halp!

[Drackir]

Look, don't optimize until you have something to optimize. Running a query to see if a user has an item and if not, inserting the record, literally takes milliseconds. Run your queries as simply and easily as possible and then, only if you're seeing a slow-down or degradation of performance, refactor your code and fix it.

I don't know what your table structure looks like but it's possible that that could be improved upon to make the queries simpler. I don't recommend adding every item to every player. You should have a players table an items table and a playeritems table. Where the playeritems table only stores a playerid, itemid and quantity. Everything else can be joined onto it.

If the game is web-based you could have an in-memory version of the player's data that gets loaded when they log in and is stored in the session. It could log the new items and flag them for adding to the DB the next time you save things or just save them immediately. This would, of course, be done with a class that you write. This also means you wouldn't have to hit the DB for checking what the player is carrying or, well, basically anything short of adding new items or updating counts/char info, etc.

[Caseyweederman]

I agree that it is silly to worry about these things now, but on the other hand it's a lot easier to just do it correctly now (and instill proper coding habits) than to try coming back to the code when it does matter and is totally unfamiliar.

You're right, the item table is just playerid, itemid, and quantity.

Storing values in session variables would be okay, but this code has to deal with a variable number of players. Combat is going to allow for pvp and cooperation from the ground up. The first time the code is run on combat resolution it determines winners and losers and rolls items for the winners. I can't think of a better way to do this asynchronously other than that the first person to load the code runs the script that affects everybody, and then the other players are notified.

Don't ask me how I'm going to do that yet... Maybe I'll just store all the combat text as strings in a table and load that to look like it was being run for the first time for each other player.
Time to learn som AJAX!

[Drackir]

Well, proper coding habits dictate that you don't focus on micro-optimizations while you work. Focus on getting it working in the first place and optimize later. Micro/pre-optimization will just make you lose focus and spend a lot of time on something that may not really be important. Trust me!

As far as your architecture goes, you don't want anyone but the server controlling the outcome of the game (rolls, stat calcs, etc.). I say this because it is trivially easy to change not only the JavaScript values but also the HTML of any web page with modern development tools.

Your best bet is to store game state in an object in the server. ASP has something called Application variables which is basically the same thing as a session, but it's stored across all users. I see that PHP doesn't have this, but I found a simple implementation here.

I also recommend (I may have already done this, but I can't remember) that you look into jQuery (or another similar library) and absolutely look up JSON. jQuery's $.ajax function is super helpful.

You may also want to look up "long-polling", Comet and (server) push/streaming.

[Caseyweederman]

Thanks for the advice, I'm now getting things done instead of being brainblocked.
Oh yeah, no maths are done client-side. The first person to poke the server makes the server do all the maths for everybody in the battle all at once.
You did mention jQuery, but I felt it was cheating. It's a bunch of code that I don't have to write, right? Could I even call anything I wrote that uses it my own code? Though I guess I didn't write any of the functions that make up PHP.
I'll look into those links.

So, uh, if the column that is being joined is null in the second table, does that overwrite that column in the left column in a left join?
I'm left joining two tables on char_id, and what I want to see is:
char_id=>1 item=>1
char_id=>1 item=>0
char_id=>2 item=>0

...but what I'm getting is:
char_id=>1 item=>1
char_id=>1 item=>0
char_id=>NULL item=>NULL

Does the column that is joined need to exist in both tables? I'd gathered that the point of left joins was no, they don't.
(The first table has char_id, but the second table does not necessarily have char_id, but knowing which ones is important.)

[Drackir]

--Disclaimer: None of this code is tested since I don't have MySQL easily accessible. I recommend you set up some tests and see how it works. --

If you're getting null on the char_id column and you're joining on that column, it means that in one of the two tables char_id is null. Consider these tables:

tblCharacters			tblItems			tblCharacterItems
| char_id | name |		| item_id |  name  |		| char_id | item_id | quantity |
------------------		--------------------		--------------------------------
|       1 | test |		|       1 | sword  |		|       1 |       2 |        3 |
|       2 | bob  |		|       2 | shield |		|       1 |       1 |        1 |
|       3 | red  |		|       3 | hat    |		|       2 |       1 |        1 |
								|       2 |       3 |        1 |

Now, if you want to find all the items that characters are carrying, excluding the item name (for now), you would do this:

1. SELECT c.char_id, c.name, ci.item_id, ci.quantity
2. FROM tblCharacters AS c
3. INNER JOIN tblCharacterItems ci
4. ON c.char_id = ci.char_id

Breaking this down, we have:
1. Select character id, name, item id and the quantity of items
2. From the characters table (with the alias "c")
3. Join the records from characters table with the records from the character items table (alias "ci"). The INNER join will cause any rows that do not match on either side of the join to be dropped from the result set (not from the table, don't worry).
4. Join them based on the char_id columns from each table being equal.

The outcome result set would look something like this (the rows may be in a different order):

| char_id | name | item_id | quantity |
|       1 | test |       2 |        3 |
|       1 | test |       1 |        1 |
|       2 | bob  |       1 |        1 |
|       2 | bob  |       3 |        1 |

Now, there's a few things to notice here:
1. Since we did an INNER join, and the character with id = 3 has no corresponding rows in the character items table, there are no results for character 3.
2. There are multiple records for each of the other characters corresponding to the matching id's in the character items table.

Now, let's take the same query and do a LEFT join:

1. SELECT c.char_id, c.name, ci.item_id, ci.quantity
2. FROM tblCharacters AS c
3. LEFT JOIN tblCharacterItems ci
4. ON c.char_id = ci.char_id

1, 2 and 4 are the same here. The only difference is 3:
1. Same.
2. Same.
3. Join the records from characters table with the records from the character items table. The LEFT join will include all rows from the LEFT table (tblCharacters) in the result set, even if they don't have a corresponding record in the RIGHT table (tblCharacterItems). If a corresponding record is not found, all fields from the left table will be filled, however all fields from the right table will be NULL.
4. Same.

The change to the record set is as follows:

| char_id | name | item_id | quantity |
|       1 | test |       2 |        3 |
|       1 | test |       1 |        1 |
|       2 | bob  |       1 |        1 |
|       2 | bob  |       3 |        1 |
|       3 | red  |    NULL |     NULL |

The thing to note here is the last row. Since we did a LEFT join, all records from the LEFT table appear in the result set regardless of whether or not they match a record from the table on the RIGHT.

Now, let's say you want to find the item names as well, that will require joining the characters table to the character items table in order to find the items that the characters are carrying. Then, you'll also have to join the items table onto the character items table to get the names like so:

1. SELECT c.char_id, c.name AS character_name, ci.item_id, ci.quantity, i.name AS item_name
2. FROM tblCharacters AS c
3. LEFT JOIN tblCharacterItems ci
4. ON c.char_id = ci.char_id
5. LEFT JOIN tblItems i
6. ON ci.item_id = i.item_id

Breaking it down again:
1. Select character id, character name, item id, quantity of items and the item name
2. From the characters table (with the alias "c")
3. Join the records from the character items table (alias "ci")
4. Join them based on the char_id columns from tblCharacters and tblCharacterItems being equal
5. Join the records from the items table (alias "i")
6. Join them based on the item_id columns from tblCharacterItems and tblItems being equal

So, we're doing two joins here to get the item names. The result set would be like this:

| char_id | character_name | item_id | quantity | item_name
|       1 | test           |       2 |        3 | shield
|       1 | test           |       1 |        1 | sword
|       2 | bob            |       1 |        1 | sword
|       2 | bob            |       3 |        1 | hat
|       3 | red            |    NULL |     NULL | NULL

I hope this helped iron some things out for you. Let me know if you have any questions.


Go Leafs!

[Caseyweederman]

Aha! That helped me. To use the first example, I was SELECTing c.char_id and ci.char_id which I suppose doesn't make any sense. I figured the columns you were joining together had to both be grabbed by the query.
Thanks for the help!
Haha, that made my code go from "what the heck, what is even wrong here, where could it possibly" to "oh hey, it just works perfectly now".

[Drackir]

You actually don't need to select ANY columns from the joined tables if you don't want to.

Glad I could help.

[Caseyweederman]

Okay, here's a question. How do I make an html input submit button display something other than the value it send?
The data received
Button:
<input type="submit" value="<?php echo ${$combat_array}['char_id']; ?>" name="combat_action">
Caught by script:
$combat_choice_set = "UPDATE combat SET choice = '" . $_POST['combat_action'] . "' WHERE char_id = '" . $_SESSION['char_id'] . "'";

The buttons right now show up with the numeric character ids that need to be caught by the script.

I don't know why these had to be type="submit". I could probably just do anything at all with an on.click event. It was quick and convenient.

I think I got it. I'm just using images which just works for some reason. Thanks!

[Drackir]

Oh geeze. Please please please tell me that's not actual code you're using for an UPDATE?

Also, you don't make a submit button do that. Try using <button type="submit" value="valuedddd">Text In Button</button>

[Caseyweederman]

Uhh, hypothetically speaking, let's say this friend of mine is using that actual code for an UPDATE.
What? Is it breakable? Should I make sure people aren't worming in some semicolon droptables or something?

[Drackir]

Consider this:

<input type="submit" value="'; DROP TABLE tblCharacters;--" name="combat_action">

So now, when I submit my button, all values in whatever table get set to blank and then the character table gets dropped. Yay!

[Caseyweederman]

So I need to validate everything. I can do that?

[Drackir]

You should always validate everything anyways. However, if you use parameters, you only have to check that the values are within a valid range. For text, you don't have to worry about quotes and stuff when you use parameters. Which is pretty much awesome!