paging recordsets

[gilgalbiblewhee]

This formula which I have doesn't work. First is how to set the limit the page with ten records per page: mysql_num_rows seems to show a result of ten. Then how to set in the select/options the pages:

PHP Code:

<?php
//paging
$page = isset($_GET['page']) ? $_GET['page'] : 1;  //or you can use the ternary operator 

$perpage = 10;// number of records per page
$end = $page*$perpage; // present page
$start = ($page*$perpage)-$perpage; 
/******************************************************************************/

$sql="SELECT * FROM dbname WHERE 1=1 AND";

$j=0;
for ($i=0; $i < count($searchTheseArr); $i++){
    if(strlen($searchTheseArr[$i]) > 4){
        $newSearchTheseArr[$j] = $searchTheseArr[$i];
        $j++;
    }
}
for ($j=0; $j < count($newSearchTheseArr); $j++){
    $sql.=" text_data LIKE '%" .$newSearchTheseArr[$j]. "%'";
    if($j!=count($newSearchTheseArr)-1){
        $sql.=" OR";
    }else{
        $sql.= " LIMIT " . $start . ", " . $end;
    }
}    

$result = mysql_query($sql) OR exit( 'Error: ' . mysql_error());
//echo mysql_num_rows($result);
for ($j=0; $j < count($newSearchTheseArr); $j++){
    echo $newSearchTheseArr[$j]."<br />";
    }
echo mysql_num_rows($result)."<br />";?>
<span style="font-weight: bold;">PAGE <?php echo $page; ?></span>
<span style="">Go to: <select id='selectTextPage' name='selectTextPage' size= "1" style="width: 100px;" onChange='changeTextPage(this.value);' >
<?php
$myarray = explode('?', $_SERVER["REQUEST_URI"]);
foreach($myarray as $value){
    $urlsnip = $value;
}
foreach($myarray as $value){
    $Pageurlsnip = $value;
}
?>
<?php
$total = mysql_num_rows(mysql_query($sql));// or die(mysql_error());
if($perpage == 0){
    $perpage = 1;
}

$numOfPages = $total / $perpage;
//echo "<option selected>Select the Page</option>";
for ($i=1; $i <= $numOfPages; $i++) {
    $Pageurlexpl = explode("?", $Pageurlsnip);

    echo "<option ";?>value="searchLinkFiles/linkTextareaSearchResult.php?<?php
    for ($k=0; $k<=count($Pageurlexpl)-2; $k++){
      echo $Pageurlexpl[$k]."&";
    }    
    echo "page=" . $i;
    echo "&searchthese=";
    for ($j=0; $j < count($newSearchTheseArr); $j++){
        echo $newSearchTheseArr[$j]."%20";
    }
?>"><?php 
    echo "Page " . $i . "</option><br />\n";
}
?>
    </select></span>
    </span><br /><?php
while($row = mysql_fetch_array($result)){
        echo "<span id='regular[]' style='padding: 5px;'>";
        echo "<span style='font-weight: bold;'>".$row["book_title"]." ".$row["chapter"].":".$row["verse"]." ";
        echo "</span>\n";    
            $strText = $row['text_data'];
                $strText = preg_replace("/(".$newSearchTheseArr[$j].")/i", "<span class='' id='' style='color:blue; font-weight:bold;'>$1</span>", $strText);
                echo $strText."<br />\n";
        echo "<br />\n";
}

mysql_close($con);
?>

[curtiss]

I'm sorry, Gil, but once again I am really having trouble understanding what question you are asking.

However, as a start, there is a minor problem with the math in your code. $end should be equal to $page*$perpage-1, otherwise you are going to repeat something on each page. For example:

$perpage = 10;
$page = 1;
would mean
$end = 10;
$start = 0; (which will give you a total of eleven results)

$perpage = 10;
$page = 2;
would mean
$end = 20;
$start = 10; (which you already showed on page 1)

If you are looking for a way to find out the total number of records that would be returned, you're not going to be able to do so with your current SQL query. You are limiting your results to 10 (or 11) records with each execution of the SQL query.

In order to find the total, you are going to need to remove the LIMIT phrase from the SQL query and you should probably use the COUNT property in your SQL query rather than trying to use mysql_num_rows.

