Page 1 of 2
number of rows
Posted: Mon May 12, 2003 5:54 pm
by liuliu
I am a beginner but I have to set up a search web page quick. So I found a script which works great for me. As I need to put a next/previous links on the page to show only 10 results at a time, I found another script and added it to the original script. Now the total number of records before the result table always shows as "10" (which is incorrect), and the next link opens up a blank page.
The code is as follows:
<?php
include("dbinfo.inc.php");
if(!isset($start)) $start = 0;
(here connect to database)
if ($submit)
{
$result = mysql_query( "SELECT * FROM test WHERE Subject='$Subject' LIMIT " . $start . ", 10")
or die("SELECT Error: ".mysql_error());
$num_rows = mysql_num_rows($result);
if(mysql_num_rows($result))
{
print "<font size='4pt'><b>Subject: $Subject:</b></font><P>";
print "<font size='4pt'><b>Search Result: There are $num_rows records.</b></font><P>";
//Above: the $num_rows always shows "10", which is incorrect
(search result table)
//second script added for navigation
$query = "SELECT count(*) as count FROM test WHERE Subject='$Subject'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$numrows = $row['count'];
if($start > 0)
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start - 10) . "\">Previous</a><BR>\n";
if($numrows > ($start + 10))
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start + 10) . "\">Next</a><BR>\n";
I wonder what's wrong.
Thanks for your time
Liu Liu
Posted: Mon May 12, 2003 7:45 pm
by volka
you've limited the resultset to 10 records, therefor mysql_num_rows only recognizes 10 entries. There is a way to directly retrieve the number of rows before
LIMIT took place but only with recent versions of mysql.
See:
http://www.mysql.com/doc/en/SELECT.html, search for SQL_CALC_FOUND_ROWS on that page
But it might be easier and more advisable to take the number of rows from a second query
$result = mysql_query( "SELECT * FROM test WHERE Subject='$Subject' LIMIT " . $start . ", 10") or die("SELECT Error: ".mysql_error());
$num_rows = mysql_num_rows($result);
Code: Select all
$result = mysql_query( "SELECT count(*) FROM test WHERE Subject='$Subject'") or die("SELECT Error: ".mysql_error());
$num_rows = array_shift(mysql_fetch_row($result));
$result = mysql_query( "SELECT * FROM test WHERE Subject='$Subject' LIMIT " . $start . ", 10") or die("SELECT Error: ".mysql_error());
It works! Now the next link...
Posted: Tue May 13, 2003 12:30 pm
by liuliu
Hi,
Thank you so much for your help. I replaced the code and it works, showing the correct total number of records in the result list. Now the second problem is the next link. When I click on the next link, it does not show the next 10 records. The page is empty.
Here is the code:
if($start > 0)
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start - 10) . "\">Previous</a><BR>\n";
if($numrows > ($start + 10))
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start + 10) . "\">Next</a><BR>\n";
Thank you for your time.
Liu Liu
Posted: Tue May 13, 2003 1:00 pm
by Jim
The parentheses might be messing you up. If I were you, I'd set the $start+ and - ten as different variables. It just makes it easier, imo.
In otherwords:
$next = $start + 10;
$previous = $start - 10;
Then use this code:
Code: Select all
if($start > 0)
echo "<a href="" . $PHP_SELF . "?start=" . $previous . "">Previous</a><BR>\n";
if($numrows > ($start + 10))
echo "<a href="" . $PHP_SELF . "?start=" . $next . "">Next</a><BR>\n";
Posted: Tue May 13, 2003 3:01 pm
by volka
next link
Posted: Tue May 13, 2003 5:48 pm
by liuliu
nope.
I replaced the two variables, the next link still gives me a blank page.....
Thanks
liu liu
next link
Posted: Tue May 13, 2003 5:59 pm
by liuliu
nope again
$start is defined if it is not defined. See the first 3 lines of code in
my first email.
<?php
include("dbinfo.inc.php");
if(!isset($start)) $start = 0;
Nice try
I noticed that when I clicked "next", the URL shows "url.php?start=10
looks like start works fine, but the script just fails to show the next 10 results.
Does that help?
Thank you for your time
liu liu
Posted: Tue May 13, 2003 6:13 pm
by volka
url.php?start=10 doesn't mean php recognizes that

