Android Tutorial For beginners: How to communicate with a server using httputils2 (Part 2: MySql)

Small update: I use Download2 instead of Poststring now. It handles some things for us like removing illegal space, etc. Otherwise some examples wont work as decribed.


Part 1: http://www.b4x.com/android/forum/th...h-a-server-using-httputils2-part-1-php.42442/

What we've got (so far)

1. Calling a php script and send parameters
2. Getting the parameters in our php script and sending parameters back
3. Receiving the answer from the script

Now we're ready to do some MySql stuff

First we must create a table. Assuming we have a hosted website wth php and MySql there is a admin tool (see documentation of your hoster) to create databases and tables.

For my example I have created a table called "Persons" with 2 culumns: "name" and "age".

So guess what parameters we want to send to the script?

Sending "name" and "age"

1. Edit your b4a-app:

'Send a GET request
  Dim job2 as httpjob
  job2.Initialize("Job2", Me)
  job2.Download2("http://www.yourdomain.com/myscript.php", _ArrayAsString("name", "klaus", "age", "30"))

Later we have some more functions and each has an own job. So I change the abstract "job2" to a more precise name. Here we want to insert new persons:

'Send a GET request
  Dim InsertPerson as httpjob
  InsertPerson.Initialize("InsertP", Me)
  InsertPerson.download2("http://www.yourdomain.com/myscript.php", _arrayAsString("name", "klaus", "age", "30"))

Take a look at "InsertP". This is the jobname. When getting back to "jobdone" we can check, from which job we are back from. You can use any name you want to.

Sub JobDone (Job As HttpJob)
  Log("JobName = " & Job.JobName & ", Success = " & Job.Success)
  If Job.Success = True Then
      Select Job.JobName
        Case "InsertP"
            'print the result to the logs
            Log(Job.GetString)
        Case "OtherFunction"
            'Do some things
      End Select
  Else
      Log("Error: " & Job.ErrorMessage)
      ToastMessageShow("Error: " & Job.ErrorMessage, True)
  End If
  Job.Release
End Sub

2. Edit your script

<?

$name = $_GET["name"];
$age = $_GET["age"];

print ("Your name is $name and your are $age years old");

?>

In the log (assuming you use Erels example) the string "Your name is...." will be seen in the log.

Connecting to MySql

Edit your script again. First we have to connect to our database:

<?

$host = "dv12345678.db.1and1.com";
$db = "db567890";
$user = "dbo5678901";
$pw = "password";

$con = mysql_connect($host,$user,$pw) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
mysql_query("SET CHARACTER SET utf8");
mysql_query("SET NAMES 'utf8'");

$name = $_GET["name"];
$age = $_GET["age"];

$res = mysql_query("Insert into Persons (name, age) VALUES ('$name', $age)");

print ("I have inserted $name and $age in my table");

?>

As you see, we need a user and password to connect to our database. For safety reasons never put this in your script directly (we will come back to this later). This is just for our example to keep things simple. I did not check for errors so far, too.

Don't forget our browser

As I've said, there is no difference calling the script from our app or directly from a browser. It is a great help to use it with a browser to test it.

www.yourdomain.com/myscript.php?name=Klaus&age=30

Getting back contents of your table

Now we want to get back the number of rows the table has. For the moment we only send back ONE string to keep our example simple. In part 3 I will show how to send back more than one string/parameter (array).

Edit your script again:

<?

$host = "dv12345678.db.1and1.com";
$db = "db567890";
$user = "dbo5678901";
$pw = "password";

$con = mysql_connect($host,$user,$pw) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
mysql_query("SET CHARACTER SET utf8");
mysql_query("SET NAMES 'utf8'");

$name = $_GET["name"];
$age = $_GET["age"];

$q = mysql_query("Insert into Persons (name, age) VALUES ('$name', $age)");

$q = mysql_query("SELECT * FROM Persons");
$count = mysql_num_rows($q);

print ("I have inserted $name and $age in my table and now there are $count rows in the table");

?>

This one inserts another row and sends back what was inserted and how many rows we have after this in our table.

Still missing: Error handling.

In part 3 we will select some more culumns and send them back as an array of parameters (like we send them). JSON_ENCODE will be a great help then.

 

[nwhitfield]

If anyone would find it helpful, I could add some equivalent code using the mysqli version of the commands too?

 

[Myr0n]

Thank you so much, this example is very easy to follow.

 

[rwblinn]

@nwhitfield: Yes, as mysqli will be supported in the future it would be helpful indeed. Appreciated.

@klaus: Thanks a lot. Nice write up.

 

[aeric]

Hi Klaus, my app unable to post the values using PostString but Download2 get the work done.

' This line is not working
myjob.PostString("http://www.mydomain.com/android/myscript.php","name=John&age=21")

' This line is working fine
myjob.Download2("http://www.mydomain.com/android/myscript.php", _
Array As String("name","Candy","age","18"))

 

[daniedb]

Need some Advice.
Working through the example

  job2.Download2("http://medomain.co.za/myscript.php", Array As String("name", "klaus", "age", "30"))

Return
I have inserted klaus and 30 in my table and now there are rows in the table

But it is not inserted?
I've tried PostString aswell and then return
I have inserted and in my table and now there are rows in the table

Even when using the Web Browser, It don't insert into the Table
Returns
I have inserted Klaus and 30 in my table and now there are rows in the table

Could it be some MySQL Privilages?

Thanks

 

[Gbenga Odumosu]

this is a good tutorial. well done. it helps newbie like me. can you pls tutor us on how to do same if the data in the mysql in my site contain pictures and we want to display it on list as done with ordinary test. thanks

 

[mcqueccu]

'Send a POST request
  Dim InsertPerson as httpjob
  InsertPerson.Initialize("InsertP", Me)

Please per this line in your tutorial, the comments read send post request...I want to find out if DOWNLOAD2 also sends post request like POSTSTRING

 

[KMatle]

'Send a POST request
  Dim InsertPerson as httpjob
  InsertPerson.Initialize("InsertP", Me)

Please per this line in your tutorial, the comments read send post request...I want to find out if DOWNLOAD2 also sends post request like POSTSTRING

It was a typo

POSTSTRING sends a POST request with a single string
DOWNLOAD" sends a GET request with an array containing data

If you have further questions, post it in the questions forum