I have a database something like mentioned below. Please note that this is not the complete table structure. Only the relevant is shown.
CREATE TABLE IF NOT EXISTS `student` (
`id` int(13) NOT NULL AUTO_INCREMENT,
`title` varchar(3) NOT NULL,
`firstname` varchar(30) NOT NULL,
`middlename` varchar(30) NOT NULL,
`lastname` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
I'm collecting the name in 3 parts, Firstname , Middlename and Lastname . Now while searching for a name i'm trying to CONCAT all the 3 parts and then search.
$sql_name = mysqli_query($con, "SELECT * FROM `student` WHERE CONCAT( firstname,' ', middlename,' ', lastname ) Like '".$search."%' LIMIT ".$start.", ".$limit."") or die("Error: ".mysqli_error($con));;
I'm only wondering if there is a better and faster way to achieve this ?
While searching over the web i saw some people using the 'difference' between name and search string and displaying it in descending order, however they weren't using MySql .