paging recordsets - Page 2

gilgalbiblewhee · #16 (**permalink**) 07-02-2008, 09:53 PM

Quote:

Originally Posted by curtiss

First of all, yes, I am saying that it appears to be the $searchthese that's causing the problem.

The rest of your SQL query does not get built if $searchTheseArr is empty, and that appears to be what's happening.

I did a document.write in the JavaScript to find out how it's writing the URL and found out that there was an & missing.

But I still have to text the rest of what you said. Thanks!

gilgalbiblewhee · #17 (**permalink**) 07-02-2008, 10:02 PM

Quote:

Originally Posted by curtiss

The syntax of the COUNT() function can be found on the MySQL manual pages. Basically, all you do is add "COUNT(*)" in place of where you would normally use just "*". Using the COUNT query will simply return a single number telling you how many rows would be returned if you were to take out the COUNT() function from your query.

In other words, if you were to use a query like:

Code:

SELECT COUNT(*) FROM mytable

and there were 100 rows in "mytable", the result of that query would be 100.

Adding an alias for the COUNT function makes it easier to refer to the value that's returned by the query. So, in other words, you could use either of the following queries (they are both exactly the same, actually):

Code:

SELECT COUNT(*) totalrows FROM mytable
SELECT COUNT(*) AS totalrows FROM mytable

Then, you would simply refer to $rs['totalrows'] to get the number that is returned (assuming that $rs is the name of the array you are using to store your recordset).

Of course, if you place a limit on your results, and use the COUNT function with that limit, the COUNT function is only going to return whatever your limit is. So, if you use LIMIT 10 in your query, and there are at least ten rows that match the criteria in your query, the COUNT function is only going to return the number 10.

Therefore, when using the COUNT function, you want to take out the LIMIT phrase from your query. Other than that, you would just use the exact same query you are using to pull the information from your database in the first place.

Ok this is what the sql says:

Quote:

SELECT COUNT(*) AS totalrows FROM bible WHERE 1=1 AND CASE WHEN text_data LIKE '%offer%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%sacrifice%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%peace%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%offering%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%offering%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%covereth%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%inwards%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%inwards%' THEN 1 ELSE 0 END > 3 LIMIT 0, 10

When I use SELECT COUNT(*) AS totalrows it shows no results except 1:.

gilgalbiblewhee · #18 (**permalink**) 07-03-2008, 03:51 PM

I followed what you said but something is still causing for it not to work:

Quote:

SELECT count(*) AS totalrows FROM bible WHERE 1=1 AND CASE WHEN text_data LIKE '%offering%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%tabernacle%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%congregation%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%Aaron%' THEN 1 ELSE 0 END > 3

I removed the limit
I put:

PHP Code:


			
echo $row['totalrows']."<br />";

which gives blank.

And the result doesn't show. When I remove the count(*) AS totalrows then I get the result again.

gilgalbiblewhee · #19 (**permalink**) 07-03-2008, 05:59 PM

Here's a shortened version of the code:

PHP Code:


			
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<title>Untitled Document</title>

</head>



<body>

<?php 

$con = mysql_connect("","root","");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }



mysql_select_db("kjv", $con);



$sql = "SELECT count(*) AS totalrows FROM bible WHERE 1=1 AND";

$sql .= " CASE WHEN text_data LIKE '%angel%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%appeared%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%flame%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%midst%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%looked%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%behold%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%burned%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%consumed%' THEN 1 ELSE 0 END > 3";

//$sql .= " LIMIT 0, 10";

$result = mysql_query($sql) OR exit( 'Error: ' . mysql_error());

        function highlight ( $str, $words )

        {

        

            if ( is_array( $words ) ) {

                                rsort($words);

                $words = array_map( 'preg_quote', $words );

                $words = join( $words, '|' );

            } else {

                $words = preg_quote( (string)$words );

            }

            //$re = '<span style="color: red; font-weight:bold;">$1</span>';

                $color[0] = "red";

                $color[1] = "blue";

            //for($c=0; $c < 8; $c++){

                //echo $color[$c]."<br />";

                $re = '<span style="color: '.$color[0].'; font-weight:bold;">$1</span>';

                //}

            return preg_replace( '/\b(' . $words . ')\b/i', $re, $str );

        }

        

echo $row['totalrows']."<br />";

$recCount = 1;

while($row = mysql_fetch_array($result)){

        echo "<span id='regular[]' style='padding: 5px; background-color: #D2C5A0; display: block; border: 1px solid #7C7C7C; color: black;'>";

        echo "<span style='font-weight: bold; color: black; background-color: #B4B3A9; display: block; border: 1px solid #7C7C7C;'>"."<span style='font-weight: normal;'> ".$recCount."</span> ".$row["book_title"]." ".$row["chapter"].":".$row["verse"]." ";

        echo "</span>\n";    



        $str   = $row['text_data'];



        //var_dump( highlight( $str, $newSearchTheseArr));

        echo highlight( $str, $newSearchTheseArr);

        echo "</span><br />\n";

        $recCount++;

}  



mysql_close($con);

?>

</body>

</html>