Jump to content

[resolved] Count(*) my query?

Mr Chris

By Mr Chris
in PHP Coding Help

 Share

Recommended Posts

Mr Chris Advanced Member

Mr Chris

Posted
Posted
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]
Link to comment
Share on other sites
hvle Member

hvle

Posted
Posted
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.
Link to comment
Share on other sites
HuggieBear Member

HuggieBear

Posted
Posted
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
Link to comment
Share on other sites
Mr Chris Advanced Member

Mr Chris

Posted
  • Author
Posted
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
Link to comment
Share on other sites
HuggieBear Member

HuggieBear

Posted
Posted
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
Link to comment
Share on other sites
Mr Chris Advanced Member

Mr Chris

Posted
  • Author
Posted
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]
Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.