I am trying pagination for a database-database search engine - this was the query

$sql = "select id, name from student_adv WHERE name LIKE '%$search%' order by id limit $startIndex, $perPage";
and it worked fine when i had
$search = John

The problem starts when i put $search = John Lennon

I modify my query to
$sql = "select id, name from student_adv WHERE name LIKE '%$search%' or name like '%$w1%' or name like '%$w2%' limit $startIndex, $perPage";

here
$search = John Lennon
$w1= John
$w2=Lennon

But, the result shows the presence in an order which is random, i.e. Lennon is in the result-set array before John Lennon or John,
But, I want the result set array to have John Lennon coming before John & John before Lennon..
So John Lennon Results come first then John and the Lennon

How do I do this??

try to extend your query with order by:


$sql = "select id, name from student_adv WHERE name LIKE '%$search%' or name like '%$w1%' or name like '%$w2%' order by name asc limit $startIndex, $perPage";


or:


$sql = "select id, name from student_adv WHERE name LIKE '%$search%' or name like '%$w1%' or name like '%$w2%' order by name desc limit $startIndex, $perPage";


whichever is correct.

Hi ManOnScooter,

In order to make certain that your results are displayed in order, include a date field or ID field in the order clause after the name:

try this sql
select * from student_adv where id!='' and match(`student_adv`) against ('\"searchkeyhere\"' IN BOOLEAN MODE)

this sql will search your string in and boolean mode...
for example john doe, this sql will return john abes doe, john mike doe, john william doe...
and not return john axe, doe john, or dei john arc,,

if you want return like with or mode, you can remove double quote and remove in boolean mode.