Need help with SELECT and UPDATE

[xXGriMe]

Im developing a trading card game in unity and need some help with the php and mysql side of things. 

 

My database structure is something like this. This is set up when the player registers. Each player has 60 rows inside of player_deck because the decks limit is 60. 

 

players

player_id/email/password/username

 

player_deck

player_id/card_id/card_amount

 

Now this is where I need help. Im trying to update these rows when the player saves the deck. From Unitys end im calling a URL that looks something like this.

 

"localhost/saveplayerdata/saveplayerdeck.php?playerid=&deckcardids=&deckcardamounts="

 

The PHP page stores the deckcardids and deckcardamounts into two arrays.

 

Now here is where im stuck. I need to get all the rows that belong to that player and update them with our data sent from Unity.

 

This is what i have so far. Im able to select the rows I need but how would I go about updating them with the data in my arrays? Thanks for reading.

<?php
    
	$playerId=$_GET['playerid'];   
	
	$playerDeckCardIds=$_GET['deckcardids'];   
	$playerDeckCardAmounts=$_GET['deckcardamounts'];  
	
	$deckCardIds = explode(" ", $playerDeckCardIds);
	array_pop($deckCardIds);
	$deckCardAmounts = explode(" ", $playerDeckCardAmounts);
	array_pop($deckCardAmounts);
	
	//This is the new array of cardIds sent from Unity
	//foreach ($deckCardIds as $value) 
	//{
		//echo "Deck Card Id $value <br>";
		
	//}
	//This is the new array of cardAmounts sent from Unity
        // foreach ($deckCardAmounts as $value) 
	//{
		//echo "Deck Card Amount $value <br>";
			
	//}
	
	try
	{
		#Connect to the db
		$conn = new PDO('mysql:host=localhost;dbname=tcg', 'root', '');
		$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    
		
		$stmt = $conn->prepare('SELECT card_id, card_amount FROM player_deck WHERE player_id = :player_id');
		$stmt->execute(array('player_id' => $playerId));
			
		$r = $stmt->fetchAll(PDO::FETCH_OBJ);
			
		#If we have a result
		if ( count($r) ) 
		{
			foreach($r as $row) 
			{
			    //echos the current database information. I need to update this data with the data inside $deckCardIds and $deckCardAmounts.
			    echo $row->card_id;
		            echo $row->card_amount;
			}
		}
		#We did not any rows that matched our id
		else
		{
			echo 'No match';
		}
		
		
	   
	}
	catch(PDOException $e) 
	{
		echo 'ERROR: ' . $e->getMessage();
	}
	
	   
	   
	   
	   
	  
?>

Edited February 21, 2016 by xXGriMe

[Muddy_Funster]

I'm still not 100% on what you're doing with this, but this bit:

 

I need to get all the rows that belong to that player and update them with our data sent from Unity

Sounds like you just want a JOIN in your UPDATE query. Something similar to this:

UPDATE player_table
LEFT JOIN
deck_table
ON (player_table.playerID = deck_table.playerID)
SET (deck_table.col1 = ?)...
WHERE player_table.playerID = ?

I'm not convinced the two split arrays is the best way to maintain data integrity between sites, it would be much better if you could get it through as either a single associative array, a JSON object or as XML.  This would help ensure that the relative deck ID has the correct card amount attached to it before it gets sent to the DB.

[xXGriMe]

I'm still not 100% on what you're doing with this, but this bit:

 

Sounds like you just want a JOIN in your UPDATE query. Something similar to this:

UPDATE player_table
LEFT JOIN
deck_table
ON (player_table.playerID = deck_table.playerID)
SET (deck_table.col1 = ?)...
WHERE player_table.playerID = ?

I'm not convinced the two split arrays is the best way to maintain data integrity between sites, it would be much better if you could get it through as either a single associative array, a JSON object or as XML.  This would help ensure that the relative deck ID has the correct card amount attached to it before it gets sent to the DB.

Im not sure thats what im looking for. Sorry im not explaining this very well. 

 

