SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Counting the number of results

    Hi Guys,

    I'm still working on a search return results script. I've nearly got it working apart from outputting the number of records for the search results found:

    [PHP]
    // Start to build the query and order the listings by the company name

    $add = '';

    $search_query = "select * from directory_listings where";

    if($business_type == '') //Nothing entered
    {
    $add .= " business_type LIKE '%'";
    }
    else
    {
    $add .= " business_type = '$business_type'";
    }
    if(!empty($town))
    {
    $add .= " AND town LIKE '%$town%'";
    }
    if(!empty($company_name))
    {
    $add .= " AND company_name = '$company_name'";
    }

    // End building the query and order the listings by the company name


    // Start pagination script and state amount of records per page

    $limit = 25;
    $query_count = mysql_query ( "SELECT COUNT(*) AS total FROM directory_listings WHERE " . $add );
    $result_count = mysql_fetch_assoc ( $query_count );
    $totalrows = $result_count['total'];
    $PHP_SELF = $_SERVER['PHP_SELF'];

    if( ! isset ( $_GET['page'] ) )
    {
    $page = 1;
    }
    else
    {
    $page = $_GET['page'];
    }

    $limitvalue = $page * $limit - ($limit);

    // End pagination script and state amount of records per page
    Last edited by 7724; Nov 29, 2006 at 07:57.

  2. #2
    SitePoint Enthusiast Optic's Avatar
    Join Date
    Aug 2000
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SQL_CALC_FOUND_ROWS is probably what you want

  3. #3
    SitePoint Member
    Join Date
    Nov 2006
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You want to use FOUND_ROWS()...

    The manual will explain it better than me but basically:
    PHP Code:
     $query "SELECT SQL_CALC_FOUND_ROWS col1,col2,col3 FROM table WHERE col1='$val1' LIMIT 0,25"
     
    $query_result mysql_query($query);
    // get the total
     
    $count_result mysql_query('SELECT FOUND_ROWS() AS total')
     
    $count_result_array mysql_fetch_array($count_result);
     
    $total  $count_result_array['total']; 

  4. #4
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers Guys!

  5. #5
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guys,

    Done some research on FOUND_ROWS(), and thought this may work. However this says in my db of 31 records;

    On Page 1
    Your search returned 25 result(s). //Wrong!

    On Page 1
    Your search returned 31 result(s). //Right!!!

    Even more bizzare!

    Any help please?

    PHP Code:

    // Start to find how many search results are being found for the query

            
    $search_query .= $add " LIMIT " $limitvalue ", " $limit;
            
    $search_results mysql_query($search_query$can_i_connect);
            
    $result mysql_query($search_query) or die (mysql_error()); 

    // Figure out the total number of results in DB:
    $total_results mysql_result(mysql_query("SELECT FOUND_ROWS()"), 0);
            
            if(
    $total_results <= 0)
            {
                echo 
    "Sorry, there were no results for your search .";
            }

    // Else and Start to find how many pagination pages I have
            
    else
            {    
                echo 
    "<b>Your search returned ".$total_results." result(s).</b> <br /><br />Here are those results, listed in ascendng order. <br /><br />";

     if(
    $page != 1){   
            
    $pageprev $page 1;
             
              
            echo(
    "<a href=\"$PHP_SELF?page=$pageprev\">PREV</a> ");   
        }else{ 
            echo(
    "PREV"); 
        } 
      
        
    $numofpages $number_of_results$limit
         
        
    #echo "<br>", $totalrows; 
       #exit; 
          
        
    for($i 1$i <= $numofpages$i++){ 
            if(
    $i == $page){ 
                echo(
    $i." "); 
            }else{ 
                echo(
    "<a href=\"$PHP_SELF?page=$i\">$i</a> "); 
            } 
        } 


        if((
    $totalrows $limit) != 0){ 
            if(
    $i == $page){ 
                echo(
    $i." "); 
            }else{ 
                echo(
    "<a href=\"$PHP_SELF?page=$i\">$i</a> "); 
            } 
        } 

        if((
    $totalrows - ($limit $page)) > 0){ 
            
    $pagenext $page 1
               
            echo(
    "<a href=\"$PHP_SELF?page=$pagenext\">NEXT</a>");   
        }else{ 
            echo(
    "NEXT");   
        } 

            }   
    // End of how many results I have found
          
        
    mysql_free_result($result); 


    // End of Else and to find how many pagination pages I have 
    Last edited by 7724; Nov 29, 2006 at 07:58.

  6. #6
    SitePoint Evangelist IJoeR's Avatar
    Join Date
    Feb 2003
    Location
    Somewhere in, MD
    Posts
    400
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just letting you know that the reason why you were getting a value of "25" is because in your select statement you used LIMIT 0, 25. So basically you told it not to go passed "25" records.

    If you remove that then it might give you the correct amount.

  7. #7
    SitePoint Addict scoobasteve1982's Avatar
    Join Date
    Apr 2007
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about mysql_num_rows function


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •