Temp table accross scripts (?)

Anon

Maybe this is a futile effort to break the rules, but I want to preserve a temporary table to be used by another script, other than the one in which it was created.

My "tool box" just doesn't have enough stuff in it yet for me to understand how to work this problem out.

Does anyone have an answere??

Thanks
DN.

Anon

What do you mean by temporary table? Do you mean a temp DB table or a template for dispalying pages?

Anon

Sorry, I should have made that more clear;

I have a mysql temporary table I want to use in more than one script. Typically a temp table would only last as long as the client connection does then its dropped. This makes it unavailable (under usual circumstances) to any other script. I was just wondering if there was an alternative method for keeping it alive and being able to use it in other scripts.

kparker

All you need is a method for generating unique valid table names. Then you can CREATE TABLE in one page, and use it elsewhere. Of course you'll have the problem of stale tables, if the user didn't persist through to the end where you remove the table, but an auxiliary table where you keep track of all the temp table names and the time of their creation should allow you to clean up old ones with a cron task.

Anon

What I ended up doing was seperating out my script into two pieces: one page accepted form input as search criteia and built the table out of those specific records, and the second script allowed the user to browse through those records with a next/previous button.

The real problem began with the next/prev action in that everytime the user clicked the 'next' button, it reiterated the ENTIRE page,i.e. if the form was on the same page as the table creation and the next/prev action, it started from the top of the page and the user would see the form again!

So, breaking them up left the next/prev operation by itself. That in turn made it hard to use a temporary table (one that exists only in memory) because the script ended the second the user clicked 'next', and thus the table ended as well.

So now I'm using 'hard' tables instead. They're created as the 'user_name, so they're unique. And a line that tests for the last record on the 'next' argument, which drops the table from the database after the user has reached the last record.

Stats: 300,000 record table being queried once per search by 400+ simultanious users which in turn creates 400+ individual tables that are in turn queried each time the user hits the 'next' or 'previous' buttons, and which are droped when the last available record is reached................

If anyone has a "resource saving" idea....please let me know.

Thanks 🙂

kparker

Perhaps your design is best for what you're doing, but I don't see why you need separate scripts. Here's how it could work:

If the page is requested via the GET method, display the form.

If the page is called with the POST method, look at the name/value of the button that got it posted. If it's next/prev, do as they suggest, otherwise you've got a brand-new query to submit. In any case, display the resulting data with Prev/Next buttons as appropriate, as well as a Start A New Search link that reloads with page via GET.

As far as the efficiency of the queries, since MySql supports LIMIT you might try that before you conclude that private or temporary tables are necessary.