MySql tutorial with read and write

[TomDuncan]

Hi All,
Has anyone modified the MySql tutorial so that you can both read and post data to an external database?

Tom :sign0104:

 

[Jack Cole]

Hi Tom,

I think the tutorial does show how to read data. Here is some code I use to post to a database.

ExecuteRemoteQuery("INSERT INTO topscores (game,level,player,score,version) VALUES ('MINESWEEPER', '" & level.Text & "', '" & player.Text & "', '" & seconds.TEXT & "', '7')",4)

And code for updating a record:

executeremotequery("UPDATE topscores SET SCORE='" & db.TOTALS.solved & "', SCORE2='" & db.TOTALS.TOTAL &"' WHERE GAME='SHUFFLE' AND PLAYER='" & db.PLAYERNAME & "' AND UNIQUEID='" & db.UNIQUEID & "'",2)

There is also a tutorial that shows how to post data using URL parameters with a php script: Tutorial - Online Scoreboard.

 

[TomDuncan]

To execute the post query won't I need to change the php code?
How is this done.
Tom

 

[Jack Cole]

No. You can do insert and update queries with the existing PHP code.

 

[TomDuncan]

Thanks will test it on my localhost.

 

[TomDuncan]

works like a charm, thanks for that.
Can Update and Insert now.
I did have to give the IP address of my My SQL server on my local network.
Happy lad.

Tom

 

[TomDuncan]

Just had a thought.
If another user made changes to the database how can any other applications using the same database get notification that changes had been made?

Tom

 

[Erel]

If another user made changes to the database how can any other applications using the same database get notification that changes had been made?

You will need to implement such a system.
You can, for example, add a time field to each record and then query the database to find all new records.

A more complicated system may use the push notifications service to update all devices when the database is updated.

 

[tcpip]

No. You can do insert and update queries with the existing PHP code.

I would like to ask a question about getting info from database.

I`ve use the tutorial and successfully i retrieve information from the database. Now i have problem in insert into query.

What do you mean we can do insert and update queries with the existing PHP code??
The code in php file that i use to make a select query is look like this:

$databasehost = "xxxx";
$databasename = "xxxx";
$databaseusername ="xxxx";
$databasepassword = "xxxx";

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());
$query = "Select * from Users";
$sth = mysql_query($query);

if (mysql_errno()) {
header("HTTP/1.1 500 Internal Server Error");
echo $query.'\n';
echo mysql_error();
}
else
{
$rows = array();
while($r = mysql_fetch_assoc($sth)) {
$rows[] = $r;


}
print json_encode($rows);


thanks

 

[rfresh]

Just had a thought.
If another user made changes to the database how can any other applications using the same database get notification that changes had been made?

Tom

You would have to have your app check the database every x mins to see if there have been any updates.

 

[netchicken]

Could you use the Trigger?

MySQL :: MySQL 5.0 Reference Manual :: 18.3 Using Triggers

 

[Jack Cole]

I mean that you can use B4A to execute the remote queries. You are not limited to using only SELECT statements. See my examples above of an insert and update query. Your PHP code that you posted here should be able to connect to your mysql database and execute those types of functions in addition to select statements.

I would like to ask a question about getting info from database.

I`ve use the tutorial and successfully i retrieve information from the database. Now i have problem in insert into query.

What do you mean we can do insert and update queries with the existing PHP code??
The code in php file that i use to make a select query is look like this:

$databasehost = "xxxx";
$databasename = "xxxx";
$databaseusername ="xxxx";
$databasepassword = "xxxx";

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());
$query = "Select * from Users";
$sth = mysql_query($query);

if (mysql_errno()) {
header("HTTP/1.1 500 Internal Server Error");
echo $query.'\n';
echo mysql_error();
}
else
{
$rows = array();
while($r = mysql_fetch_assoc($sth)) {
$rows[] = $r;


}
print json_encode($rows);


thanks