When a player registers from Unity the table player_deck gets populated with 60 rows. Each row represents a card that can exist in the players deck. These 60 rows are unique to that player based on the players id.

 

 

Now from Unity the player is able to able to remove or add cards from the deck. When the player saves the deck it calls my php page.

$playerDeckCardIds=$_GET['deckcardids']; //This contains a string that looks something like this. "1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 "

$deckCardIds = explode(" ", $playerDeckCardIds); //Split the string into a array
array_pop($deckCardIds); //Remove the last entry because it holds no id

Now I have the new card ids stored in a array $deckCardIds that need to be placed in the database. 

#Connect to the db
    $conn = new PDO('mysql:host=localhost;dbname=tcg', 'root', '');
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    //Select the rows i need to change    
    $stmt = $conn->prepare('SELECT card_id, card_amount FROM player_deck WHERE player_id = :player_id');
    $stmt->execute(array('player_id' => $playerId));
    
    //$r now holds a array of those rows        
    $r = $stmt->fetchAll(PDO::FETCH_OBJ);
    
    //$i is my index
    $i = 0;
        
    //Loop through the rows that need to be changed
    foreach($r as $row)
    {
         //This update statement needs to represent the current row im looping through
         $stmt = $conn->prepare('UPDATE player_deck SET card_id = :card_id WHERE player_id = :player_id'); //Wrong
         $stmt->bindParam(':player_id', $playerId);
         $stmt->bindParam(':card_id', $deckCardIds[$i]);
         $stmt->execute();
         $i++;
    }
I know my update statement is wrong here but im not sure how to only update the row im currently looping through.

Edited February 22, 2016 by xXGriMe

[xXGriMe]

Ok so I changed the way my code is working a little bit. Im selecting the rows I need based on the row_id that matches the player_id. Then im updating the card_ids in the database with what i have in $deckCardIds based on the current row_id we are on in our loop. 

 

Problem is for some reason every card_id in the database gets set to 0 when it finishes. I can see that its getting set but then it is changed back to 0 after the loop is done. I dont get whats going on here.

<?php
	$playerId=$_GET['playerid'];   
	$playerDeckCardIds=$_GET['deckcardids'];   
	
	$deckCardIds = explode(" ", $playerDeckCardIds);
	array_pop($deckCardIds);
	
	try
	{
		#Connect to the db
		$conn = new PDO('mysql:host=localhost;dbname=tcg', 'root', '');
		$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    
		
		$stmt = $conn->prepare('SELECT row_id FROM player_deck WHERE player_id = :player_id');
		$stmt->execute(array('player_id' => $playerId));
			
		$r = $stmt->fetchAll(PDO::FETCH_OBJ);

		$i=0;
		
		foreach($r as $row) 
		{
			$stmt = $conn->prepare('UPDATE player_deck SET card_id = :card_id WHERE row_id = :row_id');
			$stmt->bindParam(':card_id', $deckCardIds[$i]);
			$stmt->bindParam(':row_id', $row->row_id);
			$stmt->execute();
			$i++;
		}
	}
	catch(PDOException $e) 
	{
		echo 'ERROR: ' . $e->getMessage();
	}  
?>

[Psycho]

The reason why the values are getting set to 0 is simple - looking at your previous code (and comments) and the current code should tell you why

 

 

 

//$r now holds a array of those rows 

$r = $stmt->fetchAll(PDO::FETCH_OBJ);

 

$stmt = $conn->prepare('UPDATE player_deck SET card_id = :card_id WHERE row_id = :row_id');

            $stmt->bindParam(':card_id', $deckCardIds[$i]);

            $stmt->bindParam(':row_id', $row->row_id);

 

As your comment in the prior post shows you are retrieving the results as an array. But, you are trying to bind the value as if it is an object.

 

But, you are doing this the wrong way. There is no need for a select query. Just the update query. Also, you are using prepared queried all wrong. You prepare the query one time, then execute it multiple times while changing the bound data.

