boehmP boehmP - 3 months ago 8
JSON Question

Issue regarding PHP file that echoes json string

I have troubles creating a json string to use in an android app for a few days now. I followed many tutorials, but it never was exactly what i needed and I never understood this JSON stuff, although I tried to. So I wasn't able to modify it the way I wanted it.

First I tried this: http://programmerguru.com/android-tutorial/android-restful-webservice-tutorial-how-to-create-restful-webservice-in-java-part-2/

But this was with Logins, but I just want to read rows and display the data.

Maybe someone can help me and explain to me what I have to do to make it work.
The file should fetch a row from a table in my MySQL database that matches the city and country params and return it as json string.

UPDATE:
I now use the PDO_MYSQL extension and have new .php files.
But when I try to execute the file from the command line i get following error:

Fatal error: Call to undefined method DB_CONNECT_PDO::prepare() in get_data_pdo.php on line 15

My files:

db_connect_pdo.php:

class DB_CONNECT_PDO {

// constructor
function __construct() {
// connecting to database
$this->connect();
}

// destructor
function __destruct() {
// closing db connection
$this->close();
}

/**
* Function to connect with database
*/
function connect() {
try{
$db = new PDO('mysql:host=127.0.0.1;dbname=mydb;charset=utf8', 'myuser', 'mypass');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}catch(PDOException $ex) {
echo "An Error occured!";
echo($ex->getMessage());
}
// returing connection cursor
return $db;
}

/**
* Function to close db connection
*/
function close() {
// closing db connection
mysqli_close();
}


}

get_data_pdo.php:

$city = 'Berlin';
$country = 'Germany';

require_once('db_connect_pdo.php');

try{
$db = new DB_CONNECT_PDO();
$con = $db->connect();

$stmt = $db->prepare("SELECT * FROM dbtable WHERE city=? AND country=?");
$stmt->execute(array($city, $country));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

if(isset($rows)){
echo json_encode($rows);
}else{
echo 'failure';
}
}catch(PDOException $ex) {
echo "An Error occured!";
echo($ex->getMessage());
}


I followed this tutorial for the connection and sql query: http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers#External_links

Answer

Your $con variable is undefined in getData.php.

$sql = ...

$db = new DB_CONNECT();
$con = $db->connect();

$result = ...

Important: mysql_connect is deprecated since php 5.5 and removed in 7.0. I strongly advise against using it. You should look up PDO_MYSQL extension. More about it here: http://php.net/manual/en/function.mysql-connect.php

You should also read about sql injections. Using unescaped user input in queries is a huge security flaw. PDO offers prepared statements and parameter binding to deal with sql injections.

Edit: As @tadman pointed out, there is also the issue of mixing mysql_* and mysqli_*. Connection is established using the mysql_* and mysqli_* is used to execte the query and fetch the results.

Edit 2: Change $stmt = $db->prepare(...); to $stmt = $con->prepare(...);

$db is an instance of DB_CONNECT_PDO class, you can use it to prepare pdo statements, instead you must use PDO object created in DB_CONNECT_PDO::connect.

Best of luck!

Comments