Unity 利用 WWW 類別結合 PHP、MySQL 實現 LBS

在上一篇文章 － Unity 運用 LocationService Interface 實現定位功能 後，我們可以來思考如何運用目前所在的地理資訊來得到儲存在DataBase裡離你最近的Data (前提是這些Data也須具有Spatial Type才能進行計算，DataBase Table建置可參考我這篇 － MySQL使用Spatial point type計算點與點間的距離)，接下來我們以PHP、MySQL做為後端，以JSON為交換格式，來實現Location Base Service吧！

• PHP部分 (nearStore.php)

可以發現我們用$_GET來取得 Unity WWW Class 所傳遞過來的資料，而這邊geoPoint就是Unity Client端目前的lacation Data，而最後再將Query出來的結果encode成Json字串格式回傳給客戶端。

header('Content-Type: text/html; charset=utf-8');
    $database = mysql_connect('DB_IP', 'DB_ACCOUNT', 'DB_PassWord') or die('Could not connect: ' . mysql_error());
 mysql_query("SET NAMES 'UTF8'");
    mysql_select_db('openfire_scores') or die('Could not select database');
 
 $geoPoint = mysql_real_escape_string($_GET['geoPoint'], $database); 
 $query = "SELECT *,(GLength(LineStringFromWKB(LineString(geoPoint,GeomFromText('POINT({$geoPoint})'))))) AS distance FROM stores ORDER BY distance ASC LIMIT 5";
 
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());
 
    $num_results = mysql_num_rows($result);  
 
    for($i = 0; $i < $num_results; $i++)
    {
         $row = mysql_fetch_array($result);
         $store_json[$i]["id"] = $row['s_id'];
   $store_json[$i]["name"] = $row['s_name'];
   $store_json[$i]["photoLink"] = $row['s_photoLink'];
   $store_json[$i]["description"] = $row['s_description'];
    }
 
 $echo_json = json_encode($store_json);
 echo $echo_json;
 exit();

• C#部分 (HSController.cs)

Unity部分主要運用到幾個概念，StartCoroutine等待另一個Function完成、LacationService定位、WWW GET&retrieve PHP的echo Content，Using MiniJson來將PHP回傳的Json decoding 成字串存入陣列！程式邏輯如下：Start() -> GetStore() 嘗試發出Query但要先等待GetGps()得到geoPoint -> 完成後再回到GetStore -> 以GET方式將geoPoint Send往PHP來達成以目前為中心進行 LBS Query -> PHP回傳的JSON字串存在 www.text 再用MiniJson deCoding即可！

using UnityEngine;
using System.Collections;
using System.Timers;
using System.Collections.Generic;
using MiniJSON;

public class HSController : MonoBehaviour
{
 public string findStoreURL = "http://your_ip/sample_test/nearStore.php";

 LocationInfo currentGPSPosition;
 public string gps_String;

 void Start()
 {
  StartCoroutine(GetStores());
 }
 
 IEnumerator GetStores()
 {
  string id ="";
  string name ="";
  string url ="";
  string desc ="";
  yield return StartCoroutine (GetGps());//wait for gps
  string findstore_url = findStoreURL + "?geoPoint=" + gps_String;
  print (findstore_url);
  WWW hs_get = new WWW(findstore_url);
  yield return hs_get;
  
  if (hs_get.error != null)
  {
   print("There was an error getting the nearest stores: " + hs_get.error);
  }
  else
  {
   IList jsonList = (IList)Json.Deserialize(hs_get.text);
   
   foreach(IDictionary param in jsonList){
    id = (string)param["id"];
    name = (string)param["name"];
    url = (string)param["photoLink"];
    desc = (string)param["description"];
    Debug.Log("id:"+id);
    Debug.Log("name:"+name);
    Debug.Log("url:"+url);
    Debug.Log("desc:"+desc);
   }
  }
 }

 IEnumerator GetGps()
 {
  if (!Input.location.isEnabledByUser)
   yield break;
  
  Input.location.Start ();
  int maxWait = 20;
  while (Input.location.status == LocationServiceStatus.Initializing && maxWait>0) {
   yield return new WaitForSeconds(1);
   maxWait--;
  }//end while
  if (maxWait < 1) {
   print ("Time out");
   yield break;
  }
  if (Input.location.status == LocationServiceStatus.Failed) {
   print("Unable to determine device location");
   yield break;
  } else {
   print("Location: " + Input.location.lastData.latitude + " " + Input.location.lastData.longitude + " " + Input.location.lastData.altitude + " " + Input.location.lastData.horizontalAccuracy + " " + Input.location.lastData.timestamp);
   currentGPSPosition = Input.location.lastData;
   gps_String = currentGPSPosition.longitude + "%20" + currentGPSPosition.latitude;
   print (gps_String);
   Input.location.Stop();
   yield break;
  }
 } 
}

或許這並不是一個好的解決方式，確是可行的方法，若有其他更好的做法也希望能與我分享討論，最後分享一些參考的連結：

1. Unity Scripting API - WWW
2. Unity Scripting API - Couroutine
3. Unity uses PHP connecting to MySQL -  Server Side Highscores 實現排行板功能