SQL help? It's only pulling unique rows when I want everything

[Ciffy]

Sorry for posting so much but you guys are helpful and I don't know what I should be searching for as admittedly my SQL skills aren't great.

I'm trying to get my pieces out of my database table in order to display them; I'm using blocks and php. I have my piece data including what kind of piece it is, its position and its owner in the piece table but I'm storing the color each player has for their pieces in the player table (and I need the color information in order to display the right image).

I've only got pieces in the table for a single player atm. A player can have many pieces of a given type and they're all valid (ie you can have 8 pawns in chess). I've just got pieces hard coded in the setup for now. When I run my query, no matter what I try, it's only pulling one piece per TYPE. (ie it's giving me a single pawn instead of 8, single rook instead of 2, etc.)

Is there something obvious I'm missing?

SELECT pieces.piece_type kind, pieces.player_id id, pieces.piece_x x, pieces.piece_y y, player.player_color color, player.player_id
FROM pieces
LEFT JOIN player ON pieces.player_id = player.player_id

I could store the color data in the piece table but it seems silly to have it in multiple places like that when having the owner is more important. I don't even know if I'm investigating the right thing or not since it is pulling the color data for what its pulling; it's just not pulling all of the pieces.

thx.

[Ciffy]

ok, it's not the join. If i completely take out the references to the player table and just pull stuff out of pieces, it's still only giving me one piece of each type. I'm NOT using DISTINCT.

[Tisaac]

Are you running query on phpmyadmin or via a framework function ?

[Ciffy]

Framework.

[BrianLovesMarvel]

If you posted your db sql for creating the table, creating the pieces, and retrieving the pieces that would be helpful. Otherwise we don't have much to go on.

Are you using getObjectFromDB or getCollectionFromDb?

The only thing about the SQL that kind of jumped out at me was

Code: Select all

...pieces.player_id id,...

where normally "id" is the name of another valid field. You might try dropping all the renames ("id", "x", "y", etc) and see if that helps. If you really want the shorter names, you can always change dbmodel.sql.

If you go into phpmyadmin and try the queries manually, you'll be able to see how they work. Adjust the sql until it gives you what you want, then go back to your program.

Best wishes, Brian

[RicardoRix]

id must be the first field in the select, it makes the associated array with that as the key, or rather whatever the first field is.

Yours currently is 'kind' and if you only have 1 kind....

Also as a tip, ditch all the renaming of fields unless you have to. Like previously suggested just change the name in the model.

[Ciffy]

id must be the first field in the select, it makes the associated array with that as the key, or rather whatever the first field is.

Yours currently is 'kind' and if you only have 1 kind....

Also as a tip, ditch all the renaming of fields unless you have to. Like previously suggested just change the name in the model.

Thanks! This is why I post here. Exactly what I needed (and had no idea it was even a thing). Added the piece id as the first column to pull and my sql works as I want it to.

[Tisaac]

id must be the first field in the select, it makes the associated array with that as the key, or rather whatever the first field is.

Yours currently is 'kind' and if you only have 1 kind....

Also as a tip, ditch all the renaming of fields unless you have to. Like previously suggested just change the name in the model.

Thanks! This is why I post here. Exactly what I needed (and had no idea it was even a thing). Added the piece id as the first column to pull and my sql works as I want it to.

Well that's why the doc is here. There is big warning right under the doc for this method so probably my best advice for avoiding future errors is RTFM and also test stuff in phpmyadmin.directly when in doubt

[RicardoRix]

You have the player_id twice in the select statement.

Personal preference, I would do this:

"SELECT pieces.player_id, pieces.* FROM..."

but do you need the join ? you normally always have the player array (especially JS side), you can just use that to get the player colour.

[Ciffy]

How do I get to the player data from within PHP? I'm trying the following. I'm in an arg Function is that matters. First state of the game after setup.

$player_id = self::getActivePlayerId();

$gamedata = self::getAllDatas();

$players = $gamedata['players'];

Code is crashing on either of the first two lines. I've tried getCurrentPlayerId as well and it doesn't seem to help. Odd thing is, I had it all working and then went to test it again and it gave me the "you got a php error" nonsense (according to the frequent errors doc).

And to answer Tisaac, where is this flashing light? I'm trying to RTFM but there's a LOT and I'm not even sure what I'm looking for half the time.