How to navigate within a MySQL resultset

Posted on 2008-11-09
Last Modified: 2013-12-13
I have the results of a MySQL query obtained using PHP as follows
  $result = $handle->query( $query );

I can navigate from first to last record with a loop using
  $item = $result->fetch_assoc();

I need to know how to navigate freely among the records in the resultset.  
I have done recordset navigation in MS Access with VB and with Java.  Does PHP/MySQL support
moving the internal record pointer?  

Are there PHP functions for operating on $result other than fetch_assoc()?

I use mysqli( ... ) to make the database connection.

Question by:crm-serv
    LVL 7

    Expert Comment

    try to use fetch_array instead of fetch_assoc to use number as index
    LVL 39

    Accepted Solution

    Fetch all the records into an array, then navigate using the array.

    $rows = array();
    while($item = $result->fetch_assoc()) $rows[] = $item;

    Yes, there are other functions, or rather "methods", as your $result variable is an instance of a class.

    You can use data_seek() to navigate the result set directly, as an alternative to fetching everything in an array.

    Author Comment

    Thank you for the response.

    How do I use 'number as an index'?  
    LVL 19

    Expert Comment

    // where $result is query executed
    $result = mysqli_query($conn, $query) or die ("Couldn't execute query.");

    current($row) // current row; initialized as first record of array automatically
    next($row) // next row
    prev($row) // previous row row
    end($row) // last row


     echo $row['fieldname']; // reference to a field within row. this loop iterated through all rows
     echo $count; // print count
     $count++; // increment count


    print_h $row[2]; // print out the second row directly

    Author Closing Comment

    I was looking for the data_seek() kind of functionality.  Thanks for the help with this.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
    Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now