One Pagination error - help please ;-)

[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?

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 


?>

[Piranha]

On that row you try to run the following string:

SELECT COUNT(*) AS total FROM news_stories WHERE 
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

You have two select statements. A good idea to spot these kind of problems is to do the following:

$sql = "SELECT column FROM table WHERE ...";
print $sql; // Here you output the query, usually you can see problems just by doing that.
$result = mysql_query($sql);

By the way, take a look at [man]mysql_real_escape_string[/man] and the LIMIT function in mysql.

[7724]

Thanks,

But may be being a bit thick here, but are you saying I can't execute the two queries on this line?:

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

IE I can't run the select statement from $search_query?

Many Thanks

Chris

[Piranha]

I haven't looked that closely at the query in $search_query. But I know that if you want to run it as a subquery you should have it inside a parenthesis, not like you have. You can't do this:

SELECT column FROM table WHERE SELECT column2 FROM table2

And that is exactly what you are trying to do.

[7724]

Thanks,

So what you are saying is that my 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());

I can't do a count(*) on:

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

As you can't count on on the query already?

Sorry, i'm confused though. Is this the case?

Thanks

Chris

[Piranha]

Sorry for being brutally honest, but you are doing a few stupid things in the way you try to do it. The worst is probably that you try to do this as a subquery when there is absolutely no reason to. But I'll come to that later. If I assume that your first query works and returns something and that you use that as a subquery, then you put in a table in a WHERE in the next query. There is no reason to do that at all, and the result will in best case be strange.

If you defenetley want a subquery you should do like this.

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

It makes the count from the table that is created and returned from the subquery. But it is a really bad way to do it since there is no reason to have the DBMS work more than it have to, and formatting the date and order the result takes time. A better way to do is this:

$search_query = "
    	select *
    	from news_stories 
    	where section like '%$section%' 
    	AND unix_timestamp(published) <= unix_timestamp(NOW()) 
    	AND ( headline LIKE '%$searchstring%' 
    	OR story_text LIKE '%$searchstring%' )";
    $sql = "SELECT COUNT(*) AS total FROM (" . $search_query . ")";
    $query_count = mysql_query ($sql);

Then the database does not have to first order and format the result and after that just throw away that work. But it is still not good, it is easy to do this without any subquery:

$sql = "
    	SELECT COUNT(*) as total
    	from news_stories 
    	where section like '%$section%' 
    	AND unix_timestamp(published) <= unix_timestamp(NOW()) 
    	AND ( headline LIKE '%$searchstring%' 
    	OR story_text LIKE '%$searchstring%' )";
    $query_count = mysql_query ($sql);

That is the right way to do this query. No use for subqueries or unnessecary data that have to be handled. It is possible that you don't need to use unix_timestamp either, but I don't know that for sure.

Note that I create the whole query outside the mysql_query row, that way make it easy to look at the query if there are anything wrong.