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

scottharrrules43 · November 2014

After all that digging around it just did not like the the space in the $dbname.

scottharrrules43 · November 2014

the link looks good (http://scottrules44.comule.com/sgctest.php) but still won't send or receive.

scottharrrules43 · November 2014

@scottharrrules43 said:
tried your thing you made and the receive worked but the send did not work.
Any ideas?

?

jonmulcahy · November 2014

@scottharrrules43 said:
?

No ideas sorry. You'll have to start doing some code level debugging to see where it's going wrong.

Id start with outputting the Json file that you are trying to send and compare that to what is coming in. It should match the same format

scottharrrules43 · November 2014

does this mean anything?

jonmulcahy · November 2014

you are assigning the query to a variable.

you are selecting the first 30 items from the asyncTest table. This is a great resource on how to work with it:

http://www.w3schools.com/php/php_mysql_intro.asp

scottharrrules43 · November 2014

The image above has nothing to do with sending. (Duh scott)
i did study the website for a hour.
thanks for helping out by the way.

edit: i saw a guy have a same problems the webhost i am using tried fixing it the way he said but still did not work. http://www.000webhost.com/forum/web-programming/36917-cant-insert-mysql.html

(here is my code)
// construct SQL statement $sql = ("INSERT INTO users (playerID, playerName, playerStats) VALUES ('.$playerID.','.$playerName.','.$playerStats.')");

scottharrrules43 · November 2014

@scottharrrules43 said:
i saw a guy have a same problems the webhost i am using tried fixing it the way he said but still did not work. http://www.000webhost.com/forum/web-programming/36917-cant-insert-mysql.html

(here is my code)
// construct SQL statement $sql = ("INSERT INTO users (playerID, playerName, playerStats) VALUES ('.$playerID.','.$playerName.','.$playerStats.')");

any ideas?

Fajlajp · November 2014

Is there anyway to just retrive one row(and every column) instead of an whole table?

jonmulcahy · November 2014

@Fajlajp said:
Is there anyway to just retrive one row(and every column) instead of an whole table?

AFAIK right now you can only work with full tables in GS, but I remember GS saying they would be adding individual row and column checks.

On the server side you can do anything you want with it.

jonmulcahy · November 2014

@scottharrrules43 said:
any ideas?

did you check the sqlError.txt file in the root of the folder to see what that captured? The code looks fine, it seems like your web host is just locked down

colander · November 2014

Have you assigned the table id number to "users" from your .gameproj xml file?

For Example;

// this is the footer of the JSON return. Again it will have to be adjusted to match whatever your app is expecting.

    $jsonFooter='],"users":"id######","Children":[]}]}';

scottharrrules43 · November 2014

@jonmulcahy said:
did you check the sqlError.txt file in the root of the folder to see what that captured? The code looks fine, it seems like your web host is just locked down

There is no sqlError in the root. When I hit send on your template it put the Json.text in the . I don't think the host like the construct sql statement.

scottharrrules43 · November 2014

Switch to a different host and IT WORKS. Can not wait to use this in future games. (maybe some old ones hint hint.)
Thanks for all your help @jonmulcahy‌ .

Hopscotch · November 2014

@Fajlajp said:
Is there anyway to just retrive one row(and every column) instead of an whole table?

If you need to work with individual rows, it is best to have a separate table to handle the exchange of data with your server, then populating the appropriate row in the main table once received.

jonmulcahy · November 2014

@scottharrrules43 said:
Switch to a different host and IT WORKS. Can not wait to use this in future games. (maybe some old ones hint hint.)
Thanks for all your help jonmulcahy‌ .

No prob.

Gotta love those hosts who prevent you from working

scottharrrules43 · November 2014

Hoping to get a game working with this up soon.
(before Christmas)

scottharrrules43 · December 2014

sorry to bother again but i have been look at this for a week i keep getting error writing to database. So i have been tweaking and rewriting but keep the same problem.

`//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. 
        $email = $arrayPieces[1];
        $password =$arrayPieces[2];
        $coins = $arrayPieces[3];
                    $high_score = $arrayPieces[4];
                    $slot_shield = $arrayPieces[5];
                    $red = $arrayPieces[6];
                    $green = $arrayPieces[7];
                    $black = $arrayPieces[8];
                    $white = $arrayPieces[9];
                    $gold = $arrayPieces[10];
                    $level = $arrayPieces[11];
                    $points = $arrayPieces[12];
                    $points_goal = $arrayPieces[13];
                    $coin_reward = $arrayPieces[14];
                    $level_cost = $arrayPieces[15];
                    $index = $arrayPieces[16];


        // construct SQL statement
        $sql="INSERT INTO".$tableName."(email, password, coins, high_score, slot_shield, red, green, black, white, gold, level, points, points_goal, coin_reward, level_cost, index)VALUES('.$email.', '.$password.', '.$coins.', '.$high_score.', '.$slot_shield.', '.$red.', '.$green.', '.$black.', '.$white.', '.$gold.', '.$level.', '.$points.', '.$points_goal.', '.$coin_reward.', '.$level_cost.', '.$index.')";
        // 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 database\n";
            file_put_contents($sqlErrorLog, $sqlError, FILE_APPEND);
        }

        $i++;
    }
} // end of POST`

scottharrrules43 · December 2014

?

colander · December 2014

Why have you got periods before and after your arrays? For example you have '.$email.' in mine it is just '$email' the same as when I declared it $email = $arrayPieces[1].

scottharrrules43 · December 2014

@colander said:
Why have you got periods before and after your arrays? For example you have '.$email.' in mine it is just '$email' the same as when I declared it $email = $arrayPieces[1].

I will try that

scottharrrules43 · December 2014

i tried that i get error writing to database, @colander‌

colander · December 2014

This is what I had working I can't remember how much I changed to from the original if any. You seem to have the Get Data code with periods in the Post Data code without periods. Have a look and see if you can spot anything else that may be different.

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

$port = '3306';
$link = @mysql_connect(':/Applications/MAMP/tmp/mysql/mysql.sock', 'root', 'root');

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

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 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($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 database\n";
            file_put_contents($sqlErrorLog, $sqlError, FILE_APPEND);
        }

        $i++;
    }
} // end of POST

jonmulcahy · December 2014

@scottharrrules43 said:
i tried that i get error writing to database, colander‌

what's the exact error you are getting?

scottharrrules43 · December 2014

@jonmulcahy said:
what's the exact error you are getting?

jonmulcahy · December 2014

check the error log in the directory, it should write out the error.

jonmulcahy · December 2014

actually, i wrote new code in my project to capture SQL errors. I'll post the code in a bit, i have to dig it up.

jonmulcahy · December 2014

@scottharrrules43‌ replace this section:

    // 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 database\n";
        file_put_contents($sqlErrorLog, $sqlError, FILE_APPEND);
    }

with this:

            // insert SQL statement
            $result=mysql_query($sql);

            // catch any errors
            if($result){
                // if successful do nothing for now.
            }

            else {

                $message  = 'Invalid query: ' . mysql_error() . "\n";
                $message .= 'Whole query: ' . $query;
                file_put_contents($sqlErrorLog, $message, FILE_APPEND);
                die($message);
            }

and up at the top where you define your log files, add this line so it knows where to put the files. I have it right after I define the json.txt file:

$sqlErrorLog = "sqlErrors.txt";

jonmulcahy · December 2014

after making those changes reproduce the error and post the contents of sqlErrors.txt

scottharrrules43 · December 2014

I will thanks

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

Comments