Android Question Get Data from SQL file using a GET command or similar

[Peter Lewis]

Hi all,

I am looking for some advise.

I have a Customer table in SQL and I want to exctract all the data into a txt file on a Android device, Normally this txt file should only hold up to 500 records.

With all of your help managed to post to SQL via a PHP file on the server.

I have writted a php file to extract the records on the screen but just do not know how to run the query from b4A to retrieve all the records.

I do not want to login to the sql database directly. I would prefer to go through some php conde on a server.

With other database programming I have done I would have interregated the database file of how many records and then sequentually requested each record and saved each one to the txt file on the phone.

If you can point me in the right direction, I would appreciate it.

Thank you

 

[KMatle]

If I get you right, you just want to retrieve all rows from a MySQL table via php.

"I do not want to login to the sql database directly. I would prefer to go through some php conde on a server" -> you must, even in php (otherwise anyone could access the data).

Hint: Just log the data or just take a look in debug to see what the content is...

PHP

<?php

$host = "hostname";
$user = "dbuser";
$pw = "dbpw";
$db = "dbname";

$con = mysqli_connect($host,$user,$pw) or die(mysqli_error());
mysqli_select_db($con,$db) or die(mysqli_error());
mysqli_query($con,"SET CHARACTER SET utf8");
mysqli_query($con,"SET NAMES 'utf8'");

$q = mysqli_query($con,"SELECT * FROM mytable order by xxxx ASC") or die(mysqli_error($con));
        $rows = array();
        while($r = mysqli_fetch_assoc($q)) ' = culumn name -> value
        {
            $rows[] = $r;
        }
        print json_encode($rows);

?>

B4x

Dim GetDataJob As HttpJob
    GetDataJob.Initialize("GetData", Me)
    GetDataJob.PostString(ServerAddress & "/mypath/myscript.php", "Nothing")

Note: As your script doesn't need any parameters (just a call) but the PostString method needs a parameter, just set it to any value (here: "Nothing", maybe NULL works, too, but I've no chance to try right now)

Sub JobDone(Job As HttpJob)
  
    If Job.Success Then
        Dim res As String
      
        res = Job.GetString
        Log("Back from Job:" & Job.JobName )
        Log("Response from server: " & res)
      
        Dim MyDataList As List 'Define a list for the data coming from the php script
  
        Dim parser As JSONParser 'It's a JSON formatted string
        parser.Initialize(res)
          
        MyDataList.Initialize
        MyDataList=parser.NextArray 'Convert the JSON and put it in the list

        'you can save the list to a file and load it when you need it
       
        'Each list entry has a MAP with the data (500 rows = 500 maps). Just access it like this (simple code) or use for .... each ....

        Dim MyDataMap as Map
        MyDataMap.Initialize
        For i=0 To MyDataList.Size-1
              MyDataMap=MyDataList.Get(i)
              'Next: Parse the Map (key=culumn name, value=data) and put it. Hint: Take a look at the content in debug :-)
        Next
    Else
        Log("Error: " & Job.ErrorMessage)
    End If
    Job.Release
  
End Sub

 

[Peter Lewis]

Thank you for the advise , I will try it out