php - Android/php : Using PDO to retrieve a set of results using 'LIKE' , 并解析为 JSON

Question

我正在尝试使用查询来搜索数据库，并从用户名LIKE 搜索查询的表中返回几列。即，如果我输入“M”，将检索 Max、Matthew 等姓名。但是，执行查询时不会返回任何内容。我用 try/catch 函数包围了它，它们工作正常，回显了一个我可以使用的整数，但我更希望代码实际完成了它的 Intent 。我花了很长时间来摆弄这个，首先尝试使用 MySqli，然后转向 PDO，因为网上的每个人都认为它更好。

如果有人能看出这里有什么问题，请不要犹豫，更正它!

服务器端脚本如下:

if(!empty($_POST['name'])){

$host =
$db = 
$user =
$password =
$charset =

$dsn = 'mysql:host=localhost;dbname=dbname';
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

$pdo = new PDO($dsn,$user,$password,$opt);

$response = array();

$name = $_POST['name'];

$query = "SELECT user_id, name, email FROM users WHERE name LIKE ?";

try {
$stmt = $pdo->prepare("SELECT user_id, name, email FROM users WHERE name LIKE ?");
$stmt->execute([$name]);
$result = $stmt->fetch();
} catch (Exception $e) {
    echo "99"; //Statement failed
}



if ($result !== false) {
    foreach($result as $row) {
       echo json_encode($row['user_id']);
       echo json_encode($row['name']);
       echo json_encode($row['email']);
}
} else {
    echo '2'; //Empty result
}


$dsn = null;

} else {
    echo "3"; //No search entry
}

AndroidStudio的相关代码如下:

@Override
public void onTextChanged(CharSequence charSequence, int i, int i1, int i2) {
final String name = searchInput.getText().toString();

Response.Listener<String> responseListener = new Response.Listener<String>() {
    @Override
    public void onResponse(String response) {
        try {

            System.out.println(response);
            System.out.println(name);

            if(response != null) {
                System.out.println("Statement executed");
            } else if (Integer.parseInt(response) == 2) {
                System.out.println("Statement executed, but result invalid");
                Toast.makeText(getApplicationContext(), "No results found", Toast.LENGTH_SHORT).show();
            } else if (Integer.parseInt(response) == 3) {
                System.out.println("Search field empty");
                Toast.makeText(getApplicationContext(), "No search entry", Toast.LENGTH_SHORT).show();
            } else if (Integer.parseInt(response) == 99) {
                System.out.println("Failed to execute");
                Toast.makeText(getApplicationContext(), "Statement failure", Toast.LENGTH_SHORT).show();
            } else {

                JSONArray jsonResponse = new JSONArray(response);

            }


        } catch (JSONException e) {
            e.printStackTrace();
        }
    }
};

PopupContactRequest AddContactRequest = new PopupContactRequest(name, responseListener);
RequestQueue queue = Volley.newRequestQueue(PopupAddContact.this);
queue.add(AddContactRequest);

}

一旦我实际上可以将一些有用的数据传递给应用程序，我想用它填充一个搜索建议类型的 ListView ，以便用户可以选择要添加的合适的人。如果有人也知道如何做到这一点，请随时将其添加为评论或给我发消息，因为我需要我能得到的所有帮助!

干杯，J

Answer 1

你想要匹配字符串开头的数据，所以你必须在末尾附加 %

try {
$stmt = $pdo->prepare("SELECT user_id, name, email FROM users WHERE name LIKE ?");
$name = $name."%"; // add this line
$stmt->execute([$name]);
$result = $stmt->fetch();
} catch (Exception $e) {
    echo "99"; //Statement failed
}