CONFIGURE MYSQL DATABASE TO SAVE PROGRESS

and need some help to do one functionality for my unity game. I am trying to save the progress of the player in one mysql database, like this:

Code (CSharp):

1. userid level stars
2. 29        1        2
3. 29        2        1
4. 45        1        3
5. 50        1        2
6. 50        2        3
7. 50        3        1
8. 29        3        3

so the script send the userid provided by the user registration in the begining of the game. and for each level he complete, the script send the number of the level, and the amount of stars collected in the level..

the problem and question is, how I configure this in the php script and mysql database to save the information only once? because if the player with the id 50 play the first level, will add a line with the information, but if the same player play the first level again and change the amount of stars, I dont want a new line, just update the stars amount.

I take a look in the INDEX, UNIQUE, PRIMARY, FULLTEXT, SPATIAL functions but dont figured out what is the correct combination and how to put in the php script, and take a look in other questions in the forum but nothing like this.

thanks for the help!

 

Not a Unity question - you'd have better luck in a PHP/MySQL community.

But to sort of answer your question you'd do an "upsert". Basically, query for the given user ID and if you find it perform an UPDATE; if you don't then perform an INSERT.

 

Hi KelsoMRK, thnaks for the reply,

I post the question on StackOverflow too, and with some help I came to this:

Code (CSharp):

1. $connect     =   new  mysqli ($db_host,$db_user,$db_password,$db_name);
2. if (!$connect){
3.     die("connection failed.". mysqli_connect_error());
4. }
5.  
6. $userid = $_POST['userid'];
7. $level = $_POST['level'];
8. $stars = $_POST['stars'];
9.  
10. $selectuserid = "SELECT * FROM Users_Levels WHERE userid='"$_userid"'";
11. if ('".$selectuserid."' > 0){
12.     $levelsandstars = "UPDATE Users_Levels SET (userid,level,stars) WHERE level='".$level."';
13.                        if ('".$levelsandstars."' == 0){
14.                        INSERT INTO Users_Levels VALUES ('".$userid."','".$level."','".$stars."')";                              
15.                         }
16. }
17.                        
18.            
19. $result = mysqli_query($connect, $levelsandstars);
20. if (!result) echo "ther some error";
21. else echo "sent";

but dont know and anyone there reply if it is the right way to do..
can you say?

Thanks!

 

Nope - not a PHP developer

 

ok, thanks anyway!

 

Yeah, that seems about right, Is there any problem?

 

Hi CloudKid,

the script dont do anything, dont update if is the same level, and dont insert if the level dont exist..
I need to select one userid, and check if the level is already inserted, if yes, I need to update the stars value, if not, I need to inser the userid, level and stars. but with this nothing happens

thanks!

 

Oh wait, does that Update query work? you are not providing any value to userid, level and stars.
It should be something like this:

Code (CSharp):

1. UPDATE Customers
2. SET ContactName='Alfred Schmidt', City='Hamburg'
3. WHERE CustomerName='Alfreds Futterkiste';

Also, you should echo mysqli_error($connector) in order to see if there is any error

 

Hi CloudKid,

I made some modifications and make more sense to me like this:

Code (CSharp):

1. $connect     =   new  mysqli ($db_host,$db_user,$db_password,$db_name);
2.     if (!$connect){
3.         die("connection failed.". mysqli_connect_error());
4.     }
5.    
6.     $userid = $_POST['userid'];
7.     $level = $_POST['level'];
8.     $stars = $_POST['stars'];
9.    
10.     $selectuserid = "SELECT * FROM Users_Levels WHERE userid='".$userid."'";
11.    
12.     if ($selectuserid > 0){
13.         $selectlevel = "SELECT * FROM Users_Levels WHERE level='".$level."'";
14.         if ($selectlevel > 0){
15.             "UPDATE Users_Levels SET (userid,level,stars) VALUES ('".$userid."','".$level."','".$stars."') WHERE level='".$level."'";
16.         }else{
17.             "INSERT INTO Users_Levels VALUES ('".$userid."','".$level."','".$stars."')";                                                
18.         }
19.        
20.     }                          
21.              
22.     $result = mysqli_query($connect, $selectuserid);
23.     if (!result) echo mysqli_error($connect)
24.     else echo "sent";

