Display data from Web Server (PHP&MySql)

microbox · Dec 2, 2012

Hi everyone,
I'm still a newbie in B4A, yesterday I was able to run "Online Scoreboard" on my device by admac231..thanks. This morning I tried to create my own version of program...I created student_info database with one table (students) with the following fields..
id, stud_id, fname, lname, address, contact, course
Currently I'm having trouble displaying it as I needed.
My question is how can I arrange the display of the fields in this format?
1. Ben Ventura
StudID: 12345 Add: Barbara st. Course: Bscoe Tel:3215950
This is the php script I'm using

B4X:

<?php

$myPassword = "pass";
$mysqlHostName ="127.0.0.1";
$mysqlDatabaseName = "student_info";
$mysqlUsername = "root";
$mysqlPassword = "";

if($_GET['secret']!=$myPassword){
die("Access denied...student records");
}
mysql_connect($mysqlHostName, $mysqlUsername, $mysqlPassword) or die(mysql_error());


mysql_select_db( $mysqlDatabaseName) or die(mysql_error());

if(isset($_GET['id'])){
    $id = $_GET['id'];
    $id = mysql_real_escape_string($id);
    $lname = $_GET['lname'];
    $lname = mysql_real_escape_string($lname);
    $fname = $_GET['fname'];
    $fname = mysql_real_escape_string($fname);
}

if($id == "view"){

    $result = mysql_query("SELECT * FROM students") or die(mysql_error());  
    while($row = mysql_fetch_array($result)){
    echo $row['lname']." ";
    echo $row['fname']." ";
    echo "\r\n".$row['address']."\r\n";
}
}
?>

B4A code

B4X:

Sub Process_Globals
    Dim httpC As HttpClient
End Sub

Sub Globals

    Dim cmdViewMe As Button
    cmdViewMe.Initialize("cmdViewMe")
    
   Dim lstStudents As ListView
    lstStudents.Initialize("lstStudents")
   
    Dim httpReq As HttpRequest
    Dim reader As TextReader
    
End Sub

Sub Activity_Create(FirstTime As Boolean)
    If FirstTime Then
        httpC.Initialize("httpC")
    End If
   
   
    cmdViewMe.Text = "Me"
    Activity.AddView(cmdViewMe,75%x+5dip,5dip,25%x-5dip,50dip)
      
    lstStudents.ScrollingBackgroundColor = Colors.Transparent
    Activity.AddView(lstStudents,0,55dip,100%x,100%y-65dip)
    
End Sub

Sub cmdViewMe_Click
    Dim req As HttpRequest
    req.InitializeGet("http://192.168.1.101/students.php?secret=pass&id=view")
    httpC.Execute(req, 2)
    ProgressDialogShow("Fetching your students list...")
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)
    Activity.Finish
End Sub

Sub httpC_ResponseSuccess (Response As HttpResponse, TaskId As Int)
    lstStudents.Clear
    Dim result As String
    result = Response.GetString("UTF8")
    File.WriteString(File.DirInternalCache, "students.sco",result)
    reader.Initialize(File.OpenInput(File.DirInternalCache, "students.sco"))
    Dim line As String
    Dim i As Int
    i = 0
   
   
   'line = reader.ReadLine
    Do While line <> Null
        i = i+1
        lstStudents.AddTwoLines(i&". "&line,reader.ReadLine
      )
        line = reader.ReadLine
    Loop
    reader.Close
    ProgressDialogHide
    lstStudents.SetSelection(0)
End Sub

Sub httpC_ResponseError (Response As HttpResponse, Reason As String, StatusCode As Int, TaskId As Int)
    Msgbox("Error connecting to students server"&CRLF&CRLF&Reason,"Error ("&StatusCode&")")
    ProgressDialogHide
End Sub

Thank you in advance,
microbox

microbox · Dec 3, 2012

I think I'm having trouble understanding the following codes to display it right
In B4A

B4X:

 Do While line <> Null
        i = i+1
        lstStudents.AddTwoLines(i&". "&line,reader.ReadLine
        )
        line = reader.ReadLine
    Loop

in php script (not yet all fields are included)

B4X:

if($id == "view"){

    $result = mysql_query("SELECT * FROM students") or die(mysql_error());  
    while($row = mysql_fetch_array($result)){
    echo $row['lname']." ";
    echo $row['fname']." ";
    echo "\r\n".$row['address']."\r\n";
}

Hope anyone can point out what I'm doing wrong.

/microbox

NeoTechni · Dec 3, 2012

Just have a php page output the data as comma separated values

warwound · Dec 3, 2012

You could make things more robust by output JSON data from your PHP script:

PHP:

if($id == "view"){
    $result = mysql_query("SELECT * FROM students") or die(mysql_error()); 
   $output = array();
    while($row = mysql_fetch_array($result)){
      $output[] = $row;
   }
}
ob_start('ob_gzhandler'); //   comment/uncomment to enable gzip compression on the JSON output by this script
header('Cache-Control: no-cache, must-revalidate');
header('Content-type: application/json; charset=UTF-8');
echo utf8_encode(json_encode($output));

That'll handle all possible character encoding and formatting problems and allow your script to gzip the JSON output - reductions in the size of the script output in the region of 70% are more than possible.

In your B4A code the gzipped script output is handled automatically and you'd use the JSON library to parse the script output into JSON objects or JSON arrays that are pretty easy to work with.

Martin.

microbox · Dec 8, 2012

Hello NeoTechni and warwound, sorry for the delay of getting back and thank you for your response... warwound I will try your suggestion about using JSON...

Regards,
microbox

microbox · Dec 9, 2012

I'm sorry for being a :sign0104: I know this is very basic..but I'm still stuck..I can not display the items I intend to...giving me headache :BangHead:
I know I was suggested by our fellow forum members how to..but to be honest I do not understand it much right now..I appreciate if anyone can help me out.
Just want to display in this format(I have 4 records).
1. Ventura Ben
Barbara St
2. Dakota Mina
Rizal ave
3. Sue Kim
james st
4. Samson Gary
Real st

Thank you in advance,
microbox

microbox · Dec 9, 2012

Okay...I finally got it to display it right

Now I can go 1 step forward...

microbox · Dec 9, 2012

Hi, now I'm trying to pass a value to the server manually for testing, student ID = "90290" which should only display its information back....but it's giving me the following error.

B4X:

an error has occured in sub:main_httpv_responsesuccess (java line:406)
java.lang.NullPointerException

php codes for search

B4X:

//-------------Search by studID---------------
if($id == "search"){ 
    $schoolid = $_GET['schoolid'];
 
    $result = mysql_query("SELECT * FROM students where stud_id = '$schoolid'") or die(mysql_error());  
    $output = array(); 
    while($row = mysql_fetch_array($result)){ 
        //$output[] = $row;
   echo $row['lname']."\r\n"; 
   echo $row['fname']."\r\n";
   echo $row['address']."\r\n"; 
   echo $row['stud_id']."\r\n";

        echo $row['course']."\r\n";  
   
    } 
}

B4Android codes:

B4X:

Sub CmdSearch_Click
    Dim studid As String 
   studid = "90290"
   If slectopt = 1 Then
   Msgbox("Option 1","Search")
   '---------------------------------Option 1 manual
   Dim req As HttpRequest
    req.InitializeGet("http://192.168.1.101/students.php?secret=pass&id=search&schoolid='&studid'")
    httpC.Execute(req, 2)
    ProgressDialogShow("Fetching your students list...")   
   '---------------------------------
   Else If slectopt = 2 Then
   Msgbox("Option 2" ,"Selected")
   Else
   
   End If
End Sub

What do you think I'm doing bad?

Thanks in advance,
mcrobox

microbox · Dec 9, 2012

Hello, I was able to figure it out what was the problem by using this line

B4X:

 req.InitializeGet("http://192.168.1.101/students.php?secret=pass&id=search&schoolid="&schoolid&"")

Since I'm able to search using the stud_id, it would be great if there is an image of the student displaying.
Just want to ask if it is possible to display an image from the server but using only the path where the image from the remote pc is stored? If it is not..what would be the best approach? Hope anyone can help.

/microbox

warwound · Dec 10, 2012

You could retrieve the image you want to display using another HTTP request, an alternative would be to return the image in the data that your original request recieves.

Have a Google: https://www.google.co.uk/search?q=php+base64+encode+image&ie=UTF-8&oe=UTF-8

Your PHP script could do all the processing that it's currently doing and return that data and along with that data it could return a base64 encoded string that represents the image.

That'd save you another HTTP request and the image would be available as soon as you recieve the existing data.

Search the forum for base64 encoded image to find code examples showing how to turn that base64 string back into an image.

Martin.

microbox · Dec 10, 2012

excellent...thanks for the tip!
I will try it out...

/microbox

Display data from Web Server (PHP&MySql)

microbox

Active Member

Attachments

microbox

Active Member

NeoTechni

Well-Known Member

warwound

Expert

microbox

Active Member

microbox

Active Member

Attachments

microbox

Active Member

microbox

Active Member

microbox

Active Member

warwound

Expert

microbox

Active Member