Edited February 23, 2016 by Psycho

[Psycho]

OK, I'm trying to give you some code, but I see something else that doesn't make sense. Your code appears to be trying to change the card_id of the records. I would think you would be changing the values. You example of the passed data is

$playerDeckCardIds=$_GET['deckcardids']; //This contains a string that looks something like this. "1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 "

Based on the image of your table those are values not IDs (yet the code is trying to update the IDs) - very confusing. I'm not even sure why this is getting passed as a string and not an array. But, it is impossible to associate that list of values (?) with the existing records - except that you know they are the records for the current user. I don't know what the form looks like or what you are doing. It would be advantageous to have a unique input field for each card so you can associate the correct value with each card id. E.g.

<input type='text' name='card[4]' value='-1' >
<input type='text' name='card[5]' value='2' >
<input type='text' name='card[6]' value='3' >

That will be passed as a multi-dimensional array with the ids 4, 5, & 6 with those associated values.

Edited February 23, 2016 by Psycho

[mac_gyver]

OK, I'm trying to give you some code, but I see something else that doesn't make sense. Your code appears to be trying to change the card_id of the records. I would think you would be changing the values. You example of the passed data is

 

 

was just going to post the same. none of the UPDATE queries being shown make sense.

 

you need to define what you are going to do for all the different possibilities and to get help from us, you need to provide that definition so that we understand all the different possibilities.

 

for the input data, what will it look like when adding card(s) that don't exist? what will it look like when modifying existing card amounts? what will it look like when you remove card(s)? is the card_amount always 1? is the card_amount a value of a card, that is fixed or variable for any card id or is it the number of cards with that id, assuming you can have more than one of any card id? are there always 60 ids and 60 amounts in the submitted data? what do the -1 you have shown in the sample data mean?

 

then, for each of the possibilities of adding cards, removing cards, or changing the card_amount, you would design the correctly type of query to insert, update, or delete the data.

Edited February 23, 2016 by mac_gyver

[xXGriMe]

When a player registers in the game 60 rows are created in player_deck for that player. You can think of each of those rows as a slot for a card. The player will always ever have 60 rows because that is the max amount of unique cards a player is able to have in the deck.

 

The first 40 slots are auto populated with cards for the player. So now player_deck will look something like this.

 

player_deck

row_id      player_id      card_id 

1               34                78

row_id      player_id      card_id 

2               34                66

row_id      player_id      card_id 

3               34                34

row_id      player_id      card_id 

4               34                -1

row_id      player_id      card_id 

5               34                -1

 

A row with -1 as the card id means there is no card in that slot. So by looking at that i can see the player has 3 cards.

 

Now in the game I load those cards from the database. From the game the player is able to add or remove cards if they like. When the player saves the deck from the game it calls my php page with the new cards the player changed.

 

Something like this might be sent to the php page.

$playerDeckCardIds=$_GET['deckcardids']; //This contains a string that looks something like this. "67 98 3 66 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 78 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 " 

From that I can see the player sent a deck that has 43 unique cards and 17 card slots that are empty.

 

 

$deckCardIds splits that string up into a array and I remove the last entry in the array. So $deckCardIds should have a index of 59. 

$deckCardIds = explode(" ", $playerDeckCardIds);
array_pop($deckCardIds); 

Then I select the 60 rows from the database I need based on the player_id.

$stmt = $conn->prepare('SELECT row_id FROM player_deck WHERE player_id = :player_id');
$stmt->execute(array('player_id' => $playerId));
$r = $stmt->fetchAll(PDO::FETCH_OBJ);

Loop through those rows. Each iteration the loop with increase the index of $deckCardIds updating the new card_id on the loops current row_id.

foreach($r as $row) 
{
    $stmt = $conn->prepare('UPDATE player_deck SET card_id = :card_id WHERE row_id = :row_id');
    $stmt->bindParam(':card_id', $deckCardIds[$i]);
    $stmt->bindParam(':row_id', $row->row_id);
    $stmt->execute();
    $i++;
}

