MySQL PDO query to search by name and role

I'm new to this PDO PHP and I have coded this spaghetti style. I hope you can give me idea how to improve using prepared statements. I'm not a lazy person, but I really need your help to improve my coding style.

```
try {
$select = "SELECT users.id, users.users_id, username, firstname, lastname, password, position";
$from = " FROM users
INNER JOIN users_role
ON users.id=users_role.users_id
INNER JOIN role
ON users_role.users_role=role.id";
$where = " WHERE";
$placeholders = array();
if ($category !=''){
if ($category == 'admin'){
$where .= " position='admin'";
if ($character_match !=''){
$where .= " AND ";
}

}
else if($category == 'instructor'){
$where .= " position='Instructor'";
if ($character_match !=''){
$where .= " AND ";
}
}
else if ($category=='student'){
$where .= " position='student'";
if ($character_match !=''){
$where .= " AND ";
}
}
elseif ($category == 'Unverified'){
$where .= " position='Unverified'";
}
else if ($category == 'view_all') {
$where = "";
if ($character_match != ''){
$where = " WHERE ";
}
}
if($character_match !=''){
$where .= "(username LIKE '" . $character_match . "%'"
. " OR firstname LIKE '" . $character_match . "%'"
. " OR lastname LIKE '" . $character_match . "%')";
$placeholders[] = $character_match;
}
$where .= " ORDER BY position ASC";

$sql = $select . $from . $where;
$q = $t

There's different things about your code, the most important one is to start using PDO tools to facilitate our work. Use named parameters, and dissociate your query construct from setting correct values:

-
if the only thing you're going to catch is echo the error message, there's absolutely no reason to do it. setting $this->db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); will react in the exact same way.

-
for the LIKE portion of the code: named parameters must contain a full litteral string (or integer, or whatever) but you have to put wildcards % IN the string for the LIKE to work. exemple: $values[':character_match'] = '%value%'

$sql = 'SELECT users.id, users.users_id, username, firstname, lastname, password, position
            FROM users
            INNER JOIN users_role ON users.id=users_role.users_id
            INNER JOIN role ON users_role.users_role=role.id ';
if ($category != 'view_all') {
    $sql .= 'WHERE position=:position ';
}
if ($character_match != '') {
    $sql .= ($category == 'view_all' ? ' WHERE ' : ' AND ');
    $sql .= '(username LIKE :character_match OR firstname LIKE :character_match OR lastname LIKE :character_match)';
    $values[':character_match'] = $character_match;
}
$sql .= ' ORDER BY position';

switch ($category) {
    case 'admin':
        $values[':position'] = 'admin';
        break;
    case 'instructor':
        $values[':position'] = 'Instructor';
        break;
    case 'student':
        $values[':position'] = 'student';
        break;
    case 'Unverified':
        $values[':position'] = 'Unverified';
        break;
}

$q = $this->db->prepare($sql);
$q->execute($placeholders);

$rows = array();
while ($row = $q->fetch(PDO:: FETCH_ASSOC)) {
    $rows[] = $row;
}

StackExchange Code Review Q#42644, answer score: 3