Android / php：使用PDO使用“LIKE”检索一组结果，并解析为JSON

Question

I'm trying to use a query to search a database, and return a few columns from a table wherever the user's name is LIKE the search query. I.e if I type in 'M', names like Max, Matthew etc. would be retrieved. However, when executed the query isn't returning anything. I've surrounded it all with try/catch functions and they work properly, echoing an integer that I can use, but I'd much prefer that the code actually did what it's meant to do. I've spent quite a while fiddling with this, first trying to use MySqli then moving to PDO since everyone online thinks it's better.

If anyone can see what's wrong with this, please don't hesitate to correct it!

The server-side script is below:

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
}

The relevant code from AndroidStudio is as follows:

@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);

}

Once I can actually get some useful data passed to the app, I'd like to populate a search-suggestion type listview with it, so that the user can select the appropriate person to add. If anyone also knows how to do this, feel free to add it as a comment or message me, as I need all the help I can get with this!

Cheers, J

Answer 1

You want data which match beginning of string so in like you have to append % at end

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
}