Retrieving records from remote MySQL using PHP

[Brad]

Big php :sign0104: here.... I have a remote db that I insert records from my phone using server side php scripts which is fairly straightforward. I need to retrieve them now and I was wondering if the results in the code below are stored in an array or something else and how do I retrieve them?

$result = mysql_query("SELECT * FROM Table");

 

[warwound]

Hi.

Your $results is 'technically' a resource.

PHP: mysql_query - Manual

You can process $result with code such as:

$result = mysql_query("SELECT * FROM Table") or die("An error occurred: ".mysql_error());

while($row=mysql_fetch_assoc($result)){
   // this will iterate through each row in $result
   // $row will be an associative array with keynames from your database table column names
   // so if your have SELECTed columns with names 'phonenumber' and 'name' you can get the values:

   $phonenumber=$row["phonenumber"];
   $name=$row["name"];

   //   more code

}

If you simply want to return all database results to your B4A code as a JSON string you can do this:

$result = mysql_query("SELECT * FROM Table") or die("An error occurred: ".mysql_error());

$json=array();

while($row=mysql_fetch_assoc($result)){
   $json[]=$row;

}
header('Content-type: application/json; charset=UTF-8');
echo json_encode($json);

That does assume though that you are using version 5.2.0 or later of PHP:

PHP: json_encode - Manual

Martin.

 

[Brad]

I'm using 5.2.

Great! Thanks for the help Martin.