0

I am new in forum, 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:

userid level stars
29     1     2
29     2     1
45     1     3
50     1     2
50     2     3
50     3     1
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!

Umair M
  • 10,298
  • 6
  • 42
  • 74
Bryan Jonson
  • 1
  • that's not a mysql problem. that's a client-side logic problem. YOU need to write the code to decide what should happen. – Marc B Sep 26 '16 at 16:23
  • The sql side of thing can be taken care of a proper primary key / unique index and an insert ... on duplicate key update ... statement. – Shadow Sep 26 '16 at 16:29
  • I not say that is a mysql problem, I just dont know how configure this in the php and mysql database.. because primary key takes only one value, and unique can take reppeated values, so the logic says that the userid have to be a unique field, and the level have to be a primary key, but how configure to the leve be a primary key based on the userid? because if I dont make this when one player save the progress of level 1, others players will not save.. because this primary key already taken.. – Bryan Jonson Sep 26 '16 at 16:30
  • Shadow I am new to php programing, how I do what you suggest? – Bryan Jonson Sep 26 '16 at 16:50

4 Answers4

0

I recommend you use http://redis.io/ (in-memory data structure store, used as database, cache and message broker) to save progress in the games.

slava
  • 791
  • 1
  • 11
  • 26
0

First you want an unique index on the combination (userid, level) and then you want to do an update if the combination exists and an insert otherwise.

For how to create the unique index please take a look at How do I specify unique constraint for multiple columns in MySQL?

For how to code the SQL query to do update/insert please take a look at SQL: If Exists Update Else Insert

The article above uses Microsoft SQL syntax. In PHP you can code this by issuing the query and then using mysql_affected_rows to see how many rows where affected. If 0 rows where affected then you issue the INSERT query from PHP.

Community
  • 1
  • 1
Cristian Bidea
  • 679
  • 4
  • 12
  • Hi Cristian Bidea, I am trying to implement your suggestion.. so I go to php panel and maque a UNIQUE index and apply the same index to userid and level.. and in my php code I am making in this way: – Bryan Jonson Sep 26 '16 at 18:26
  • $selectuserid = "SELECT * FROM Users_Levels WHERE userid='"$_userid"'";.......................................‌​..... if (selectuserid > 0){.........................................................‌​....................‌​....................‌​................. $levelsandstars = "UPDATE Users_Levels SET (userid,level,stars) WHERE level='".$level."'............................................................................................................................‌​............ if ('".$levelsandstars."' == 0){ INSERT INTO Users_Levels VALUES ('".$userid."','".$level."','".$stars."')"; } } – Bryan Jonson Sep 26 '16 at 18:32
  • No it is not. The code isn't even valid. That's not the way one would encode SQL queries in PHP. Please read the following section from the manual and try to code basic examples. At the end of each documentation page you have ample code examples. 1. http://php.net/manual/en/function.mysql-db-query.php 2. http://php.net/manual/en/book.mysql.php – Cristian Bidea Sep 27 '16 at 08:03
0

in pseudo code you need to do something like this in SQL.

UPDATE $table set column=value WHERE id=$ID

Max B
  • 42
  • 8
0

Hi brayan actually the problems is that no one will write code for you, you have to do it yourself. I guess you are unaware with SQL i.e., you asked that

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.

Anyhow You first required some basic understanding of SQL and PHP with Unity. I will recommend you this Guide Server_Side_Highscores of unityWiki it help you to make database and server logic intergartion with PHP. Now for your Second important part of question.

You have to update user code after each level completion.Or you can simply ask to user about socre save. Before inserting new record into the database you have to check that userId with level id alread exist or not. some thing like this

Select userid, level, stars
from youTableName
where userid = ?
and level = ?

if the above query return empty response then you simply need to add the record

INSERT INTO table_name (userid, level, stars)
VALUES (value1,value2,value3);

Otherwise you have to update that specific column.

Muhammad Faizan Khan
  • 10,013
  • 18
  • 97
  • 186
  • Hi Mohammad Faizan Khan, thanks for the reply, I understand that I need a basic knowledge in php and mysql, I am watching and studying this in the web, but somethings are too difficult for me, the english lenguage is not my natural lenguage, so some explanations I dont understand but I trying.. really thanks for your explanations.. I understand some parts and will study the others to implement in the code.. regards! – Bryan Jonson Sep 27 '16 at 14:20