how to step up and down through the result of an sql query

[ajoo]

Hi guys, I am stuck with this seemingly simple problem. 

 

I run a query on a MYSQL database and say it returns 4 rows of result. Now every where on the net I have found that this result can be stepped through using a while loop. But if i wanted to step through this loop one at a time and wait like at the end of each loop before proceeding further how can that be done? OR say I wanted to go to the 3rd result in the result set and display it and then GO BACK and display the 2nd result how can i do so. 

 

This would be a great help if someone can provide a solution 

 

Thanks all. 

[Ch0cu3r]

You can use mysqli_data_seek

[Barand]

or store the rows in an array

[mac_gyver]

i'm going to go with 'what is pagination?' for my guess, Alex

[ajoo]

Thanks guys ! I'll try thr first 2 but i am not sure I understand pagination. Would be happy if Guru mac_gyver can explain that concept a bit in detail. Thanks loads

[Ch0cu3r]

Pagination is a very common technique for displaying your results in pages rather than display them as one big long list of results. So page 1 can display results 1 - 10, page2 will display the results from 11 - 20 etc.  how many results you want shown per page is down to you.

 

Here is a basic tutorial on pagination

http://www.phpfreaks.com/tutorial/basic-pagination

Edited September 28, 2013 by Ch0cu3r

[vinny42]

Why do you want to wait between records and why do you have the need to go back an process the same record again?

 

Explain what you are working on, not what you see as a solution to the problem (because you don't know how to solve the problem, that's why you are posting)

[ajoo]

Hi vinny ! thanks for the response. OK so I was just trying to create a data entry form you know. Where an operator will key in entries to the data base via this form. He can do that one at a time. Then he may need to edit one of those entries at a later time. So he may need to search for a record and find the entry he wants to edit. The search can generate many entries in case he is not sure , say of the exact entry id or the name of the concerned person in that entry whose form he wants to edit. So a LIKE search query can generate many search entries and he may need to step thru each one before he finds the one he wishes to edit. 

 

I hope I am able to explain & make you understand the problem now. Thanks 

[ajoo]

Hi Ch0cu3r, Thanks for the hints and the tutorial. Much obliged. data_seek seems to be the way out I think. Thanks for the pointers. 

[vinny42]

So a LIKE search query can generate many search entries and he may need to step thru each one before he finds the one he wishes to edit. 

[/quote

 

Hmmm no. :-)

When you do a search you will get X results. If you have too many results to display on one page, you can use pagination to divide the results into smaller groups to make it easier to understand for the user.

But, when the user selects a record to edit, you access that record directly, by it's primary-key value. You cannot use "record 6 from the set you get when you search for 'foo'" because (at least theoretically) the resultset can change while you are waiting for the user to make a choice. Record 6 in the list may become record 7 by the time you are selecting it for editing.

 

But perhaps it's also worth saying that data-seek is something you do when your have to do something special to the resultset. That is extremely rare. If you want records 11-20 from the resultset of a query you would add LIMIT 10 OFFSET 11 to the query so you only get the records you want. You would never, ever, select everything and use data-seek to stip to the records you need. (just like you wouldn't go into the supermarket and buy all the milk they have, and when you get home, throw away all but the two cartons you needed)

[ajoo]

HI vinni,  I guess you are right. I am rather new to mysql and it never occoured to me that the record would change while we are viewing the searched subset. I'll keep that in mind and think about it and get back. 

Thanks loads

[ajoo]

Hi vinny, I checked what you said and its definitely something to watch out for. This is how i used it. I used seek to find the records with the LIKE command and then once i have the record i want to edit, I make changes to it based on its primary ID or email address which is again unique. SO that ways i get the editing done on the correct record. 

 

Do you see any issue in this now ? Your comments and thoughts are very welcome. Thanks loads.

[mac_gyver]

there's really no point in using any seek command for what you are doing.

 

your query(ies) should retrieve just the row(s) that you want.

 

if you have a LIKE query that matches more than one possible row, you would display those matching rows with edit links containing the id for each one. the user editing the information would select that particular row by clicking on one of the edit links. the edit code would use that id to only retrieve the data for that one row.

Edited October 1, 2013 by mac_gyver

[vinny42]

Perhaps it's easiest if I sum up how it's usually done :-)

 

First the user enters a search paramter.

then you run a query with LIKe (or whatever) to find the records that match the  search.

You print a list of records and if the list gets too long you use LIMIT and OFFSET to paginate the results.

Each link you print has the ID of the record in it so when the user clicks one, you can use aa WHERE to find exactly that one record and display it for editing.

When the edit is finished the form is submitted with the ID so you can save the new data.

 

So, no seeking is done at all.