how do I add and subtract with mysql database

Get help using Construct 2

Post » Sun Jan 14, 2018 8:43 pm

Hello there!

I've looked around for my issue. I've found several tutorials and forum topics about highscores with AJAX and PHP. In other small games I've made that all works great. What I'm trying is not to achieve a new score but to add a score on top of a already excisting one. I'm not a coder, if someone gives me a head start that would be great to start with!

What I would like to achieve is the following:

On the inputpage I have a 'list' called names and a 'list' called numbers. What I would like to achieve is then when I press a button the amount in the list 'numbers is added to the present score in the MYSQL database of the person mentioned in the 'list' names. If the name doesn't exist it creates one.

Other thing is how to subtract and how to reset all scores to 0 in the database and how to show the scores of all persons on a page.

Many thanks in advance!
Ralf
B
7
S
1
Posts: 8
Reputation: 391

Post » Mon Jan 15, 2018 1:31 pm

this is quite a custom necessity therefore you must write a program for it, a php script could be the best idea, since is simple to use. Start learning php and mysql and how to use php to execute query on a database :)
B
10
S
4
G
1
Posts: 49
Reputation: 930

Post » Mon Jan 15, 2018 9:26 pm

Thank you for your reply.

After spending some time searching and trying I almost got it to work. I have found the following PHP file for saving a name & score:
Code: Select all
<?php


$db = "";//Your database name
$dbu = "";//Your database username
$dbp = "";//Your database users' password
$host = "";//MySQL server - usually localhost

$dblink = mysql_connect($host,$dbu,$dbp);
$seldb = mysql_select_db($db);

if(isset($_GET['name']) && isset($_GET['score'])){

     //Lightly sanitize the GET's to prevent SQL injections and possible XSS attacks
     $name = strip_tags(mysql_real_escape_string($_GET['name']));
     $score = strip_tags(mysql_real_escape_string($_GET['score']));
    $sql = mysql_query("INSERT INTO `$db`.`fil078` (`id`,`name`,`score`) VALUES ('','$name','$score');");
     
     if($sql){
     
          //The query returned true - now do whatever you like here.
          echo 'Your score was saved. Congrats!';
         
     }else{
     
          //The query returned false - you might want to put some sort of error reporting here. Even logging the error to a text file is fine.
          echo 'There was a problem saving your score. Please try again later.';
         
     }
     
}else{
     echo 'Your name or score wasnt passed in the request. Make sure you add ?name=NAME_HERE&score=1337 to the tags.';
}

mysql_close($dblink);//Close off the MySQL connection to save resources.
?>


I managed to get it to work for 50% of my needs. What I still would like to achieve:
1. When a new player uploads his/her score that he/she pops up in the highscore list (this happens now)
2. When a excisting player uploads his/her score the score is added to the amount he/she already has (not happening now)

Hope someone can help me with this ;)
B
7
S
1
Posts: 8
Reputation: 391

Post » Tue Jan 16, 2018 7:57 am

Well, before inserting a new score in the table you should make a query to check if in the table already exists a record with the given name, in that case you'll have to make an update on the existing row instead of an insert.
But there are some things that you may want to consider:
1: are names like "John" and "john" the same or should you consider them different?
2: is really the name a proper index to save a player score? What if two different players use the same name? Their scores will be merged into one! Would it be correct and acceptable? If not, we you should study a way to tell these players apart
B
10
S
4
G
1
Posts: 49
Reputation: 930

Post » Tue Jan 16, 2018 7:10 pm

Again tnx for your reply.

I asume using an ID number is ofcourse better to avoid conflicts. In this case it doesn't matter since it will be a small group of people who'll be using/playing it.

The thing is don't know how to make the right query/function (or whatever it takes ;) ) on the database so that scores are added up incase of an already excisting name.

Any ideas on how to do that?
B
7
S
1
Posts: 8
Reputation: 391

Post » Tue Jan 16, 2018 9:41 pm

Hi again,

A friend of mine mentioned the following:

Code: Select all
$sqlInsert = mysql_query("INSERT INTO `$db`.`fil078` (`id`,`name`,`score`) VALUES ('','$name','$score')");
$sqlUpdate = mysql_query ("UPDATE `$db`.`fil078` SET score = score + '$score' WHERE name = '$name';");


They work when used seperatly. When combined only the INSERT query works.

Any idea on how to combine these queries?
B
7
S
1
Posts: 8
Reputation: 391


Return to How do I....?

Who is online

Users browsing this forum: DarthPapalo666, krls and 57 guests