Forum Moderators: coopster
[webmasterworld.com...]
[webmasterworld.com...]
[webmasterworld.com...]
Birdman
i don't understand the line here from the following code posted by someone in one of the threads you directed me to:
while (list($DBfielda,$DBfieldb)=mysql_fetch_row($result))
what are $DBfielda, $DBfieldb?
thanks.
-----------------------------
<?
if(!isset($page))
{
$page = 1;
}
else
{
$page = $page;
}
$max_results = 5;
$from = (($page * $max_results) - $max_results);
$result=mysql_query("select *
from tablename
LIMIT $from, $max_results ");
$num_rows = mysql_num_rows($result);
while (list($DBfielda,$DBfieldb)=mysql_fetch_row($result))
{
?>
<tr>
<td class="webbtd"><a href="edit.php?aid=<? echo $DBaid?>"><? echo $DBtitle. ' ' .$DBfirstname. ' ' .$DBlastname?></a></td>
</tr>
<?
}
?>
</table>
<?
$total_results = mysql_num_rows($result);
$max_results = 50;
$prev = ($page - 1);
$next = ($page + 1);
$total_pages = ceil($total_results / $max_results);
echo "<div align=center>";
if($page > 1)
{
echo "<<<a href=index.php?page=$prev> Previous</a> - ";
}
for($i = 1; $i <= $total_pages; $i++)
{
if(($page) == $i)
{
echo "$i";
}
else
{
echo "<a href=index.php?&page=$i> $i </a>";
}
}
if($page < $total_pages)
{
echo " - <a href=index.php?page=$next>Next</a> >>";
}
elseif ($page = $total_pages)
{
}
echo "</div>";
?>
<?
/* Set current, prev and next page */
$page = (!isset($_GET['page'])? 1 : 0;
$prev = ($page - 1);
$next = ($page + 1);
/* Max results per page */
$max_results = 5;
/* Calculate the offset */
$from = (($page * $max_results) - $max_results);
/* Query the db for total results. You need to edit the sql to fit your needs */
$result = mysql_query("select id from tablename");
$total_results = mysql_num_rows($result);
$total_pages = ceil($total_results / $max_results);
$pagination = '';
/* Create a PREV link if there is one */
if($page > 1)
{
$pagination .= '<a href="index.php?page='.$prev.'">Previous</a> ';
}
/* Loop through the total pages */
for($i = 1; $i <= $total_pages; $i++)
{
if(($page) == $i)
{
$pagination .= $i;
}
else
{
$pagination .= '<a href="index.php?page='.$i.'">$i</a>';
}
}
/* Print NEXT link if there is one */
if($page < $total_pages)
{
$pagination .= '<a href="index.php?page='.$next.'">Next</a>";
}
/* Now we have our pagination links in a variable($pagination) ready to print to the page. I pu it in a variable because you may want to show them at the top and bottom of the page */
/* Below is how you query the db for ONLY the results for the current page */
$result=mysql_query("select *
from tablename
LIMIT $from, $max_results ");
while ($i = mysql_fetch_array($result))
{
/* This is where you print your current results to the page */
}
?>
$page = (!isset($_GET['page'])? 1 : 0;
and where am i suppose to put all the code? I mean i used to put all the database connection and search script in the beginning of the page before the <html> opening tag. My question probably is not making sense to you now, but I'll put the pages I am working on up later and hopefully you are able to take a good at it for me then. thanks.
$page = (!isset($_GET['page']))? 1 : $_GET['page'];
i almost got it to work, the only problem now is that the first page only returns 8 records while it should return 10. The rest of the pages work fine, they all return the correct number of records. Any guess on where the problem lies? Thanks again for your help!
One thing I(well, most coders) always do when having problems is echo the sql to the page to see if the error is there.
Instead of this:
$result=mysql_query("select * from tablename LIMIT $from, $max_results");
Do this:
$sql = "select * from tablename LIMIT $from, $max_results ";
print $sql;
$result=mysql_query($sql);
Then compare the query from page one to the others to see if something is amiss. Once you're done troubleshooting, remove the print line.
See if that helps and let us know if you still have the prob.
Birdman
With your advice, I checked the database and indeed there were two empty rows. :) So everything works perfect now! I want to express my appreciation for your help, thank you so much, I wouldn't have done it without your help!
Birdman
another question with pagination. okay, i have a search box on my page which allows users to search for anything with one or more words. So let's say, the user searches for "business", which the database returns 19 records. I set the $NO_recordPerPage variable to 10. So there should be two pages of results. The pagination script works fine up to that point. Page 1 returns 10 records of "business", but then when I click on page two (which is supposed to return the rest of the 9 records), I get an error that says:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result....
I know my sql query is correct, otherwise it shouldn't return the first 10 records at all.
This same situation occurs no matter what the user searches first, the first page always works fine, just the consecutive pages not able to fetch the data.
Do you have know what I did wrong? thanks so much again.
$page = (!isset($_GET['page']))? 1 : 0;
$from = (($page * $max_results) - $max_results);
In the case where $_GET['page'] is not set, you're okay.
$page = 1;
$from = 1 * 5 - 5 = 0;
In the case were $_GET['page'] is set
$page = 0;
$from = 0 * 5 - 5 = -5
So you need
$page = (!isset($_GET['page']))? 1 : $_GET['page'];
Remember - if something doesn't work, always echo your query as a first step in debugging. This is why I pretty much always write
$query = "SELECT * FROM table";
$result = mysql_query($query);
// uncomment next line to debug
// echo $query;
rather than
$result = mysql_query("SELECT * FROM table");
I didn't use the exact code posted by birdman, but thanks to it and some help from birdman, i was able to play around and made it to work. On my page, the user is able to browse by category and sub-category, and the pagination script works perfect for them, it returns the correct number of records per page and returns subsequent pages as needed. But then when I implemented the search feature, where the user can also search what they want instead of just browsing, the pagination script doesn't return subsequent pages here. it only returns the first page and even though it should return more pages, it doesn't. I did use Print $query to test my query, and the thing i found weird is that the first page returns the right query, while any subsequent pages does NOT return any query at all. so i guess the query is not being passed to any subsequent pages, but i don't konw why. thanks.
select * from books where 1=1 and (Series_No LIKE '%business%' OR Price LIKE '%business%' OR Title LIKE '%business%' OR Sort_Num LIKE '%business%' OR Calc_Field LIKE '%business%')order by Series_No asc limit 0, 10
if the user searches for "business"
but then on subsequent pages, the query is:
select * from books where 1=1 and (Series_No LIKE '%%' OR Price LIKE '%%' OR Title LIKE '%%' OR Sort_Num LIKE '%%' OR Calc_Field LIKE '%%')order by Series_No asc limit 10, 10
the search word is not being passed to the query in subsequent pages, instead of '%business%' , it's '%%'.
and i don't understand why it's not being passed. any ideas birdman, tom, or anyone? thanks.
Are you depending on register globals being on? It looks like you're doing it right (i.e. using the super globals), but just so we're all on the same track, your url should look like this
href="http://example.com/page.php?page=2&search_word=blah"
then you would use
$_GET['page'] and $_GET['search_word'] to build your query.
Alternately, you could pass the whole query except the limit clause in a session variable and append the limit and offset based on $_GET['page'].
Tom
What you can do is, before you build the pagination links, check to see if they came to the page via a search and if they did, append the search text to the URL.
In the example below, you will need to change 'search_text' to the name of the form variable that gets passed when the user originally searches.
Hopefully, that that makes sense :)
if(isset($_POST['search_text'])){
$search = '&search_text=' . $_POST['search_text'];
} else {
$search = '';
}
if($page > 1)
{
$pagination .= '<a href="index.php?page='.$prev.$search.'">Previous</a> ';
}
/* Loop through the total pages */
for($i = 1; $i <= $total_pages; $i++)
{
if(($page) == $i)
{
$pagination .= $i;
}
else
{
$pagination .= '<a href="index.php?page='.$i.$search.'">$i</a>';
}
}
/* Print NEXT link if there is one */
if($page < $total_pages)
{
$pagination .= '<a href="index.php?page='.$next.$search.'">Next</a>";
}
<added>
You're fast Ergo! BTW..
If you're using the code posted by Birdman, you're not getting subsequent pages
Absolutely right. I noticed that too and corrected it in msg #6. Too bad you can only edit your posts within an hour(or something like that) of posting it :(
</added>
"search_word" is the name of the search textbox
$start = 0
$display_number = 10
$search = $_POST['search_word'];
$query = "select * from books where 1=1 and (Series_No LIKE '%$search%' OR Price LIKE '%$search%' OR Title LIKE '%$search%')order by Series_No asc limit $start, $display_number";
then for the page numbering i use:
echo '<a href="results.php?search=' . $search . '&start=' . ($start - $display_number) . '&num_pages=' . $num_pages . '">Previous</a> ';
but somehow the search word is not being passed to subsequent pages. everything works perfect in the first page. i tried a lot of things, but still no luck. So when I click on the for example page 2 to see the 2nd page of records, my query becomes:
select * from books where 1=1 and (Series_No LIKE '%%' OR Price LIKE '%%' OR Title LIKE '%%')order by Series_No asc limit 10, 10";
where the search word is empty.
What do you get when you do a
print_r($_POST);
Tom
$search = (isset($_POST['search_word']))? $_POST['search_word'] : $GET['search'];
That line should take care of the search part but what about when they just browse to a category? Try it out and let us know what happens.
Birdman
$Parameters=$_SERVER['QUERY_STRING'];
if (preg_match("/pg.*?&/i",$Parameters,$Pg) ¦¦ preg_match("/pg.*./i",$Parameters,$Pg))
{
$Prev=htmlentities(preg_replace("/".$Pg[0]."/i","pg=".($PageNo-1)."&",$Parameters));
$Next=htmlentities(preg_replace("/".$Pg[0]."/i","pg=".($PageNo+1)."&",$Parameters));
}
else
{
$Prev=htmlentities($Parameters==""?'pg='.($PageNo-1):$Parameters.'&pg='.($PageNo-1));
$Next=htmlentities($Parameters==""?'pg='.($PageNo+1):$Parameters.'&pg='.($PageNo+1));
}
I simply parse out the user input and create an SQL query from it, then save it to a session variable as
$_SESSION['filter'] = $sql;
The for pagination, the query for any given page is simply
$query = $_SESSION['filter'] . "LIMIT " . ($page_size * ($page-1)) . ", " . $page_size;
Tom
ergophobe, thanks for your help, using sessions sounds good too, i'll try that later.
anjanesh, thanks for your inputs.
Bravos everyone!
