number of rows

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

liuliu
Forum Newbie
Posts: 8
Joined: Fri May 09, 2003 4:52 pm

number of rows

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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);
:arrow:

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());
liuliu
Forum Newbie
Posts: 8
Joined: Fri May 09, 2003 4:52 pm

It works! Now the next link...

Post 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
Jim
Forum Contributor
Posts: 238
Joined: Fri Apr 19, 2002 5:26 am
Location: Near Austin, Texas

Post 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";
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

liuliu
Forum Newbie
Posts: 8
Joined: Fri May 09, 2003 4:52 pm

next link

Post by liuliu »

nope. :?
I replaced the two variables, the next link still gives me a blank page.....

Thanks
liu liu
liuliu
Forum Newbie
Posts: 8
Joined: Fri May 09, 2003 4:52 pm

next link

Post 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 :wink:

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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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?
ampersand
Forum Commoner
Posts: 54
Joined: Thu Nov 28, 2002 2:00 am
Location: Norway

Post 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
liuliu
Forum Newbie
Posts: 8
Joined: Fri May 09, 2003 4:52 pm

next link

Post 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
ampersand
Forum Commoner
Posts: 54
Joined: Thu Nov 28, 2002 2:00 am
Location: Norway

Post 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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
ampersand
Forum Commoner
Posts: 54
Joined: Thu Nov 28, 2002 2:00 am
Location: Norway

Post by ampersand »

Yes. I have read that..
liuliu
Forum Newbie
Posts: 8
Joined: Fri May 09, 2003 4:52 pm

next link

Post 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
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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);

}
?>
Last edited by McGruff on Thu Aug 11, 2005 9:35 am, edited 4 times in total.
Post Reply