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
$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.