but now I receive the error:
syntax error, unexpected T_ELSE, expecting ',' or ';' on this line else echo "sent";

 

Well, as the error says, you forgot to add ";" after "echo mysqli_error($connect)"

 

Hi CloudKid,

I fix the error but the script still not work.. so I changed the thing a little bit.. I am adding the levels and other things to a list like this:

Code (CSharp):

1. public class PlayerPreferences
2. {
3.     public string PlayerPrefsKey;
4.     public string PlayerPrefsValue;
5.  
6.     public  PlayerPreferences (string NewPlayerPrefsKey, string NewPlayerPrefsValue){
7.         PlayerPrefsKey = NewPlayerPrefsKey;
8.         PlayerPrefsValue = NewPlayerPrefsValue;
9.     }
10. }

Code (CSharp):

1. public List<PlayerPreferences> playerprefslist = new List<PlayerPreferences>();
2.  
3. playerprefslist.Add (new PlayerPreferences (key,value));

and I am trying to send to database with this scripts:

Code (CSharp):

1. public class SCRIPT_SAVE_SavePlayerPrefsOnWebServer : MonoBehaviour {
2.  
3.     [SerializeField] public string PlayerPrefsUrl;
4.     [SerializeField] public string SecureKey;
5.     public int UserID;
6.     public string PlayerPrefsKey;
7.     public string KeyValue;
8.  
9.  
10.     // Use this for initialization
11.     void Start () {
12.         if (PlayerPrefs.HasKey ("userID")){
13.             UserID = PlayerPrefs.GetInt ("userID");
14.         }
15.         StartCoroutine (SendPlayerPrefs ());
16.     }
17.  
18.     IEnumerator SendPlayerPrefs(    ){
19.  
20.         foreach (var playerpreferences in FlipWebApps.GameFramework.Scripts.GameStructure.GameManager.Instance.playerprefslist) {
21.             WWWForm playerprefsform = new WWWForm ();
22.             playerprefsform.AddField ("userid", UserID.ToString ());
23.             playerprefsform.AddField ("PlayerPrefsKey", playerpreferences.PlayerPrefsKey.ToString());
24.             playerprefsform.AddField ("KeyValue", playerpreferences.PlayerPrefsValue.ToString ());
25.             WWW SendPlayerPrefsForm = new WWW (PlayerPrefsUrl, playerprefsform);
26.             yield return SendPlayerPrefsForm;
27.         }
28.         print ("Sucess");
29.         StopAllCoroutines ();
30.     }
31. }

Code (CSharp):

1. $connect     =   new  mysqli ($db_host,$db_user,$db_password,$db_name);
2. if (!$connect){
3.     die("connection failed.". mysqli_connect_error());
4. }
5.  
6. $userid = $_POST['userid'];
7. $PlayerPrefsKey = $_POST['PlayerPrefsKey'];
8. $KeyValue = $_POST['KeyValue'];
9.  
10. $playerprefs = "INSERT INTO PlayerPrefs(userid,PlayerPrefsKey,KeyValue)
11.                VALUES ('".$userid."','".$PlayerPrefsKey."','".$KeyValue."')";
12.  
13.              
14. $result = mysqli_query($connect, $playerprefs);
15. if (!result) echo "ther some error";
16. else echo "sent";

in my mysql table, I made this:

Code (CSharp):

1.  userid     PlayerPrefsKey     KeyValue  
2. 13             xxxx                      xxx
3. 13             xxxx                      xxx
4. 4               xxxx                      xxx
5. 13             xxxx                      xxx
6. 5               xxxx                      xxx

**the userid is a idex colum

So I receive the values, and is all working, but if I try this:

Code (CSharp):

1.  
2.   $selectuserid = "SELECT * FROM PlayerPrefs WHERE userid='".$userid."'";  
3.   if ($selectuserid > 0){
4.   $selectlevel = "SELECT * FROM PlayerPrefs WHERE PlayerprefsKey='".$PlayerprefsKey."'";
5.      if ($selectlevel > 0){
6.        "UPDATE PlayerPrefs SET (userid,PlayerprefsKey,KeyValue) VALUES ('".$userid."','".$PlayerprefsKey."','".$KeyValue."') WHERE PlayerprefsKey='".$PlayerprefsKey."'";
7.      }else{
8.   "INSERT INTO PlayerPrefs (userid,PlayerprefsKey,KeyValue) VALUES ('".$userid."','".$PlayerprefsKey."','".$KeyValue."')";  
9.      }
10.    
11.   }

