php, MySQL and B4A together clash. I cannot tell the source of the error.

[Mahares]

I am getting the 'Query was empty' error in the web page. Of course the error in B4A that follows is: Lastexception, Runtime exception, JSON array expected in this line: lstItems = parser.NextArray
I am not sure if the problem is in the B4A code or in the following php script. My knowledge in php is almost non existent and JSON means nothing to me but a mispelled first name. This is my first exposure to php and MySQL together. I read a ton of information in the forums. I appreciate the help from someone with patience.
BELOW is the php CODE:
<?
//code to connect to the remote database web site MySQL database
$databasehost = "production.db.925091.hostedresource.com";
$databasename = "production";
$databaseusername ="production";
$databasepassword = xtsytehnZ%";

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());

$query = $_GET["query"];
$sth = mysql_query($query);

if (mysql_errno()) {
echo mysql_error();
}
else
{
$rows = array();
while($r = mysql_fetch_assoc($sth)) {
$rows[] = $r;
}
print json_encode($rows);
}
?>

 

[mc73]

How do you pass the 'query' variable to your php?

 

[Mahares]

Here is the complete project zipped. The php file is exactly what I posted on my previous post. Thank you for sifting through it.

 

[Erel]

Your PHP script is wrong (doesn't match the B4A code). You should use the script from the MySQL tutorial.

 

[warwound]

Try this:

<?php
//code to connect to the remote database web site MySQL database
$databasehost = "production.?????.com";
$databasename = "production";
$databaseusername = "production";
$databasepassword = "?????"; //   added a quote " to the start of the password string

mysql_connect($databasehost, $databaseusername, $databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());

$query = $_GET["query"]; //   very dangerous, leaves your database open to hackers!
$result = mysql_query($query);

$output = array();

if ($result == false) {
   $output['query'] = $query;
   $output['error'] = mysql_error();
} else {
   while ($row = mysql_fetch_assoc($result)) {
      $output[] = $row; //   add the row to the output array
   }
}

ob_start('ob_gzhandler'); //   comment/uncomment to enable gzip compression of JSON output
header('Cache-Control: no-cache, must-revalidate');
header('Content-type: application/json; charset=UTF-8');
echo utf8_encode(json_encode($output));
?>

If the script fails it will return the query string and MySQL error to enable you to debug the error.

Is the query in the URL properly encoded?

You ought to remove the database login details from your post by the way, and think seriously whether anyone could call your script with a malicious query - anyone could insert data, drop a table etc.

Martin.