如果你的mysql查询中有一些条件（Where子句），如何创建分页？

Question

I have a query like this :

     $pagenumber = get_page_number(); // I'll get the pagenumber by a method wich runs when user click's on page numbers , for example : 2 or 3 or whatevere user clicks.


     //Now , I'll make A Limit caluse : 

     $limit = $pagenumber.",10"; // limit the page to show 10 result  => example : LIMIT 2,10

     $sql = "SELECT * From doctors LIMIT $limit";
     --> after this , I show the result which is by my on way (echo a div for every result );

It was a cool pagination , which was working fine.

---

But the problem is , when there is A WHERE in my query , mysql gets confused and I dont know what to do

Consider this :

     $sql = "SELECT * FROM doctors WHERE `firstname` LIKE '$firstname' LIMIT $limit";    

So , its not working:((

I know the problem is "WHERE " clause , because when user clicks on a page number , my $limit will be for example : 2,10 and my query will be this :

    $sql = "SELECT * FROM doctors Where firstname LIKE '$firstname' LIMIT 2,10";    

Here there is A condition (WHERE firstname ....) How mysql even knows where to start searching ?(by starting point , I mean if "LIMIT x,y" x is starting point)

How mysql knows what I mean by "LIMIT 2,10".

Where does placed 2 in my table ?(when there is a condition);

If there is NO condition , 2 means record number 21 ( am I right ?), of course if offset is 10 ; (LIMIT 2,10) ;

is that Right?

But , what if there is A WHERE clause , ??????

Now what is that starting point ?(2????)

If you want more specific details , feel free to ask(I think I explained percisely) Thanks

Answer 1

$pagesize = 10;
$start = ($page) * $pagesize;  // assuming you're passing in 0 based pages, if they're 1 base, do a $page -1

$sql = "SELECT * FROM doctors Where firstname LIKE '$firstname' ORDER BY lastname LIMIT $start,$pagesize"; 

You need to order your result set in order for limit to make sense. Also, start needs to account for page size.