Skip to main content

カード削除

To delete cards, we need to create a new API in PHP that

  • Receives the user id
  • Receives a list of card ids to remove (including duplicates)
  • Runs an SQL query that reduces the count by 1 for each card that matches the request
  • If the count reaches zero, remove the record from the database

Removing cards using SQL

First, let's see how decreasing the count in SQL would look like. Let's say we want to decrease user_id = 1, card_id = 1 by two cards. Since this user has 2 cards of card_id = 1, this would remove all cards. We can decrease the count by telling MySQL to subtract 2 from the field "count" like so:

UPDATE user_cards SET count = count - 2 WHERE user_id = 1 and card_id = 1;

This is telling MySQL to take the column "count" and subtract it by 2, but only for user_id = 1and card_id = 1. After running it we get:

image.png

We can check that the count went to zero. All we have to do next is remove all records where count is 0.0 or less. We can easily do that with DELETE.

DELETE FROM user_cards WHERE count <= 0;

We can use these two instructions in PHP to remove cards from players. Let's design our API

PHP スクリプト

Before we get started, let's restore our database. Run the following SQL command in phpMyAdmin:

REPLACE INTO `user_cards` (`id`, `user_id`, `card_id`, `count`) VALUES
(1, 1, 1, 2), (2, 1, 2, 1), (3, 1, 5, 1),(4, 1, 8, 2),
(5, 1, 6, 1), (6, 2, 3, 5), (7, 2, 6, 3),(8, 2, 12, 1);

Now, let's design our script. Create a new file called delete_player_cards.php.  In our API, we are going to request the client to send us the user id and how many cards to delete for each card id. Since the amount of cards to be deleted is variable, we are going to request the client to send the cards to be deleted as a JSON object that looks like this:

{
	"user_id" : "1",
	"cards" : [
		{ 
			"id" : "1",
			"count" : 2
		},
		{ 
			"id" : "8",
			"count" : 1
		},		
	]
}

In this example, we want to remove 2 copies of card id "1", and 1 copy of card id "8" for user id "1". Since we don't have any way to send data yet, let's code it directly into the script first. We'll use POST later to send the data from Unity.

<?php
	// JSONを返すべき
	header("Content-Type: application/json; charset=utf-8");

	// 接続 
	$dbh = new PDO('mysql:host=localhost; dbname=card_game', "root", "", [PDO::ATTR_PERSISTENT => true]);
	
	// テスト中にデータを固定
	$data = '{
		"user_id" : "1",
		"cards" : [
			{ "id" : "1", "count" : 2 },
			{ "id" : "8", "count" : 1 }
		]
	}';
	
	// JSONに変換
	$json = json_decode($data, true);
	
	// ユーザーIDと削除したいデータの分ける
	$user_id   = $json['user_id'];
	$card_list = $json['cards'];
?>

Now we can build our SQL statement using a for loop to iterate over each card delete request in the array.

// SQL文を構築
foreach ($card_list as $card)
{		
    $sql = "UPDATE user_cards SET count = count - " . $card['count']
         . " WHERE user_id = " . $user_id
         . " AND card_id = " . $card['id'];
         
    // 確認しましょう
    print($sql . "\n");
}

If we run it, we should see something like this:

image.png

This is exactly what we need to delete cards! Let's complete our script by executing the SQL statement and delete cards from the database.

// SQL文を構築
foreach ($card_list as $card)
{		
    $sql = "UPDATE user_cards SET count = count - " . $card['count']
         . " WHERE user_id = " . $user_id
         . " AND card_id = " . $card['id'];
         
    $dbh->exec($sql);
}		

// 最後に、countが0以下になったカードを削除
$dbh->exec("DELETE FROM user_cards WHERE count <= 0");

// 空のJSONを返す
print("{}")

and we can check before running our code:

image.png

and after:

image.png

All that is left now is to replace our test JSON with the actual POST input. This is our final script:

<?php
	// JSONを返すべき
	header("Content-Type: application/json; charset=utf-8");

	// 接続 
	$dbh = new PDO('mysql:host=localhost; dbname=card_game', "root", "", [PDO::ATTR_PERSISTENT => true]);
	
	// POSTデータを取得
	$data = $_POST['data'];
	
	// JSONに変換
	$json = json_decode($data, true);
	
	// ユーザーIDと削除したいデータの分ける
	$user_id   = $json['user_id'];
	$card_list = $json['cards'];
	
	// SQL文を構築
	foreach ($card_list as $card)
	{		
		$sql = "UPDATE user_cards SET count = count - " . $card['count']
  		     . " WHERE user_id = " . $user_id
			 . " AND card_id = " . $card['id'];
			 
		$dbh->exec($sql);
	}		
	
	// 最後に、countが0以下になったカードを削除
	$dbh->exec("DELETE FROM user_cards WHERE count <= 0");
	
	// 空のJSONを返す
	print("{}")	
?>

Unityを使い、APIとの接続

Our next step is to implement the Unity side. We need to create the same data structure our PHP script is expecting.

 

 

Back to top