- Home /
C# How to select certain SQL data?
I have a c# script using a web request to connect to my mySQL database through php. Right now it displays the entire table on one GUIText component on a GameObject. My database has 3 columns, one for the identifying number, one for the Name, and one that contains image URLs. I want the game to use the level number and choose the SQL row with a identifying number corresponding to that number. It would select that row and put it’s Name in the GUIText component and use the Image URL to put in image on another component. When the level number changes to the next level the content would need to change to the corresponding row. My problem is I only know how to get Unity to show the whole table at once… How do I get it to use a level number (integer) to choose one row of the mysql table and show one column at a time?
The PHP:
$query = "SELECT * FROM `CharacterGallery` ORDER by `charID` DESC 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);
echo $row['charID'] . "\t" . $row['Name'] . "\n" . $row['ImageURL'] ."\t";
}
The c#:
using UnityEngine;
using System.Collections;
public class CharacterGallery : MonoBehaviour
{
public string displaychargalleryURL = "http://displaychargalleryurl.com";
void Start()
{
StartCoroutine(GetCharacterGalleryQue());
}
// Get the questions from the MySQL DB to display in a GUIText.
// remember to use StartCoroutine when calling this function!
IEnumerator GetCharacterGalleryQue()
{
gameObject.GetComponent().text = "Loading Scores";
WWW hs_get = new WWW(displaycharactergalleryURL);
yield return hs_get;
if (hs_get.error != null)
{
print("There was an error getting the Character Data: " + hs_get.error);
}
else
{
gameObject.GetComponent().text = hs_get.text;
}
}
}
You can change your query to include the WHERE CLAUSE
$query = "SELECT * FRO$$anonymous$$ CharacterGallery
WHERE 'charID = 1
And if you want to change that 1 , do it in php change it to some variable and similarly make a variable in C# and than send it in your URL .
Okay, that's helpful, thanks. Now could you help with the part of displaying conent from columns according to the charID column? Like say the charID variable is 1, on my "Image" gameobject, I need it to show the image stored in the "image" column on the row charID 1 row. And on my "Title" gameobject, it needs to display the text stored in the "title" column of the same row as charID 1.
Than the next step would be , to get the data in your hs_get as hs_get.text and than from what you retrieve you have to separate the values ,for that i would suggest first on php side using your result ,encode it into JSON and use some json parsing utility in unity you will easily get the name and the image url this way and than make another request with image url and you can then use the way unity documentation says public class ExampleClass : $$anonymous$$onoBehaviour { public string url = "http://images.earthcam.com/ec_metros/ourcams/fridays.jpg"; IEnumerator Start() { WWW www = new WWW(url); yield return www; Renderer renderer = GetComponent(); renderer.material.mainTexture = www.texture; } }