the code dont work, dont INSERT and dont UPDATE.. I already try a lot of combinations like, UPDATE WHERE, or UPDATE ON DUPLICATED KEY, and other things that you suggest and I found in the web, but no success, and now I am run out of ideias.. can you say what can I do?

NOTE: this intire system and code I made just because you are helping, and I dont know how to be thankful enough for all the help!

 

Hey, so, I feel like the initial code was better, so I will describe where your problem is using that.
So you are creating a query using:

Code (CSharp):

1.     $selectuserid = "SELECT * FROM Users_Levels WHERE userid='".$userid."'";

However, this it's just a string, it dosn't execute. In order to get the result you need to execute the script.
When you are dooing: if($selectuserid >0) you are just comperint the strin "Selet * from..." with 0

You will need to send the request using $result = mysqli_query($connect, $selectuserid);
Then:

Code (CSharp):

1.  
2.  
3. $request = ""
4. if (mysqli_num_rows($result) > 0) {
5.     // output data of each row
6.     while($row = mysqli_fetch_assoc($result)) {
7.         echo "id: " . $row["userid"]. " - Name: " . $row["level"]. " " . $row["stars"]. "<br>";
8.     }
9.    $request = "UPDATE Users_Levels SET (userid,level,stars) VALUES ('".$userid."','".$level."','".$stars."') WHERE level='".$level."'";
10. } else {
11.     echo "0 results";
12.     $request = "INSERT INTO Users_Levels VALUES ('".$userid."','".$level."','".$stars."')";
13. }
14. //the rest of your code that sends a querry and prints if any error
15. mysqli_query($connect, $request)
16.  
17.  

I hope this piece of code will make you understand how php and sql works. You don't actually need that while. That is just to debug.
In conclusion, you were not doing anything with you sql strings. You always have to send them using mysqli_query or other provided function

 

Hi CloudKid,

thanks for the explanation, so every time I need to check some value, I need to make a query to connect and check the values, and save the values in the script with a result, and after that, check if the number of rows are bigger than 0.. and make a new query to update or insert the values, is this how the php works?

based on your explanation I make this:

Code (CSharp):

1. $connect     =   new  mysqli ($db_host,$db_user,$db_password,$db_name);
2. if (!$connect){
3.     die("connection failed.". mysqli_connect_error());
4. }
5.  
6. $userid = $_POST['userid'];
7. $PlayerPrefsKey = $_POST['PlayerPrefsKey'];
8. $KeyValue = $_POST['KeyValue'];
9.  
10.              
11. $selectuserid = "SELECT * FROM PlayerPrefs WHERE userid='".$userid."'";
12. $selectuseridresult = mysqli_query ($connect, $selectuserid);
13.  
14. $request = "";              
15. if (mysqli_num_rows ($selectuseridresult) > 0){
16.     while($row = mysqli_fetch_assoc($result)) {
17.         echo "id: " . $row["userid"]. " - Name: " . $row["PlayerPrefsKey"]. " " . $row["KeyValue"]. "<br>";
18.     }
19.     $request = "UPDATE PlayerPrefs SET (userid,PlayerprefsKey,KeyValue) VALUES ('".$userid."','".$PlayerprefsKey."','".$KeyValue."') WHERE PlayerprefsKey='".$PlayerprefsKey."'";
20. }else{
21.     echo "0 results";
22.     $request = "INSERT INTO PlayerPrefs VALUES ('".$userid."','".$PlayerprefsKey."','".$KeyValue."')";                                              
23. }
24.  
25.              
26. $result = mysqli_query($connect, $request);
27. if (!result) echo "ther some error";
28. else echo "sent";

I dont know why, but the values dont update, and if I delete all the data in the table to value be 0 the script just uploaded id one time.. I think maybe the way I am sending the information from Unity is the problem,

Code (CSharp):

