Is there a way to make a result cursor persist through subsequent page calls? I want to "select * from sometable" , create formatted page for the current row, then be able to navigate forward and backward without executing the query again. I think I can do it using frames, but I would like to know if it can be done using one recursive page.
TIA,
chris gonzalez
You can't.
MySQL doesn't do cursors, and HTTP is stateless.
What if I selected 1M records and spent 4 hours working with them.
The database would keep all 1M records in it's buffer (and locked probably) for 4 hours.... ouch.
You could try to use 'temp'-tables, but then you'd be using mostly old data.
Instead, make your database faster. Use indexes, optimize the tables. If that's not enough, ditch mysql and use postgres.
Vince, thanks for your informed reply. What do you think about using frames? I can call the sql query in one frame and display, for example, a person's name which could serve as a link to another frame. The values could be passed encoded in the URL. In theory, it should be faster, because the record set (which I call cursor, is that incorrect?) is only called once.
Temporarily saving your data in a frame would save you query-time to get the results, but it will also cost you time to upload the records into the frame.
My biggest concern is that when you store results in a frameset, or anywhere outside the database, you are nolonger up-to-date.
The records in the frameset are outdated as soon as you edit a single one of them.
This might be ok if you are the single user, but with several users you'lll probably soon find that you are interfering with eachother.
Would this work even so? How do you maintain a connection to the same PHP script?
You don't.
Example:
frameset with two frames.
Left frame contains a list of records (with all the data of that record hidden in javascript vars)
Clicking on a record in the left frame sends the data to the right frame. There you can edit it and press submit. the right frame then sends the modified data to the server.
Now to get to the next record you don't have to run query on the server to get the data because it's already stored in the left frame.
Note: this is most definately NOT something I would ever recommend to do. It's just a technical possibility.
OIC. Indeed, now I see what you mean about the stale query data.
True, the query data would not be up-to-the-minute, but would be entirely appropriate for data that is non-volatile (i.e. a school roster, family tree, etc...). Definitely not recommended for timely information, like stocks! But even eBay is not 100% live data...
would be entirely appropriate for data that is non-volatile i.e. a school roster,
Huh? A school roster is most definitely volatile any time a student enters or leaves. But I think a more telling point is this: if you have to load the entire query into the non-displaying frame, why not just load it all into the displaying frame and thus (magic! hurray!) you don't need the frames at all?
Well, you don't want to load it all into the displaying windows because if everything is in the same displaying frame, then submitting that frame will magically make all your data disappear, which kind of doodies the whole issue of storing the data in the window in the first place :-)
Huh? A school roster is most definitely volatile any time a student enters or leaves. But I think a more telling point is this: if you have to load the entire query into the non-displaying frame, why not just load it all into the displaying frame and thus (magic! hurray!) you don't need the frames at all?
--
The school example I was giving was more in the lines of a high school, not university...my point was data like that simply does not change as rapidly as others, and is not as sensitive to time. Using that example, WORST CASE; student data would most likely change on a daily level, stock data changes (unfortunately for some of us) at a faster (and is IMO more volatile) rate.
As far as loading all the data in one window, vincent answered that one quite nicely.
chris goznalez