After all this runs the card_id for all 60 rows in the database gets set to 0 though. 

 

I feel really bad for not being able to explain this better for you guys. I do appreciate you taking your time to try and help me though.

Edited February 23, 2016 by xXGriMe

[mac_gyver]

your database design needs help. you should not create x empty rows, then try to manage the data in those rows.

 

you should only insert data that exists and delete data that gets removed. you can limit the maximum number of rows that can be inserted by using an INSERT ... SELECT query, with a COUNT() term and comparison in the SELECT part to only insert a row if the count is less than the maximum.

 

to do this, you need to have a unique composite index set up for the player_id/card_id columns so that you cannot insert the same card_id for any player more than one time.

 

see the following query - 

 INSERT INTO player_deck (player_id, card_id, card_amount)
 SELECT -- the following values being SELECTed are the data to insert, unknown if you can use place-holders and bound data
 1234, 4, 1 FROM DUAL -- dual is an allowed dummy table name to satisfy the FROM ... WHERE syntax
 WHERE (SELECT COUNT(*) FROM player_deck WHERE player_id = 1234) < 5 -- insert the data if the WHERE (subquery count) < 5 is TRUE

the 1234, 4, 1 example data are the player_id, card_id, and card_amount values. you would supply these to the query. the player_id value occurs a second time later in the query.

 

the 5 in the < 5 is the limit. you would change it to 60. if the count is less than this value, the row will be inserted. if the count is equal to greater than this value, the row will not be inserted.

 

if you are actually trying to insert this data if it doesn't exist or update the card_amount if a row already exists, you can add an ON DUPLICATE KEY UPDATE ... to the end of this query (just tested.)

[Psycho]

As I stated before, you have no data in the POST data to associate each of the records with existing records in the database. For example, with this

 

$playerDeckCardIds=$_GET['deckcardids']; //This contains a string that looks something like this. "67 98 3 66 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 78 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 "

 

Which row_id should be updated with the card_id of 67? Are you going to *assume* that the first record in the post string should be associated with the lowest number card_id for the user? (FYI: You should never assume when it comes with interpreting data). I'm really not interested in reviewing your project and the business rules to determine all the changes needed suffice to say it appears to be seriously flawed.

 

But, I will give you this:

 

1) If the card_id is not used as a reference in other tables you could just delete all the existing records for the user then create new ones based on the POSTed data.

 

2. Run a query to get a list of the card_id's for all the users records, then run a loop over each one and update each with the 'next' record in the post data. This assumes there is no importance in which record is updated with what value. This appears to be what you were trying to achieve - but you were trying to reference an array value as an object property

[xXGriMe]

As I stated before, you have no data in the POST data to associate each of the records with existing records in the database. For example, with this

$playerDeckCardIds=$_GET['deckcardids']; //This contains a string that looks something like this. "67 98 3 66 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 78 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 "

Which row_id should be updated with the card_id of 67? Are you going to *assume* that the first record in the post string should be associated with the lowest number card_id for the user? (FYI: You should never assume when it comes with interpreting data). I'm really not interested in reviewing your project and the business rules to determine all the changes needed suffice to say it appears to be seriously flawed.

 

But, I will give you this:

 

1) If the card_id is not used as a reference in other tables you could just delete all the existing records for the user then create new ones based on the POSTed data.

 

2. Run a query to get a list of the card_id's for all the users records, then run a loop over each one and update each with the 'next' record in the post data. This assumes there is no importance in which record is updated with what value. This appears to be what you were trying to achieve - but you were trying to reference an array value as an object property

Thanks for the advice. I am going to go back and rethink how this data should be sent and revived. I originally was going to just delete rows not used by the player in player_deck rather than having -1 be a empty card slot but i figured that it would increase the latency from client to database. I also thought about what would happen if say the server crashed while the database was deleting and inserting rows. If that happened the player could potentially lose a account that they spent hours on. Regardless thank everyone in here for spending time trying to help me. If I have more questions ill be back.