Sending and Receiving data using your own SQL Server - PHP file and TestProject included

build

well if i import a csv file with 1 million or 1000 rows. Gamesalad still limits it to 100 rows.
And what if the game had more than 10000 players with each 10000+ buildings? That would require a lot of rows.

I use windows 8.

NNterprises

Never tried windows 8 but windows 10 works fine.
I imported a 250 row CSV and yes GS only lets me SEE 100 rows of it, but all 250 rows have been tested in GS and ad-hoc and works fine. I'm not sure what real limitations it has, like is 1000000 too much, who knows? But I do know you will only be able to see 100 of them and it works for 250 I can confirm.

And 999 works according to @jonmulcahy

build

i will test with a csv with 1000000 row containing the numbers 1-1000000. Then make it scroll up 5000 numbers a sec. too see how far it goes

Manto

@build said:
i will test with a csv with 1000000 row containing the numbers 1-1000000. Then make it scroll up 5000 numbers a sec. too see how far it goes

Last time I tested, it worked with 30k rows. Though I used my own import script as it was taking a long time with gs.

I don't think you need that many rows in the GS game. You'll need to have all rows on the server, but it is enough to have one or two tables in GS, that will be loaded based on what the player does in the game. For example when a player wants to see other player's buildings, you would use the get table behavior to load only that player's buildings. When they switch the player whose buildings they are looking at, the table can be overwritten.

The_Gamesalad_Guru

@build said:
Then what if a player had more than 100 buildings?
I mean the tables in gamesalad maxes out at 100 rows.

And how do i call the ID specific placement data from a table with thousands of IDs?

Im also not quite sure how to add multiple table values to the .php file.

These are the only problems stopping me from creating my game.

Execpt for the bugged custom collisions, but thats another topic

I think you're asking gamesalad to do more than it can at this point. It's too bad they haven't released their playstogether multiplayer as this would be easier to do. Building a multiplayer game is super complex, even with their multiplayer system that handles the backend. Trust me I built a two player multiplayer with the together option.

build

@The_Gamesalad_Guru said:

I think you're asking gamesalad to do more than it can at this point. It's too bad they haven't released their playstogether multiplayer as this would be easier to do. Building a multiplayer game is super complex, even with their multiplayer system that handles the backend. Trust me I built a two player multiplayer with the together option.

I think so too. The idea wasnt to create a multiplayer game though, It was for grabbing every attribute from a database table and then save it to the table afterwards.

And im pretty sure that you can add several tables into the .php files, im just not entirely sure how to.

chaosmasterro

So I'm working with PHP 7. I changed most of the commands out to an updated mysqli version. I can use both the get and send functions. The get functions returns a 1 and transfer my data in. The send function returns a 1 but when it writes to my table it only writes null/0 in my fields when it should write a different value.

Any idea why? Since it's not a syntax error my log is not updating and the sqlerror.txt file is not returning "Unable to write to database" obviously.

JScott

Hey all, I know this is an old thread, just wondering if someone can help me! I've spent days trying to get this figured out, very frustrating, but now I need to figure it out or die trying...
So, I've got the "get" part working great (I've followed @jonmulcahy 's thread, plus the others I could find). This is funny because I wasn't really trying for "get" and it seems more complicated. But the "send" part is making me crazy! I've included the php and a couple of pics. I'm assuming the connect to SQL is correct as it's the same on my get.php (which works). I've left the password out, but you're welcome to it if you want to give it a try.
I thought it might be due to ghetto free webhosts, but I've tried a few, even paid for this current one. And since it sort of works (for get) I think it must be in the code. The gs file is the same as Jon's asynctest, just changed the URL.

I don't get any error logs(that I can find at least) and I don't know if there should be a .json file showing up somewhere?

Please have a look!

<?php // connect to SQL $servername = "localhost"; $username ="tysonbye_tyson"; $password = "xxxxxxx"; $link = mysql_connect($servername, $username, $password); // database connection strings. change these to your DB and Table names. $dbName = "tysonbye_tyson"; $tableName = "testdata"; if (!$link) { //exit('Error: Could not connect to MySQL server!'); echo mysql_errno($link) . ": " . mysql_error($link). "\n"; } // connect to the table mysql_select_db($dbName)or die("cannot select DB"); // lets prepare some files to capture what is going on. $incomingJson = 'json.txt'; //$fullArray = 'fullArray.txt'; // needed if you enable the debugging secton below $sqlErrorLog = "sqlErrors.txt"; // initialize the string with a blank value $string = ""; // start SEND data if ($_SERVER['REQUEST_METHOD'] === 'POST') { //capture incoming data error_reporting(1); $sig = $_POST["sig"]; $jsondata = $_POST["params"]; // this line captures the sent data so you can figure out what you need to send back. file_put_contents($incomingJson,$jsondata); // this line tells the application that the data send was successful. echo '{"Status":"Success"}'; // convert JSON to an array $array = json_decode(stripslashes($jsondata), TRUE); /* // formats the array to view it easier $results = print_r($array,true); file_put_contents($fullArray,$results); */ //get the total number of objects in the array $arrlength = count($array['Children']['1']['Properties']); // set while loop index $i = 0; //loop through array node and get row values while ($i < $arrlength ) { // get row value $value = $array['Children']['1']['Properties'][$i]['Value']."n"; // convert delimited string to an array $arrayPieces = explode("|", $value); // get array values. This section would have to be modified to capture each value you are interested in. $rowName = $arrayPieces[0]; // this variable will be blank if you don't name your rows. $playerID = $arrayPieces[1]; $playerName =$arrayPieces[2]; $playerStats = $arrayPieces[3]; // construct SQL statement $sql="INSERT INTO ".$tableName."(playerID, playerName, playerStats)VALUES('$playerID', '$playerName', '$playerStats')"; // insert SQL statement $result=mysql_query($sql); // catch any errors if($result){ // if successful do nothing for now. } else { // if failure, write to custom log $sqlError = "Error writing to databasen"; file_put_contents($sqlErrorLog, $sqlError, FILE_APPEND); } $i++; } } // end of POST // close the SQL connection mysql_close($link); ?>

