[resolved] Count(*) my query?

[Mr Chris]

Hi,

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

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

Which is part of this line:

[code=php:0]
    $query_count = mysql_query ( "SELECT COUNT(*) AS total FROM news_stories WHERE " . $search_query );
    $result_count = mysql_fetch_assoc ( $query_count );
[/code]

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

[code=php:0]  $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()); [/code]

Can someone please help?

Thanks

Chris



[code=php:0]
<?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
                                                 

?>
[/code]

[hvle]

There is maybe something is wrong with your query,
$query_count = mysql_query ( "SELECT COUNT(*) AS total FROM news_stories WHERE " . $search_query );
Normally after Where, there should be 1 or more condition. 

You should echo your query to see is it a valid query.  Do this before you execute the query.

[HuggieBear]

Your query is wrong... If you look at what your query looks like, it's like this:

[color=blue]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%'[/color]

This is not valid SQL.

Regards
Huggie

[Mr Chris]

Thanks Guys,

I've changed my query to:

[code=php:0]
$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%' ) Order by id Asc";
[/code]

Which is a valid query as i've tested it in phpmyadmin.  However i'm still getting the same error message:

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

So why is my Count(*) line:

  [code=php:0] $query_count = mysql_query ( "SELECT COUNT(*) AS total FROM news_stories WHERE " . $search_query ); [/code]

Not Working with this line?

[code=php:0]$result_count = mysql_fetch_assoc ( $query_count ); [/code]

Many Thanks

Chris

[HuggieBear]

It's still not valid... That query now looks like this:

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

Try this...

[code]
<?php
$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%' ) ORDER BY id";

$result = mysql_query($search_query);

$result_count = mysql_num_rows($result);
?>[/code]

Regards
Huggie

[Mr Chris]

Hi Huggie,

Thanks for your help.  From what i've read I would have thought i'm nearly there from this.  I've gone back and used a simple query to check it's all working

So With my pagination script I get:

- It to output all the results in the DB
- I get it so it displays pagination previous and next buttons

BUT IT DOES NOT LIMIT MY RESULTS - ie it displays all the results on one page:

[url=http://www.greenocktelegraph.co.uk/gtele/search_test/results_works.php]http://www.greenocktelegraph.co.uk/gtele/search_test/results_works.php[/url]

Now I know I have to do something with this line:

[code=php:0]$query_count = mysql_query ( "SELECT COUNT(*) AS total From news_stories" );  [/code]

To incorporate this query:

[code=php:0]    $search_query = "select * from news_stories"; [/code]

But I’m not sure what?

I just need it to count the results?

Thanks

Chris

[code=php:0]
<?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 * from news_stories";

// 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" );  // THIS LINE IS THE PROBLEM!!!!
    $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
                                                 

?>
<?while ($obj=mysql_fetch_object($search_results)){?>

<table width="100%" border="0" cellspacing="0" cellpadding="0" background="lines.jpg">
  <tr>
    <td height="1"></td></tr>
</table><img src="blank.jpg" width="4" height="4">
<table width="100%" border="0" cellspacing="1" cellpadding="1">
  <tr>
    <td width="41%" height="25">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td width="2%"><img src="results/left_jaw.jpg" width="11" height="24"></td><td width="93%" bgcolor="#CCCCCC"><span class="arrow">»</span> <span class="name"><?echo $obj->headline; ?></span></td><td colspan="2" width="5%"><img src="results/right_jaw.jpg" width="11" height="24"></td></tr>
</table></td><td width="59%" height="25">&nbsp;</td></tr>
</table><img src="blank.jpg" width="4" height="4">
<?}
?>
[/code]