关于回复json字符串的PHP文件的问题

Question

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 1

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!