count(*) on query?

[7724]

Hi,

I’m having problems with my pagination search results page. I’m getting the message:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/***********/results.php on line 38

Which is part of this line:

    $query_count = mysql_query ( "SELECT COUNT(*) AS total FROM news_stories WHERE " . $search_query ); 
    $result_count = mysql_fetch_assoc ( $query_count );

However, I can’t see what is wrong? Is it not seeing my $search_query and paginating the results?

  $search_query = "select *, DATE_FORMAT(appeared, '%W %d %M, %Y') as published_date from news_stories where section like '%$section%' AND unix_timestamp(published) <= unix_timestamp(NOW()) AND ( headline LIKE '%$searchstring%' OR story_text LIKE '%$searchstring%' ) Order by id Asc" or die(mysql_error());

Can someone please help?

Thanks

Chris

<?php 

// Start the connection to the database 
         
include("************.ini");

// End the connection to the database     

// Start to get the data from the form and trim any whitespace 
         
        if($_SERVER["REQUEST_METHOD"]=='POST') 
        { 
            $section = trim($_POST['section']); 
            $searchstring = ($_POST['searchstring'] != "") ? $_POST['searchstring'] : false; 

        } 
           else 
        { 
            $section = trim($_GET['section']); 
            $searchstring = trim ($_GET['searchstring'] != "") ? $_GET['searchstring'] : false; 

        } 

// End getting the data from the form and trimming any whitespace 
         
         
// Start to build the query

    $search_query = "select *, DATE_FORMAT(appeared, '%W %d %M, %Y') as published_date from news_stories where section like '%$section%' AND unix_timestamp(published) <= unix_timestamp(NOW()) AND ( headline LIKE '%$searchstring%' OR story_text LIKE '%$searchstring%' ) Order by id Asc" or die(mysql_error()); 

// End building the query


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

    $limit = 5; 
    $query_count = mysql_query ( "SELECT COUNT(*) AS total FROM news_stories WHERE " . $search_query ); 
    $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 


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

        $search_query . " LIMIT " . $limitvalue . ", " . $limit; 
        $search_results = mysql_query($search_query, $link); 
        $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 "Your search returned ".$totalrows." result(s). <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\"><img src=\"results/previous.jpg\" width=\"120\" height=\"22\" class=\"prev_next_border\"></a> ");    
    }else{ 
        echo(""); 
    } 
   
    $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 * $page)) > 0){ 
        $pagenext = $page + 1; 
            
        echo("<a href=\"$PHP_SELF?page=$pagenext\"><img src=\"results/next.jpg\" width=\"120\" height=\"22\" class=\"prev_next_border\"></a>");    
    }else{ 
        echo("");    
    } 

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


// End of Else and to find how many pagination pages I have 
                                                  

?>

 

[thedaver]

In my opinion, you are misusing the $result_count in your first snippet.

$result_count is an associative array of results from the query ($query_count). To fetch the total number of rows in your current code, you'd need a third line requesting:

$result_count_actual=$result_count["total"];

I note that your SQL query defines the output count(*) as total, so that's what I've used.

D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[7724]

Hi Dave,

Thanks for your help:

I've added that line as suggested - is this what you mean?

It's still erroring out on:

Warning: mysql_fetch_assoc():?

Thanks

Chris

<?php 

// Start the connection to the database 
         
include("**********");

// End the connection to the database     

// Start to get the data from the form and trim any whitespace 
         
        if($_SERVER["REQUEST_METHOD"]=='POST') 
        { 
            $section = trim($_POST['section']); 
            $searchstring = ($_POST['searchstring'] != "") ? $_POST['searchstring'] : false; 

        } 
           else 
        { 
            $section = trim($_GET['section']); 
            $searchstring = trim ($_GET['searchstring'] != "") ? $_GET['searchstring'] : false; 

        } 

// End getting the data from the form and trimming any whitespace 
         
         
// Start to build the query

    $search_query = "select *, DATE_FORMAT(appeared, '%W %d %M, %Y') as published_date from news_stories where section like '%$section%' AND unix_timestamp(published) <= unix_timestamp(NOW()) AND ( headline LIKE '%$searchstring%' OR story_text LIKE '%$searchstring%' ) Order by id Asc" or die(mysql_error()); 

// End building the query


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

    $limit = 5; 
    $query_count = mysql_query ( "SELECT COUNT(*) AS total FROM news_stories WHERE " . $search_query ); 
    $result_count = mysql_fetch_assoc ( $query_count ); 
    $result_count_actual=$result_count["total"];  // New Line added
    $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 


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

        $search_query . " LIMIT " . $limitvalue . ", " . $limit; 
        $search_results = mysql_query($search_query, $link); 
        $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 "Your search returned ".$totalrows." result(s). <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\"><img src=\"results/previous.jpg\" width=\"120\" height=\"22\" class=\"prev_next_border\"></a> ");    
    }else{ 
        echo(""); 
    } 
   
    $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 * $page)) > 0){ 
        $pagenext = $page + 1; 
            
        echo("<a href=\"$PHP_SELF?page=$pagenext\"><img src=\"results/next.jpg\" width=\"120\" height=\"22\" class=\"prev_next_border\"></a>");    
    }else{ 
        echo("");    
    } 

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


// End of Else and to find how many pagination pages I have 
                                                  

?>

 

[thedaver]

Sorry, I didn't read your full code, it already had the correct handling from my first post. You can delete the new line.

Have you actually printed out the SQL command in full as a debug and tried to run that SQL in the mysql command line?

Do you have a closing quotation mark for the criteria in your WHERE clause?

D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[vacunita]

That error usually appears when the query was not executed successfully, and no results were returned to be used by the mysql_fetch_assoc function.

So first we need to see what's wrong with the query if anything:

Add the red part to your code. and post back any error it throws out.

 $query_count = mysql_query ( "SELECT COUNT(*) AS total FROM news_stories WHERE " . $search_query ) [red]or die(mysql_error())[/red] ;

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.