Create a vocations table with columns such as id, name, description which you can add rows for each of the available vocations. You can then search the players table for players by vocation name by JOINing the vocations table.
-- Example query to fetch players who are 'knights'
FROM players p
LEFT JOIN vocation v ON v.id = p.vocation
WHERE v.name = 'knight'
SELECT name FROM players LEFT JOIN vocation ON vid = vocation WHERE vname = $search
CREATE TABLE `players` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`group_id` INT NOT NULL DEFAULT 1,
`account_id` INT NOT NULL DEFAULT 0,
`level` INT NOT NULL DEFAULT 1,
`vocation` INT NOT NULL DEFAULT 0,
`health` INT NOT NULL DEFAULT 150,
`healthmax` INT NOT NULL DEFAULT 150,
`experience` INT NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE
ENGINE = InnoDB;
CREATE TABLE `vocation` (
`vid` INT NOT NULL,
`vname` VARCHAR (11),
PRIMARY KEY (`vid`),
) ENGINE = InnoDB;