Mark Hensler — Tue, 21 Jan 2003 17:18:34 +0000

Yes, and yes. With the "LIMIT $start,100 " actually being part of the SQL for your query. The rest of that line was just not even pseudo-code.

Busy — Tue, 21 Jan 2003 09:18:09 +0000

Has to be the second option.

you have:
list($total_records) = mysql_fetch_row($result);
and
if ($total_results > ($start+100)) add 'next' link to ($pg + 1)

Is the $total_records and $total_results meant to be the same thing?
Also
"get search results from database ( LIMIT $start,100 )"
is from what I had above which would be a query - $query = "SELECT * FROM ...
Is this what you meant?

Mark Hensler — Sun, 19 Jan 2003 21:07:13 +0000

Quote: Originally posted by Busy
Is there a way to combine the two queries (find total results amount but only display limited results without selecting everything from the databse and using PHP to sort and display them.

If you're on a mySQL version 4 server, you could use SQL_CALC_FOUND_ROWS (look for FOUND_ROWS() near the bottom)

If your not on a mySQL4 server, you'll probably want to do something like this:
$query = "SELECT count(id) FROM table_name WHERE exact_same_where_clause";
$result = mysql_query($query);
list($total_records) = mysql_fetch_row($result);

if (isset($pg)) {

$start = (($pg-1) * 100);
get search results from database ( LIMIT $start,100 )
display results using a while or for statement
if ($pg > 1) add 'previous' link to ($pg - 1)
if ($total_results > ($start+100)) add 'next' link to ($pg + 1)

}
else {

display all

}

Busy — Sat, 18 Jan 2003 09:59:31 +0000

OK me again

Still trying to get this thing to work (been adding and subtracting stuff) got it so it kind of works but it needs two queries to the database to work, here is what I'm trying to do in english

if ($pg==2){

get search results from database starting at result #100 to #200
display results using a while or for statement
add previous button (pg1)
if total overall results is more than 200 add link to pg3

}
elseif ($pg==3){

get search results from database starting at result #200 to #300
display results using a while or for statement
add previous button (pg2)
if total overall results is more than 300 add link to pg4

}

....

else {

display all
}

the above works except I can't get total number of results and the limited results to display.
The pg* only has 100 results with max of 5 pages where as the pg1 or default is 200 results per page and 12 pages

Is there a way to combine the two queries (find total results amount but only display limited results without selecting everything from the databse and using PHP to sort and display them.

Also is there an easier way to do the above (english code), I didnt post the actual code as it's way to big to post here

Mark Hensler — Fri, 10 Jan 2003 01:21:27 +0000

Instead of a large switch, use a formula...

<?php
$perpage = 10;
if (!isset($page)) {
    $page = 0;
}

$ded = $page * $perpage;
?>

Busy — Thu, 09 Jan 2003 23:48:46 +0000

Got "page contains no data" after adding the above code.
What I have works for two sections but I can't get it to carry on for more than one page, here is what I had:

<?php
   $data = mysql_query($result);
   
echo mysql_error();
$fi = 0;
$columns = 2;

$num_rows = mysql_num_rows($data);
//echo $num_rows;

if ($num_rows <= 10){ $ded = $num_rows; } 
        else { $ded = 10; }
echo \"\n<table border=\\"1\\" width=\\"100%\\">\";


for($i = 0; $i < $ded; $i++) {
    $row = mysql_fetch_array($data);
    if($i % $columns == 0) {

        echo \"<tr>\n\";
    }     $fi++; 
                    $auth = $row[\"desc\"];
        $num = $row[\"id\"];

    echo \"<td><a href=\\"view.php?id=\" . $num . \"\\">\" 
. $auth . \"</a><br></td>\n\";
    if(($i % $columns) == ($columns - 1) || ($i + 1) == $num_rows) 
{
        echo \"</tr>\"; }
}
echo \"</table>\n\";   

//this is set to display all of whats left for the moment
if ($num_rows >= \"10\"){ $ded = ($num_rows - 10); } 
            else { $ded = ($num_rows - 10); }
echo \"\n<table border=\\"1\\" width=\\"100%\\">\";

$i = 0;
for($i = 0; $i < $ded; $i++) {
    $row = mysql_fetch_array($data);
    if($i % $columns == 0) {

        echo \"<tr>\n\";
    }     $fi++; 
                    $auth = $row[\"desc\"];
        $num = $row[\"id\"];

    echo \"<td><a href=\\"view.php?id=\" . $num . \"\\">\" 
. $auth . \"</a><br></td>\n\";
    if(($i % $columns) == ($columns - 1) || ($i + 1) == $num_rows) 
{
        echo \"</tr>\"; }
}
echo \"</table>\n\";

}else{ just list all of them }
?>

I found this bit of code at hotscripts (I think it was there) and changed to suit (was only one display table). the code above will display the first table with only 10 items (set them low for testing) and the second table displays everything else with a blank if there is an odd number. I can limit the results of this second table but as mentioned can't carry it on to another page without doing ten lots of the above, or doing a long switch statement depending on the page number, ie: page==1 $ded = 10, page ==2 $ded = 20 ...

Busy — Thu, 09 Jan 2003 21:21:30 +0000

Thanks I'll try the above and let you know what happens but might end up doing it at least 10 times as the results will vary between under 1000 to over 2500 results with only 200 being displayed each time.

Mark Hensler — Thu, 09 Jan 2003 17:52:43 +0000

It depends on how you want your tables to be structured and the data to be sorted...
Because you want the groups of 20 to be in columns, I've sorted (or printed) the results down then over.

(code not tested)

<?php
output:
+--------------------------------------+--------------------------------------+
| +----------------------------------+ | +----------------------------------+ |
| | 1                                | | | 6                                | |
| +----------------------------------+ | +----------------------------------+ |
| | 2                                | | | 7                                | |
| +----------------------------------+ | +----------------------------------+ |
| | 3                                | | | 8                                | |
| +----------------------------------+ | +----------------------------------+ |
| | 4                                | | | 9                                | |
| +----------------------------------+ | +----------------------------------+ |
| | 5                                | | | 10                               | |
| +----------------------------------+ | +----------------------------------+ |
+--------------------------------------+--------------------------------------+



// get $result here

// right now, we haven't printed anything
$j=0;

// loop while there are more results, and we have printed less than 80 results
//
// second table is optional, and is only printed if there are more than
// 40 results
while ((mysql_num_rows($result) > $j) && ($j < 80)) {
    echo \"<table >\n\";
    echo \" <tr>\n\";
    echo \"  <td>\n\";
    
    // left colum
    echo \"\n\";
    echo \"<table >\n\";
    // we require 20 rows, fill with results while we have them
    for ($i=1; $i<=20; $i++) {
        echo \" <tr>\n\";
        if ($tmp = @mysql_fetch_array($result)) {
            echo \"  <td>$tmp[field1]</td>\n\";
        }
        else {
            echo \"  <td>&nbsp;</td>\n\";
        }
        echo \" </tr>\n\";
    }
    echo \"</table >\n\";
    echo \"\n\";
    
    echo \"  </td>\n\";
    echo \"  <td>\n\";
    
    // right colum
    echo \"\n\";
    echo \"<table >\n\";
    // we require 20 rows, fill with results while we have them
    for ($i=21; $i<=40; $i++) {
        echo \" <tr>\n\";
        if ($tmp = @mysql_fetch_array($result)) {
            echo \"  <td>$tmp[field1]</td>\n\";
        }
        else {
            echo \"  <td>&nbsp;</td>\n\";
        }
        echo \" </tr>\n\";
    }
    echo \"</table >\n\";
    echo \"\n\";
    
    echo \"  </td>\n\";
    echo \" </tr>\n\";
    echo \"</table>\n\";
    echo \"\n\";
    echo \"<br>\n\";
    echo \"\n\";
    
    // we've now printed 40 rows
    $j+=$i;
}
?>

The next one is more tricky. We could use a series of mysql_data_seek() calls. But I think using an array is more efficient, so I'll use that.

<?php
output:
+--------------------------------------+--------------------------------------+
| 1                                    | 6                                    |
+--------------------------------------+--------------------------------------+
| 2                                    | 7                                    |
+--------------------------------------+--------------------------------------+
| 3                                    | 8                                    |
+--------------------------------------+--------------------------------------+
| 4                                    | 9                                    |
+--------------------------------------+--------------------------------------+
| 5                                    | 10                                   |
+--------------------------------------+--------------------------------------+



// get $result here

// right now, we haven't printed anything
$j=0;

// loop while there are more results, and we have printed less than 80 results
//
// second table is optional, and is only printed if there are more than
// 40 results
while ((mysql_num_rows($result) > $j) && ($j < 80)) {
    // we require 40 rows, fill with results while we have them
    $cache = array();  // clear our cache
    for ($i=1; $i<=40; $i++) {
        // ternary operator
        $cache[] = ($tmp = @mysql_fetch_array($result)) ? $tmp : NULL;
    }
    
    echo \"<table >\n\";
    
    // left colum
    // we require 20 rows, fill with results while we have them
    for ($i=1; $i<=20; $i++) {
        echo \" <tr>\n\";
        if ($cache[$i] == NULL) {
            echo \"  <td>&nbsp;</td>\n\";
        }
        else {
            echo \"  <td>\".$cache[$i][field1].\"</td>\n\";
        }
        if ($cache[{$i+20}] == NULL) {
            echo \"  <td>&nbsp;</td>\n\";
        }
        else {
            echo \"  <td>\".$cache[{$i+20}][field1].\"</td>\n\";
        }
        echo \" </tr>\n\";
    }
    
    echo \"</table>\n\";
    echo \"\n\";
    echo \"<br>\n\";
    echo \"\n\";
    
    // we've now printed 40 rows
    $j+=$i;
}
?>

ternary opperator