How to insert datas on a remote database?

Solar2D Network
#1

How could I insert datas on a remote db without downloading the file and uploading it again?

thank you,

Andrea [import]uid: 63063 topic_id: 34578 reply_id: 334578[/import]

#2

I guess you use a php script on your server?

Here’s a sample how I update a highscore for my Trivia game:

[code]

<?php
$host = "localhost"; $user = "insert\_your\_username"; $pass = "your\_password"; $database = "ypour\_database\_name"; $linkID = mysql\_connect($host, $user, $pass) or die("Could not connect to host."); mysql\_select\_db($database, $linkID) or die("Could not find database."); $Name = $\_GET["Name"]; $Score = $\_GET["Score"]; $Device = $\_GET["Device"]; if ($Name == "" or $Score == "" or $Device == "") { die("Error"); } $sql = "SELECT \* FROM Trivia WHERE Name = '$Name' AND Device='$Device'"; $resultID = mysql\_query($sql, $linkID) or die("Data not found?."); $num\_rows = mysql\_num\_rows($resultID); if ($num\_rows \<\> "0") { $sql = "UPDATE Trivia SET Score = '$Score' WHERE Name ='$Name' AND Device = '$Device'"; $resultID = mysql\_query($sql, $linkID) or die("Data1 not found."); echo "Score updated!"; }else{ $sql = "SELECT \* FROM Trivia WHERE Name ='$Name'"; $resultID = mysql\_query($sql, $linkID) or die("Data2 not found."); $num\_rows = mysql\_num\_rows($resultID); if ($num\_rows \<\> "0") { echo "Username already exists in scoreboard, try changing your username"; }else{ echo "Score updated!"; $sql ="INSERT INTO Trivia (Name,Score,Device) VALUES ('$Name','$Score','$Device')"; $resultID = mysql\_query($sql, $linkID) or die("Data3 not found."); } } ?\> [/code] [import]uid: 50459 topic\_id: 34578 reply\_id: 137500[/import]
#3

Thank you! That’s a good idea!
But using this method I should open an url from my app using system.openURL("http://www.mywebsite.com/index.php?Name=\*\*\*&Score=\*\*\*&Device=\*\*\*") which opens a page in the browser…and if I want to open an url without displaying the browser page how could I do that?

Thanks a lot,

Andrea

[import]uid: 63063 topic_id: 34578 reply_id: 137507[/import]

#4

Look at the network.request() API call. Also see this blog post:

http://omnigeek.robmiracle.com/2012/04/15/using-corona-sdk-with-rest-api-services/ [import]uid: 199310 topic_id: 34578 reply_id: 137527[/import]

#5

Thank you rob! Your blog helped me a lot! [import]uid: 63063 topic_id: 34578 reply_id: 137605[/import]

#6

I guess you use a php script on your server?

Here’s a sample how I update a highscore for my Trivia game:

[code]

<?php
$host = "localhost"; $user = "insert\_your\_username"; $pass = "your\_password"; $database = "ypour\_database\_name"; $linkID = mysql\_connect($host, $user, $pass) or die("Could not connect to host."); mysql\_select\_db($database, $linkID) or die("Could not find database."); $Name = $\_GET["Name"]; $Score = $\_GET["Score"]; $Device = $\_GET["Device"]; if ($Name == "" or $Score == "" or $Device == "") { die("Error"); } $sql = "SELECT \* FROM Trivia WHERE Name = '$Name' AND Device='$Device'"; $resultID = mysql\_query($sql, $linkID) or die("Data not found?."); $num\_rows = mysql\_num\_rows($resultID); if ($num\_rows \<\> "0") { $sql = "UPDATE Trivia SET Score = '$Score' WHERE Name ='$Name' AND Device = '$Device'"; $resultID = mysql\_query($sql, $linkID) or die("Data1 not found."); echo "Score updated!"; }else{ $sql = "SELECT \* FROM Trivia WHERE Name ='$Name'"; $resultID = mysql\_query($sql, $linkID) or die("Data2 not found."); $num\_rows = mysql\_num\_rows($resultID); if ($num\_rows \<\> "0") { echo "Username already exists in scoreboard, try changing your username"; }else{ echo "Score updated!"; $sql ="INSERT INTO Trivia (Name,Score,Device) VALUES ('$Name','$Score','$Device')"; $resultID = mysql\_query($sql, $linkID) or die("Data3 not found."); } } ?\> [/code] [import]uid: 50459 topic\_id: 34578 reply\_id: 137500[/import]
#7

