Using PHP to check for first and last record in database

  • CStrauss
  • Graduate
  • Graduate
  • User avatar
  • Posts: 122
  • Loc: St. Louis MO. USA

Post 3+ Months Ago

Hello,

Im displaying my records from mysql database and 2 links one goes to next record and one goes to previous record.

My problem is trying to figure out when its at the last record and you hit next it goes back to first record and vice versa for previous. I was able to count to find out how many records I got in my database but cant find the best way to use that number do this. Mainly cause if i go by record ID as records are deleted that number will have holes in it meaning if have id 1,2,3 and delete to the id order will be 1,3.

Not sure if there is a mysql or php function to use to accomplish this if anyone has an idea please let me know im at the point of over thinking this and everything seems like mush now.

Thanks
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Can you share the bit of code that's troubling you?
  • CStrauss
  • Graduate
  • Graduate
  • User avatar
  • Posts: 122
  • Loc: St. Louis MO. USA

Post 3+ Months Ago

well basicly looking to do something like this.

using if statements to check the total number of records in database and check to see if the current record is on the last record of the database and if so if you hit next takes you to the first record.

Then if your on the first record of the database and hit the previous link it takes you to the last record. as far as code i had something like this

Code: [ Select ]
<a href="somepage.php?rec_id=<?=$rec_id + 1;?>">Next</a>

<a href="somepage.php?rec_id=<?=$rec_id - 1;?>">Previous</a>
  1. <a href="somepage.php?rec_id=<?=$rec_id + 1;?>">Next</a>
  2. <a href="somepage.php?rec_id=<?=$rec_id - 1;?>">Previous</a>


The problem I found with this that if records are deleted then errors will be generated for example I have rec_id 1, 2, 3 and I delete 2 and try to navigate from 1 there will be no + 1 to find according to the link.

Also the other problem when it gets to record 3 and I hit next instead of going back to 1 i generates errors.

So was wondering if there is a way via function or something to check if you reach the last record or first record of a record set and move to the apporiate record.
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Ahh, ok. That helps a bit.

This assumes that you've got a variable called '$count' holding the count of results (you could alternatively use a function like count on an array or there's probably a function to return size of your results object).

Code: [ Select ]
<a href="somepage.php?rec_id=<? echo ($rec_id == $count) ? 0 : $rec_id + 1; ?>">Next</a>
<a href="somepage.php?rec_id=<? echo ($rec_id == 0) ? $count : $rec_id - 1; ?>">Previous</a>
  1. <a href="somepage.php?rec_id=<? echo ($rec_id == $count) ? 0 : $rec_id + 1; ?>">Next</a>
  2. <a href="somepage.php?rec_id=<? echo ($rec_id == 0) ? $count : $rec_id - 1; ?>">Previous</a>


This also assumes that $rec_id is not a zero-based reference.
  • casablanca
  • Proficient
  • Proficient
  • User avatar
  • Posts: 481

Post 3+ Months Ago

In addition to what UPSGuy said, here's some more info that might help.

You can get the total number of records with this SQL query:
Code: [ Select ]
SELECT COUNT(*) FROM table_name

To get over the problem of deleted records, instead of using $rec_id as the record ID itself, you can let the database take care of the record numbering by using the LIMIT clause, like this:
Code: [ Select ]
SELECT * FROM table_name LIMIT $rec_id, 1
  • CStrauss
  • Graduate
  • Graduate
  • User avatar
  • Posts: 122
  • Loc: St. Louis MO. USA

Post 3+ Months Ago

First off thanks guys for your input to my problem, However I'm still dealing with two issues. In my database I purposely have a gap between my id to simulate a deleted record and interruption between the number sequence. this still causes errors when going from record 16 then the next record id is 14, mainly because its looking for record id 15 that doesn't exist.

The other issue I'm still having when I'm on the last record and hit next a error pops up because of going back to first record being displayed it looking for the next record id in the current sequence and same for when I'm on the first record being displayed and hit previous same error results.

Here is the more my actual code, its displaying images that information of the images being stored in the database.


Code: [ Select ]
    // Get Data from database
<?
    include $_SERVER['DOCUMENT_ROOT'].'/common.php';

    $num_records = @mysql_query("SELECT COUNT(*) FROM gallery") or die (mysql_error());

    $total = mysql_result($num_records,0,0);

    $img_id = $_GET['img_id'];

    $page = $_GET['page'];

    $sql = mysql_query("SELECT * FROM gallery WHERE img_id= '$img_id'");

    if(!$sql){
        echo "Error With MySQL Query: ".mysql_error();
    }

    $row = mysql_fetch_assoc($sql);

    stripslashes(extract($row));

?>
        // html and stuff to display data information is here but not typing it all out lol.
            

        // My next previous back links
        <a href="/gallery_view.php?img_id=<?= ($img_id == 0) ? $count : $img_id - 1; ?>&page=<?=$page;?>">Next</a>
        <a href="/gallery.php?page=<?=$page;?>">go back</a>
        <a href="gallery_view.php?img_id=<?= ($img_id == $total) ? 0 : $img_id + 1; ?>&page=<?=$page;?>">Previous</a>
  1.     // Get Data from database
  2. <?
  3.     include $_SERVER['DOCUMENT_ROOT'].'/common.php';
  4.     $num_records = @mysql_query("SELECT COUNT(*) FROM gallery") or die (mysql_error());
  5.     $total = mysql_result($num_records,0,0);
  6.     $img_id = $_GET['img_id'];
  7.     $page = $_GET['page'];
  8.     $sql = mysql_query("SELECT * FROM gallery WHERE img_id= '$img_id'");
  9.     if(!$sql){
  10.         echo "Error With MySQL Query: ".mysql_error();
  11.     }
  12.     $row = mysql_fetch_assoc($sql);
  13.     stripslashes(extract($row));
  14. ?>
  15.         // html and stuff to display data information is here but not typing it all out lol.
  16.             
  17.         // My next previous back links
  18.         <a href="/gallery_view.php?img_id=<?= ($img_id == 0) ? $count : $img_id - 1; ?>&page=<?=$page;?>">Next</a>
  19.         <a href="/gallery.php?page=<?=$page;?>">go back</a>
  20.         <a href="gallery_view.php?img_id=<?= ($img_id == $total) ? 0 : $img_id + 1; ?>&page=<?=$page;?>">Previous</a>


I reversed the code of UPSGUY as you can see because my images are being displayed in descending order (from last record to first) thats the only modification I made to his.

I tried casablanca's use Limit to handled deleted records and go errors still basically saying undefined variables of the elements of the array its trying to fetch.

Not sure if there is any other soultions or something else you can see to help but if so please post.

Thanks again for your help, its gotten me of in the right direction

Post Information

  • Total Posts in this topic: 6 posts
  • Users browsing this forum: No registered users and 95 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.