Trouble setting PHP variable in if statement

Question

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!

Deebster · Accepted Answer · 2011-08-22 19:42:39Z

2

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.

answered Aug 22, 2011 at 19:42

Deebster

2,8391 gold badge26 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

stefmikhail Over a year ago

I tried both of your suggestions. Changing the query destroyed my pagination, and I have a feeling is has to do with the code I did not include that controls the pagination. I also tried what your second suggestion, and there was no problem under I placed my if statement. Perhaps my statement is wrong somehow? I'm pretty new to PHP.

Deebster Over a year ago

You need to give more information about what happened, what is your output after your changes?

stefmikhail Over a year ago

I was able to get it to work by reversing the order of the two queries and their respective variables. Am I right to surmise that since I did this, I can't use the SQL_CALC_FOUND_ROWS? I'm assuming this as $query_pag_num is now defined before $query_pag_data.

Deebster Over a year ago

That's right: you need to do the query first to populate the variable found_rows() returns

Andrej · Accepted Answer · 2011-08-22 19:38:10Z

1

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()";

answered Aug 22, 2011 at 19:38

Andrej

7,5041 gold badge22 silver badges22 bronze badges

4 Comments

stefmikhail Over a year ago

And use the if statement I already put together? Where would I place the if statement in your opinion?

stefmikhail Over a year ago

Changing the query as you suggested has destroyed my pagination. Only the Combo Box now works. Perhaps it has something to do with the PHP I did not show you.

Andrej Over a year ago

Try to use instead of $row = mysql_fetch_array($result_pag_num); $count = $row['count']; - $count = mysql_fetch_field($result_pag_num);

Andrej Over a year ago

It seems that my solution doesn't solve your problem. You can look at slideshare.net/suratbhati/… an try to choose the best variant.

Quinn · Accepted Answer · 2011-08-22 19:36:47Z

0

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.

answered Aug 22, 2011 at 19:36

Quinn

714 bronze badges

1 Comment

stefmikhail Over a year ago

I tried what you suggested, and there was no problem under I placed my if statement. Perhaps my statement is wrong somehow? I'm pretty new to PHP.

Marc B · Accepted Answer · 2011-08-22 19:38:47Z

0

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.

answered Aug 22, 2011 at 19:38

Marc B

362k44 gold badges433 silver badges508 bronze badges

2 Comments

stefmikhail Over a year ago

Gosh, I get what you're explaining... kind of. But with the code, how do I 'redo the loop' as you suggested? Also, I haven't tried this out yet, but others have suggested that I change my query to include sql_calc_found_rows and it destroys my pagination. Could this work with my existing queries the way they are?

Marc B Over a year ago

sql_calc shouldn't change anything. it just makes mysql calculate how many rows WOULD have been returned by the query, if it wasn't for the limit clause. it doesn't change actual query results at all. As for the redo bit, just replicate your query setup bits where you calculate the limits to being with, but do it was if the user had requested the last page for real (e.g. change $page to 5 instead of the 7 or whatever they requested and re-run the query with the new limit.

Collectives™ on Stack Overflow

Trouble setting PHP variable in if statement

4 Answers 4

4 Comments

4 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

4 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related