Thank you! That’s a good idea!
But using this method I should open an url from my app using system.openURL("http://www.mywebsite.com/index.php?Name=\*\*\*&Score=\*\*\*&Device=\*\*\*") which opens a page in the browser…and if I want to open an url without displaying the browser page how could I do that?

Thanks a lot,

Andrea

[import]uid: 63063 topic_id: 34578 reply_id: 137507[/import]

#8

Look at the network.request() API call. Also see this blog post:

http://omnigeek.robmiracle.com/2012/04/15/using-corona-sdk-with-rest-api-services/ [import]uid: 199310 topic_id: 34578 reply_id: 137527[/import]

#9

Thank you rob! Your blog helped me a lot! [import]uid: 63063 topic_id: 34578 reply_id: 137605[/import]

#10

Hi Rob;

I read your tutorial thanks, however I’m still having trouble grasping how I would update a value - say the user’s score - in the remote database. Would it be using the network.request or could we simply set the URL as the directory for the system.pathForFile and open something like a io.open read write sort of thing?

I’m so confused…

Does the GET also update the info on the remote database? 

Wow, I need more coffee!

#11

You would write a PHP script, maybe called “setscore.php” that takes the parameters you need to pass (playername, score, etc.).  That script will either read them as a GET value from the URL or a POST value, depending on your preferences.  I would assume your database would probably use playername or something similar as a key and once you have the variables in PHP do an SQL query like

$query = “REPLACE scores (playername, score) VALUES (” . $playername . ", " . $score . “)”;

or something like that and then have mySQL run the query on $query.

#12

Ok thanks for your time Rob I appreciate it…

I think I’m starting to get it now, so the PHP script will take what it needs from something like:

http://servername.com/setscore.php?deviceid="…deviceID…"&score="…score --if I were using device ID as a key.

and the PHP script will do the updating of the values…

Too the PHP docs…  :ph34r:

Wait… is there a sample of a PHP script that has the update function in it?

I will check around some more.

Thank you again.

#13

LOL wow I’m dumb today… yeah the script up there ^^

My apologies for the dumbness!

:rolleyes:

#14

Hi Rob;

I read your tutorial thanks, however I’m still having trouble grasping how I would update a value - say the user’s score - in the remote database. Would it be using the network.request or could we simply set the URL as the directory for the system.pathForFile and open something like a io.open read write sort of thing?

I’m so confused…

Does the GET also update the info on the remote database? 

Wow, I need more coffee!

#15

You would write a PHP script, maybe called “setscore.php” that takes the parameters you need to pass (playername, score, etc.).  That script will either read them as a GET value from the URL or a POST value, depending on your preferences.  I would assume your database would probably use playername or something similar as a key and once you have the variables in PHP do an SQL query like

$query = “REPLACE scores (playername, score) VALUES (” . $playername . ", " . $score . “)”;

or something like that and then have mySQL run the query on $query.

#16

Ok thanks for your time Rob I appreciate it…

I think I’m starting to get it now, so the PHP script will take what it needs from something like:

http://servername.com/setscore.php?deviceid="…deviceID…"&score="…score --if I were using device ID as a key.

and the PHP script will do the updating of the values…

Too the PHP docs…  :ph34r:

Wait… is there a sample of a PHP script that has the update function in it?

I will check around some more.

Thank you again.

#17

LOL wow I’m dumb today… yeah the script up there ^^

My apologies for the dumbness!

:rolleyes: