CODEWITHSUNDEEP
phpajax
stefmikhail
stefmikhail

Reputation: 7125

Trouble setting PHP variable in if statement

I have an Ajax call to create an image gallery. There is a combo box to set the number of images per page, and pagination to navigate through the pages. It's working fine, except that if a person is on the last page, and they increase the number of images per page, it reloads to the current page number. This is a problem because there are now less pages and so the page is blank. In other words, you end up on page 7 of 5 for instance.

I reckon the problem is in the PHP as that is where the number of pages is calculated. I've thought out an if statement to deal with this:

if ($page > $no_of_paginations) {
 $page = $no_of_paginations;
}

However, I don't know where to place this. The reason being, $page needs to be defined before the mysql_query, but $no_of_paginations is defined after the query.

Any thoughts on how I can make this functional?

I'll post the pertinent code below:

<?php
$page = 0;
if(isset($_GET['page'])){
$page = (int) $_GET['page'];
}
$cur_page = $page;
$page -= 1;
if((int) $_GET['imgs'] > 0){ 
    $per_page = (int) $_GET['imgs']; 
} else { 
    $per_page = 16; 
}
$start = $per_page * $page;

include"db.php";

$query_pag_data = "SELECT `imgURL`,`imgTitle` FROM `images` ".
"ORDER BY `imgDate` DESC LIMIT $start, $per_page";

$result_pag_data = mysql_query($query_pag_data) or die('MySql Error' . mysql_error());

echo "<ul class='new_arrivals_gallery'>";
while($row = mysql_fetch_assoc($result_pag_data)) { 
    echo "<li><a target='_blank' href='new_arrivals_img/".$row['imgURL']."' class='gallery' title='".$row['imgTitle']."'><img src='new_arrivals_img/thumbnails/".$row['imgURL']."'></a></li>";
  }
echo "</ul>";   


/* --------------------------------------------- */
$query_pag_num = "SELECT COUNT(*) AS count FROM images";
$result_pag_num = mysql_query($query_pag_num);
$row = mysql_fetch_array($result_pag_num);
$count = $row['count'];
$no_of_paginations = ceil($count / $per_page);
?>

Thanks for your help!

Upvotes: 1

Views: 183

Answers (4)

Deebster
Deebster

Reputation: 2839

As you are currently doing it, there would be no problem with moving the last section to above the main query.

A better way to find the total records with MySQL is to use the SQL_CALC_FOUND_ROWS keyword on your main query (so here it is SELECT SQL_CALC_FOUND_ROWS imgURL, imgTitle FROM images WHERE etc) then you can query SELECT FOUND_ROWS() and just get the number of records found in the last query. As well as being faster and more efficient, this avoids a race condition when records are added between the two queries.

However, in this case you should probably just do the two current queries in the reverse order as your only other option is to check at the end and repeat if necessary.

Upvotes: 2

Marc B
Marc B

Reputation: 360602

Sometimes you just have to bite the bullet and do a query twice. Accept the user-provided "I want page X" value, and try to get that particular page. If it ends up being past the end of the available data, you can either say "hey, wait... that ain't right" and abort, or redo the loop and default to the last available page.

while(true) {
     $sql = "select sql_calc_found_rows .... limit $X,$Y";
     $result = mysql_query($sql) or die(mysql_error());

     $sql = "select found_rows();"; // retrieve the sql_calc_found_rows_value
     $res2 = mysql_query($sql);

     $row = mysql_fetch_array($res2);
     $total_rows = $row[0];

     if ($total_rows < $requested_row) {
        ... redo the loop and request last possible page ...
     } else {
        break;
     }
}

details on found_rows() function here.

Upvotes: 0

Andrej
Andrej

Reputation: 7504

You should use query:

$query_pag_data = "SELECT SQL_CALC_FOUND_ROWS `imgURL`,`imgTitle` FROM `images` ".
"ORDER BY `imgDate` DESC LIMIT $start, $per_page";

And instead of 

$query_pag_num = "SELECT COUNT(*) AS count FROM images";

use 

$query_pag_num = "SELECT FOUND_ROWS()";

Upvotes: 1

Quinn
Quinn

Reputation: 71

If you do the "SELECT COUNT(*) ..." query earlier in the script (at least before the other query), you will have $no_of_paginations earlier, and can use it to clamp $page to the correct range.

Upvotes: 0

Related Questions