1.  IEnumeratorSendPlayerPrefs(){
2.  
3. foreach(var playerpreferences in playerprefslist){
4. WWWFormplayerprefsform=newWWWForm();
5. playerprefsform.AddField("userid",UserID.ToString());
6. playerprefsform.AddField("PlayerPrefsKey",playerpreferences.PlayerPrefsKey.ToString());
7. playerprefsform.AddField("KeyValue",playerpreferences.PlayerPrefsValue.ToString());
8. print(playerpreferences.PlayerPrefsKey);
9. print(playerpreferences.PlayerPrefsValue);
10. WWWSendPlayerPrefsForm=newWWW(PlayerPrefsUrl,playerprefsform);
11. yieldreturnSendPlayerPrefsForm;
12. }
13. print("Sucess");
14. StopAllCoroutines();
15. }

because from there I send one request for each value in the list to php code.. so pherhaps, I need to do that in the php code too? or maybe because I am sending all togheter the php code have not time to check the information and update or insert?

is there a way to save the information that I am sending from unity in php code, and after that send the information to database?

Thanks!

 

Hi CloudKid,

I have the honor to say that I finally make it work!

Code (CSharp):

1. $connect     =   new  mysqli ($db_host,$db_user,$db_password,$db_name);
2. if (!$connect){
3.     die("connection failed.". mysqli_connect_error());
4. }
5.  
6. $userid = $_POST['userid'];
7. $PlayerPrefsKey = $_POST['PlayerPrefsKey'];
8. $KeyValue = $_POST['KeyValue'];
9.                
10. $selectuserid = "SELECT * FROM PlayerPrefs WHERE userid='".$userid."'";
11. $selectuseridresult = mysqli_query ($connect,$selectuserid);
12.  
13. $request = "";                
14. if (mysqli_num_rows ($selectuseridresult) > 0){
15.     $selectplayerprefkey = "SELECT PlayerPrefsKey FROM PlayerPrefs WHERE PlayerPrefsKey='".$PlayerPrefsKey."'";
16.     $selectplayerprefkeyresult = mysqli_query ($connect,$selectplayerprefkey);
17.     if (mysqli_num_rows ($selectplayerprefkeyresult) > 0){
18.         $request = "UPDATE PlayerPrefs SET KeyValue='".$KeyValue."' WHERE PlayerPrefsKey='".$PlayerPrefsKey."'";  
19.     }else{
20.         $request = "INSERT INTO PlayerPrefs VALUES ('".$userid."','".$PlayerPrefsKey."','".$KeyValue."')";
21.     }      
22. }else{
23.     $request = "INSERT INTO PlayerPrefs VALUES ('".$userid."','".$PlayerPrefsKey."','".$KeyValue."')";
24. }
25.  
26.                
27. $result = mysqli_query($connect, $request);
28. if (!result) echo "ther some error";
29. else echo "sent";

in this way, all things worked! really thanks for all the help!

 

Code (CSharp):

1. IEnumerator aha(float snaga)
2.     {
3.         WWW coneccion = new WWW ("http://localhost/wohweb/info.php?uss=" + txtUsuario.text + "&snaga=" + snaga.ToString ());
4.  
5.         yield return(coneccion);
6.          if (coneccion.text == "202") {
7.             Debug.Log ("aaa");
8.  
9.  
10.             snaga = snaga;
11.  
12.         } else {
13.             print (coneccion.text);
14.             Debug.LogError ("neee");
15.         }
16.  
17.  
18.     }
19.  

php code

Code (CSharp):

1. <?php
2. include_once 'db.php';
3.  
4. $uss = @$_GET['uss'];
5. $snaga = @$_GET['snaga'];
6.  
7. if(!$con)
8. {
9.     echo "400";
10. }else{
11.     $sql = "SELECT * FROM usuarios WHERE username LIKE '$uss'";
12.    
13.     $result = mysqli_query($con,$sql);
14.     if(mysqli_num_rows($result)>0)
15.     {
16.        
17.         $sql = "UPDATE  usuarios SET snaga =  '$snaga' WHERE username LIKE '$uss'";
18.         $result = mysqli_query($con,$sql);
19.         echo "202";
20.  
21.     }else{
22.         echo "401";
23.     }
24.  
25. }
26.  
27.  
28. ?>

how i can when add in unity editor "snaga" number to 100, and "snaga" update in db to 100..

sory for english
i hope you understand