Counting the number of results

[7724]

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

[Optic]

SQL_CALC_FOUND_ROWS is probably what you want

[startledferret]

You want to use FOUND_ROWS()...

The manual will explain it better than me but basically:

 $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']; 

[7724]

Cheers Guys!

[7724]

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?

// 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 

[IJoeR]

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.

[scoobasteve1982]

How about mysql_num_rows function