but what about
if ($submit)
{ ...
where does this come from and is it defined?
Posted: Wed May 14, 2003 10:16 am
by ampersand
I'm having the same problem with this script..
I get the amount of results as specified and when I'm clicking next I get nothing. When clicking previous/back I get nothing
next link
Posted: Wed May 14, 2003 2:43 pm
by liuliu
Hi,
My website is down today so I could not access it. But I think I know what the problem is, though I don't know how to fix it. After I selected a subject from the pull down menu and clicked submit, the page showing the first 10 results had the URL:
url.php?subject=education&submit=submit&start=0
Then I clicked "next", and the URL was url.php?start=10
It is apparent that the variables $subject and $submit had not been passed to the "next" page, or probably in my case, they had been overwritten when the page loaded again.
My web page starts with a regular html pull down menu and a submit button.
THEN I started the <?php
connect database
If submit..... run sql and show result
limit 10, next & previous link
?>
So when the "next" page loads, it will show the pull down menu, but nothing else, because no option has been selected in the menu and the submit button has not been clicked. What it actually does is to load the initial search entry page is all.
If this is the probrem, then either I have to separate the pull down menu page from the result page, or add more complicated codes to the same page ???
Any ideas?
Thank you for your time
liu liu
Posted: Wed May 14, 2003 3:13 pm
by ampersand
I have two pages but the same problem.
First I enter the keyword that is passed along to the "query" page as a variable, but when clicking next I just get an empty page.
Posted: Wed May 14, 2003 3:30 pm
by twigletmac
You have both read this haven't you:
viewtopic.php?t=511
So you are sure that it's nothing to do with register_globals?
Mac
Posted: Wed May 14, 2003 4:16 pm
by ampersand
Yes. I have read that..
next link
Posted: Wed May 14, 2003 5:17 pm
by liuliu
Hi,
I read it after I was told, but I could barely understand the first half. That is, we have to use $_POST['variable'] to access a variable from an input form, right?
I use $subject as a variable to store the option selected from the pull down menu. And I only use this variable in the sql. Does that mean that I have to use $_POST['variable'] in the sql. That doesn't look right.
And also, my script runs beautifully if I don't limit the results to 10 and have a next/previous link. It obviously can access the variable if I am satisfied with showing 300+ records on one page.
See, I told you, I don't understand.
Thank you for your time.
liu liu
Posted: Wed May 14, 2003 6:39 pm
by McGruff
Here's a paginator script I use. One final improvement I never quite got around to would be to show the page numbers in bold except for the current page - dithering it out is slightly more user-friendly but you can probably work out how to do that.
The function does a LIMIT query to find items for the selected page and returns the result resource identifier, along with a "page X of Y" type nav link.
Code: Select all
<?php
// An "abstracted" function for pagination. Can use for forum posts, multi-page articles - anything you like.
// Outputs "page X of Y .. 1 | 2 | 3 " nav link as well as a result resource identifier containing all db rows for the selected page.
// In the calling script:
/*******************************************************
// get the fn output array (! see below for notes on passing vars to paginator !)
$page = paginator($table, $ID_column, $cols, $where, $order_by, $max, $link, $style, $page_choice);
// echo the page nav links - anywhere you like in your html layout
echo $page[0];
// loop through the result resource
while ($result = mysql_fetch_array($page[1])) {
...
..format & output $vars (ie the table columns specified in the $cols argument) in html as you please..
...
}
************************************************************/
// Paginator vars explained:
// $table - name of table to query.
// $ID_column - col used to find total table rows (an primary key auto-increment INT column is best - but your choice)
// $cols - part of the mysql query string "SELECT col1, col2, col3, etc..". Separate col names by commas, no spaces at start or end of the string. Set: $cols='*' to SELECT all columns.
// $where - the WHERE clause, if required, like this: $where = "WHERE column_name='value' "; (note the trailing space!). $where must be set but, if you don't actually need a WHERE clause, set: $where = ''; (no trailing space).
// $order_by - the ORDER BY clause, if required, like this: $order_by = 'ORDER BY column_name DESC '; (note the trailing space!). $order_by must be set but if you don't actually need an ORDER BY clause set: $order_by = ''; (no trailing space).
// $max - the max items per page (I usually define a constant in a config section in the calling script). If, for some reason, this is passed to the calling script via user input, you MUST do this before passing the value to paginator(): $max = intval($max); (prevents mysql query hijacking).
// $page_choice - the page to be viewed. This will be passed to the calling script most likely via the query string, ie something like this: articles.php?op=viewarticle&page_choice=2. You MUST do this to $page_choice before passing the value to paginator(): $page_choice = intval($page_choice); (prevents mysql query hijacking).
// $link - the URL to your site page, minus the $page_choice value (this will be added by the script). If you call a viewArticle() function like this: articles.php?op=viewarticle&page_choice=2 $link would be: $link = 'articles.php?op=viewarticle&page_choice='; (the full query string minus the page number).
// $style - refers to a text style in an attached stylesheet; the page X of Y nav link will be displayed in this style (!! more work needed here !! Really need two styles: a bold and normal version so we can dither out the current page number in the "1 | 2 | 3 | 4" part of the link; also note that <a> styles may over-ride the $style argument in paginator() depending on what's in the stylesheet).
// Security Issues: setting $table
// Any vars defining table or column names (et al?) in a mysql query must be examined very carefully. If you call paginator() like this:
// $page = paginator('articles', 'aid', 'aid, title, intro', '', 'ORDER BY title ', 20, 'index.php?page=articles_list_', 'body', $page_choice);
// ... the $table argument is not passed as a var so no-one can tell the function to sniff around in any tables you don't want them to. BE VERY CAREFUL if your scripts choose a $table dynamically. If you set $table from user input it could be possible for a hacker to tell paginator to display items in any db table.
// Security: query hijacking
// It's up to you to check that arguments passed to paginator() are safe. If the function is called as above (all arguments are strings except for $page_choice), you're OK (just don't forget to intval $page_choice). However, if you send $vars to the function, make sure your scripts are not vulnerable to value substitution and carry out checks on vars created from user input. If you don't know how to do that, DO NOT send vars to paginator, just call the function with strings as above (and intval $page_choice). There's nothing inherently insecure with the paginator script: all your mysql query code will be equally unsafe if you don't know how to make db queries secure.
// Security: access to the paginator script file
// THE PAGINATOR FILE MUST BE .htaccess PROTECTED! (include it in other scripts where you need it). If a site visitor can run the script directly they could submit any old table name and so query any database table (user passwords eg..) - and that would be just the least of a whole world of nastiness which could be perpetrated. Again, it's not the paginator script that's at fault: most normal php websites have scripts which can be abused if access is not controlled. Htaccess protection for the paginator file means that the script can only be called with the arguments YOU the programmer choose to send to it.
function paginator($table, $ID_column, $cols, $where, $order_by, $max, $link, $style, $page_choice) {
// get num rows & total pages
$mysql = "SELECT $ID_column FROM $table";
$query = mysql_query($mysql) or die("Cannot query the database.<br>" . mysql_error());
$rows = mysql_num_rows($query);
// reclaim some memory
mysql_free_result($query);
// get total pages
$total_pages = ceil($rows / $max);
// First, some checks on $page_choice.
// (1) If page choice == 0 we choose the last page
// (2) If we delete the only item on a multi-page list of topics, or add a new item which takes us beyond the current page, $page_choice supplied to this function is no longer valid so we select the last page:
IF ($page_choice == 0 OR $page_choice > $total_pages) {
$page_choice = $total_pages;
// auto sets to first page if null
} ELSEIF (is_null($page_choice)) {
$page_choice = 1;
}
// declare $offset for db query; result rows start at 0 so, if 10 items per page, need offset: 0, 9, 19, 29 etc
// EXCEPT when $page_choice = 0 (choose last page)
// OR if $page_choice > $total_pages (choose last page again)
// OR choose first page in any other case
IF ($page_choice > 1 AND $page_choice <= $total_pages) {
$offset = ($page_choice - 1) * $max - 1;
} ELSEIF ($page_choice == 0 OR $page_choice > $total_pages) {
$offset = $rows - $max - 1;
} ELSE {
$offset = 0;
}
// get rows for the page
$mysql = "SELECT " . $cols . " FROM " . $table . " " . $where . $order_by . "LIMIT " . $offset . "," . $max;
$query = mysql_query($mysql) or die("Cannot query the database.<br>" . mysql_error());
// now define the page nav links (!! NB: will need to adjust the code to take account of instances where we may have hundreds of pages, such as a mature discussion forum)
// if only one page, no need to create the string
IF ($total_pages > 1) {
$page_nav_links = " . . . . ";
$x = 1;
do {
IF ($x == $page_choice) {
// this is the page currently being viewed so no hyperlink needed
// !! syles: more work needed: should be dithering this out !!
$page_nav_links .= $x . ' | ';
} ELSE {
$page_nav_links .= '<A HREF="' . $link . $x . '">' . $x . '</A> | ';
}
$x++;
} while ($x <= $total_pages) ;
}
// define the rest of the nav link preceding the ".... X | Y | Z" string declared above
$page_nav_links = '<span class="' . $style . '">page ' . $page_choice . ' of ' . $total_pages . $page_nav_links . '</span>';
// paginator function output
return $output= array($page_nav_links, $query);
}
?>