Therefore, your second SQL query would look something like "SELECT COUNT(*) totalrows FROM dbname WHERE 1=1..." without the limit at the end. Then, you would access the total number of rows by pulling the "totalrows" key out of the result.

[gilgalbiblewhee]

It's still not working.

I removed the LIMIT and it's showing a blank page:

PHP Code:

<?php
//paging
$page = isset($_GET['page']) ? $_GET['page'] : 1;  //or you can use the ternary operator 
$perpage = 10;// number of records per page
$end = $page*$perpage; // present page
$start=($page-1)*$perpage;
/******************************************************************************/
$sql="SELECT count(*)totalrows  FROM bible WHERE 1=1 AND";

$j=0;
//to sort out all words with length less than 4 like AND, OR, BUT...
for ($i=0; $i < count($searchTheseArr); $i++){
    if(strlen($searchTheseArr[$i]) > 4){
        $newSearchTheseArr[$j] = $searchTheseArr[$i];
//j renumbers the words of length 4 and up
        $j++;
    }
}
//sql searches only length 4 and up
for ($j=0; $j < count($newSearchTheseArr); $j++){
    $sql.=" text_data LIKE '%" .$newSearchTheseArr[$j]. "%'";
    if($j!=count($newSearchTheseArr)-1){
        $sql.=" OR";
    }else{
//removes the OR from the last line and replaces with the following
        //$sql.= " LIMIT " . $start . ", " . $perpage;
    }
}    
    
//echo count($newSearchTheseArr)."<br />";
echo $sql."<br />";
?>
<span style="font-weight: bold;">PAGE <?php echo $page; ?></span>
<span style="">Go to: <select id='selectTextPage' name='selectTextPage' size= "1" style="width: 100px;" onChange='changeTextPage(this.value);' >
<?php
/*not sure if these are useful*/
$myarray = explode('?', $_SERVER["REQUEST_URI"]);
foreach($myarray as $value){
    $urlsnip = $value;
}
foreach($myarray as $value){
    $Pageurlsnip = $value;
}
/*********************/
?>
<?php
//in case the url shows no page
if($perpage == 0){
    $perpage = 1;
}

$numOfPages = $end / $perpage;
//echo "<option selected>Select the Page</option>";
for ($i=1; $i <= $numOfPages; $i++) {
    $Pageurlexpl = explode("?", $Pageurlsnip);

    echo "<option ";?>value="searchLinkFiles/linkTextareaSearchResult.php?<?php
    for ($k=0; $k<=count($Pageurlexpl)-2; $k++){
      echo $Pageurlexpl[$k]."&";
    }    
    echo "page=" . $i;
    echo "&searchthese=";
    for ($j=0; $j < count($newSearchTheseArr); $j++){
        echo $newSearchTheseArr[$j]."%20";
    }
?>"><?php 
    echo "Page " . $i . "</option><br />\n";
}
?>
    </select></span>
    </span><br />

[gilgalbiblewhee]

I'm getting frustrated at this. This is not working:

PHP Code:

//paging
$page = isset($_GET['page']) ? $_GET['page'] : 1;  //or you can use the ternary operator 

//$resultspertpage = 10;
$perpage = 10;// number of records per page
$end = $page*$perpage; // present page
$start=($page-1)*$perpage;
/******************************************************************************/
//$sql="SELECT count(*)totalrows  FROM bible WHERE 1=1 AND";
$sql="SELECT * FROM bible WHERE 1=1 AND";
$j=0;
//to sort out all words with length less than 4 like AND, OR, BUT...
for ($i=0; $i < count($searchTheseArr); $i++){
    if(strlen($searchTheseArr[$i]) > 4){
        $newSearchTheseArr[$j] = $searchTheseArr[$i];
//j renumbers the words of length 4 and up        
        $j++;
    }
}
//sql searches only length 4 and up
for ($j=0; $j < count($newSearchTheseArr); $j++){
    $sql.=" CASE WHEN text_data LIKE '%" .$newSearchTheseArr[$j]. "%' THEN 1 ELSE 0 END";
    //$sql.=" text_data LIKE '%" .$newSearchTheseArr[$j]. "%'";
    if($j!=count($newSearchTheseArr)-1){
        $sql.=" +";
        //$sql.=" OR";
    }else{
//removes the OR from the last line and replaces with the following    
        $sql.= " > 3";
        $sql.= " LIMIT " . $start . ", " . $perpage;
    }
} 