JScott

That wall of text don't look so good, hopefully this is better...

Manto

@JScott said:
That wall of text don't look so good, hopefully this is better...

Did you check the error file defined by the variable $sqlErrorLog ? It should write "Error writing to database" if there's an error.

JScott

Thanks @Manto. It looks like that variable is defined as "sqlErrors.txt", but I don't know where that file goes/is!

The top of the php is cut off in the post above, so I'm attaching the top. It looks like debugging is not enabled due to the "//" in front of "$fullArray"? I've removed those and tried again, but not getting any error logs (or I don't know how to view them...).

jonmulcahy

Uncomment line 43 and 44 and it will dump whatever is incoming to a file in the same directory as the php file.

jonmulcahy

If the file is not created, it's not even getting triggered

Shenanigans Studio

Anyone have a clue as to what the table limit for sql tables is in gamesalad? I'm using my own server for cross platform highscores and I need to Get Table when the person begins playing so that I can check to make sure their username is unique. However I'm worried that if I have 50k,100k,500k,1Mil users(rows in my table) then gamesalad wont be able to import the table. Or perhaps their a work around to check to unique usernames?

JScott

Thanks @jonmulcahy . I've uncommented lines 43 and 44. I also noticed line 12 was commented?

But, uncommenting those, didn't seem to change anything, does that mean it's not getting that far? Not connecting?

JScott

@Shenanigans Studio sorry, as you can see I can't even figure out how to send data yet! But I followed your posts on the subject, it's helped me so far. Looks like you're doing some cool stuff with this feature.

jonmulcahy

@Shenanigans Studio said:
Anyone have a clue as to what the table limit for sql tables is in gamesalad? I'm using my own server for cross platform highscores and I need to Get Table when the person begins playing so that I can check to make sure their username is unique. However I'm worried that if I have 50k,100k,500k,1Mil users(rows in my table) then gamesalad wont be able to import the table. Or perhaps their a work around to check to unique usernames?

Search on here for uuid. I have a post about generating a unique identifier which I use to make sure everything is unique. Only downside is if you delete the game and recreate, you get a new UID

jonmulcahy

@JScott said:
Thanks @jonmulcahy . I've uncommented lines 43 and 44. I also noticed line 12 was commented?

But, uncommenting those, didn't seem to change anything, does that mean it's not getting that far?

That would be my assumption. Who are you using as a web host? I use site5 for all my needs.

Check your apache logs, which will be at the server level, probably in your websites cpanel or whatever they use. You should see incoming traffic from your phone.

They might have a php error log too, in case it was a php problem.

Manto

@JScott

Tested your code on a local php 5.6.28 server and it worked. What version of php are you using? If you are using PHP 7, the code may not work because mysql_connect is removed. You can check the version by for example adding:
echo 'Current PHP version: ' . phpversion();
to your php file and opening the page in browser.

Manto

@Shenanigans Studio said:
Anyone have a clue as to what the table limit for sql tables is in gamesalad? I'm using my own server for cross platform highscores and I need to Get Table when the person begins playing so that I can check to make sure their username is unique. However I'm worried that if I have 50k,100k,500k,1Mil users(rows in my table) then gamesalad wont be able to import the table. Or perhaps their a work around to check to unique usernames?

Why not let the database check if the username is unique? Send the username using the get table behavior and let the server only return whether the username is available or not. You should be able to send data to server in get table behavior if you add the username to the url, for example like this:

http://someserver.com/checkusername.php?username=manto

Then in the php file use $_GET["username"] to access username and a sql query to check if it's unique.

JScott

@Manto thanks, looks like the server is using PHP 5.4.45
@jonmulcahy I am using ifastnet for a server, which I'm sure is not the best, but I paid for the super awesome deluxe version because I thought that might help. it didn't.
So, could be them, but seems weird that I can get data, but not send?
The cpanel error logs show nothing there. I can see traffic, don't know if that means anything?

Manto

@JScott said:
@Manto thanks, looks like the server is using PHP 5.4.45
@jonmulcahy I am using ifastnet for a server, which I'm sure is not the best, but I paid for the super awesome deluxe version because I thought that might help. it didn't.
So, could be them, but seems weird that I can get data, but not send?
The cpanel error logs show nothing there. I can see traffic, don't know if that means anything?

Are those request in the image clickable? Can you find more information about what data is sent to the server?

JScott

OK, some progress (I think). I now have a json.txt and fullArray.txt in my server folder. But they are both empty!

Shenanigans Studio

Thanks for all the help guys! I'm having trouble searching the array. I've spent quite a bit more hours on trying to figure this out than I'd like to admit. for some reason in_array and array_search aren't giving an outout for me. Ive checked and $rows is an array and it does contain the information I need and the $_GET function is running fine(can see in code).

//find user
$user = $_GET[username];
echo in_array($user, $rows);
echo array_search($user, $rows);
echo 'Testing Array:' . $rows[3]['username'];
echo 'break' . 'Testing $_GET' . $user;
echo 'break' . '$rows is array?' . gettype($rows);

Here's the output link
http://www.forgedhero.com/nameChecker.php?username=Jake

Manto

@Shenanigans Studio said:
Thanks for all the help guys! I'm having trouble searching the array. I've spent quite a bit more hours on trying to figure this out than I'd like to admit. for some reason in_array and array_search aren't giving an outout for me. Ive checked and $rows is an array and it does contain the information I need and the $_GET function is running fine(can see in code).

//find user
$user = $_GET[username];
echo in_array($user, $rows);
echo array_search($user, $rows);
echo 'Testing Array:' . $rows[3]['username'];
echo 'break' . 'Testing $_GET' . $user;
echo 'break' . '$rows is array?' . gettype($rows);

Here's the output link
http://www.forgedhero.com/nameChecker.php?username=Jake

So your array contains all usernames?

If you just want to check if the username exists, it's better to use sql query (databases are good at searching), e.g:

select 1
from table
where username = Jake;

Shenanigans Studio

@Manto hmm I'm getting a HTTP 500error, could you check my syntax please:

$query = mysql_query("SELECT * FROM ".$tableName WHERE username = $_GET[username]);

Manto

@Shenanigans Studio said:
@Manto hmm I'm getting a HTTP 500error, could you check my syntax please:

$query = mysql_query("SELECT * FROM ".$tableName WHERE username = $_GET[username]);

The part between $tableName and $_GET should be string:

$query = mysql_query("SELECT * FROM ".$tableName." WHERE username = ".$_GET[username]);

Shenanigans Studio

@Manto Well that takes care of the error, the page runs now, but unfortunately I'm getting the same problem as I was when trying in_array and array_search. when add ?username=Jake or any other username I know is in my array the new line of code selects 0 rows. And I'm certain Jake is in my array because I ran echo $rows[4]['username'] and it returned "Jake". Here is my code in its entirety if that helps:

// connect to SQL
include_once 'php/functions.php';

$port = '3306';
$link = @mysql_connect('143.95.234.90', 'forgedhe_wp217', '--------');

// database connection strings. change these to your DB and Table names.
$dbName = "forgedhe_leaderboard";
$tableName = "leaderboard";

if (!$link) {
    exit('Error: Could not connect to MySQL server!');
}

// connect to the table
mysql_select_db($dbName)or die("cannot select DB");

// lets prepare some files to capture what is going on.
$incomingJson = 'json.txt';
//$fullArray = 'fullArray.txt';  // needed if you enable the debugging secton below
$sqlErrorLog = "sqlErrors.txt";

// initialize the string with a blank value
$string = "";

// start GET data
if ($_SERVER['REQUEST_METHOD'] === 'GET') {

    // initialize the JSON body variable
    $jsonBody="";

    // get table contents
    $query = mysql_query("SELECT * FROM ".$tableName." WHERE username = ".$_GET[username]);

    // construct an array to hold the data we pull from mySQL
    $rows = array();

    // loop through the table and drop the data into the array
    while($row = mysql_fetch_assoc($query)) {
        $rows[] = $row;    
    }

//find user
$user = $_GET[username];
echo in_array($user, $rows);
echo array_search($user, $rows);
echo 'Testing Array:' . $rows[3]['username'];
echo "
" . 'Testing $_GET' . $user;
echo "
" . '$rows is array?' . gettype($rows);

} // end of get


// close the SQL connection
mysql_close($link);

EDIT: all players have a unique ID and if I alter code to "WHERE PlayerID = 3" then it outputs a row(the player with ID 3). So I'm assuming my problem is on the sql end. Do I need to set up something in my sql structure perhaps? I can select any rows by any other values(integers), its just the text attributes Im having trouble selecting.

Manto

EDIT: all players have a unique ID and if I alter code to "WHERE PlayerID = 3" then it outputs a row(the player with ID 3). So I'm assuming my problem is on the sql end. Do I need to set up something in my sql structure perhaps?

Instead of select * use select 1. Then it'll output "1" if it exists and "" if it doesn't.

Manto

@Shenanigans Studio said:
// get table contents
$query = mysql_query("SELECT * FROM ".$tableName." WHERE username = ".$_GET[username]);

Actually $_GET[username] should be $_GET["username"]