[curtiss]

Alright.

To diagnose SQL queries, the first step is to print out the query you're trying to execute somewhere in your code.

Then, when you print out the SQL query, copy it and try to execute it either through the command line or through a utility like PHPMyAdmin.

If you get the result you're looking for, then the problem must be in the way you're writing your PHP. If you don't get the result you're looking for, then you need to rewrite your SQL query.

You need to split up the contributing factors to find out where the problem actually lies.

[gilgalbiblewhee]

Here's an example:

SELECT * FROM bible WHERE 1=1 AND CASE WHEN text_data LIKE '%bring%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%Aaron%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%remnant%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%offering%' THEN 1 ELSE 0 END + CASE WHEN text_data LIKE '%Aaron%' THEN 1 ELSE 0 END > 3 LIMIT 0, 10

[curtiss]

Have you run that query through PHP MyAdmin or the command line SQL client? If so, what results did you get?

[gilgalbiblewhee]

No I didn't. I don't know how that works. But let me be more specific. The option only shows page 1 nothing else. I put an a tag to go to the next page but the next page shows error. So I have:

javascript: var page=2; var searchthese="bring+Aaron+remnant+offering+Aaron"; aTextPage();

I'm using ajax to draw the page in a tag, so I vared the page. And it shows no error in the error console. It must be a problem in the sql.

Maybe this:
$page = isset($_GET['page']) ? $_GET['page'] : 1; //or you can use the ternary operator

[gilgalbiblewhee]

Originally Posted by curtiss

I'm sorry, Gil, but once again I am really having trouble understanding what question you are asking.

However, as a start, there is a minor problem with the math in your code. $end should be equal to $page*$perpage-1, otherwise you are going to repeat something on each page. For example:

$perpage = 10;
$page = 1;
would mean
$end = 10;
$start = 0; (which will give you a total of eleven results)

$perpage = 10;
$page = 2;
would mean
$end = 20;
$start = 10; (which you already showed on page 1)

If you are looking for a way to find out the total number of records that would be returned, you're not going to be able to do so with your current SQL query. You are limiting your results to 10 (or 11) records with each execution of the SQL query.

In order to find the total, you are going to need to remove the LIMIT phrase from the SQL query and you should probably use the COUNT property in your SQL query rather than trying to use mysql_num_rows.

Therefore, your second SQL query would look something like "SELECT COUNT(*) totalrows FROM dbname WHERE 1=1..." without the limit at the end. Then, you would access the total number of rows by pulling the "totalrows" key out of the result.

What is totalrows? I removed:
$sql.= " LIMIT " . $start . ", " . $perpage;
and inserted:
$sql="SELECT count(*) totalrows FROM bible WHERE 1=1 AND";

But got 1: as an answer.

[gilgalbiblewhee]

So what's wrong?

[curtiss]

Originally Posted by gilgalbiblewhee

I put an a tag to go to the next page but the next page shows error.

What is the error that it's showing?

Which method are you using for your AJAX request? Our you using POST or GET?

[gilgalbiblewhee]

Originally Posted by curtiss

What is the error that it's showing?

Which method are you using for your AJAX request? Our you using POST or GET?

I use the GET method. The error says:

SELECT * FROM bible WHERE 1=1 AND
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

on Page 2.

Page 1's sql says:

SELECT * 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 LIMIT 0, 10

[curtiss]

I'm assuming that, in your AJAX function you are sending the "searchthese" var through the GET request, right?

If so, do you have code like the following in your PHP file?

if(isset($_GET['searchthese'])) {
$searchTheseArr = explode('+',$_GET['searchthese']);
}

[gilgalbiblewhee]

are you saying that it's the $searchthese that's giving the problem?

I'm thinking that it's the absence of the total record count that messing up the query. How should the syntax be with count() in the sql? Because knowing that ... I think I have to divide by 10 (per page) which would show the number of pages. That's what I need fixed.

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

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:

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):

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.