Jump to content

Archived

This topic is now archived and is closed to further replies.

realjumper

Temporary table and persistant connection

Recommended Posts

Hi,

I am using a temporary table in my application and everything is sweet. However, it would be great to use the data in the temp table on the following page as this would make things easier for me. Because the connection is broken as the page transitions to the next page, the temp table gets dropped. So, if I have a persistant connection, am I right in thinking that the temp table won't drop until the connection closes?.....which would mean that I can use the temp table on the next page with it's data intact.

If my thinking is correct and a persistant connection will allow the temp table to remain, the next question is how would I close the persistant connection....and therefore drop the temp table.....later in my application? I notice from the manual that a persistant connection cannot be closed by using mysql_close() function, so is there another way? If not, how long does the persistant connection last ans where is this timeout set? Can I adjust the timeout?

Thanks heaps and sorry to be a pain!

Cheers,
Neil

Share this post


Link to post
Share on other sites
If I needed a table to persist across page loads, I would just create the table and drop it with a real DROP TABLE command.

You can always add some cleanup somewhere to make sure you delete old temp tables that are accidentally still around.

A persistent connection could indeed extend the life of the table, but I'm not sure if any other thread has permission to access it, and you can't be sure which connection the new page load will get from the persistent pool. You also can't be sure when or if the connection will be regenerated, dropping the table. Not solid enough to depend on.

Share this post


Link to post
Share on other sites
Thanks for the reply. Unfortunately I can't use a 'proper' table as it is populated with results depending on the users actions in answering questions as part of a test. I need to record the number of correct answers as well as the incorrect answers for each user. More importantly, I need to be able to display the users incorrect answers with the actual correct answer to the questions at the completion of the test. That way the user can see what the correct answer was. Potentially there could be 10-40 users on the same page at the same time doing the same test so the results of each users test will be unique to that user.....that's why I figured that a temporary table would be the answer....and so it seems to be, apart from the connection issue.

According to the manual, if a persistant connection exists and another connection is started, the original connection....that is the current connection, will over-ride the new connection attempt. As you suggest, this, for some reason isn't reliable. The temporary table behaves as expected, but not all the time. Sometimes it returns the expected records, sometimes it returns nothing at all, leading me to think that the connection timed out and consequently dropped the table....BUT....sometimes after a few hit's of the refresh button on the browser, the records display again as if nothing was wrong!?!?

After leaving the application idle for a while after it decides to return no records at all, I modify the 'Create Temporary Table If Not Exist...' to 'Create Temporary Table....' and I get an error say that the table exists!! So, the table exists and therefore I presume the connection is still alive....yet sometimes the correct records are returned....sometimes nothing is returned (maybe a different thread is created which can't access the table??).

As you can probably imagine....this inconsistancy does nothing to help me sort this out, so I am floundering here. If this is a connection timeout issue I need to extend the connection time, but I'm not sure where it is located or what it is called......php.ini....apache.....mysql?? However, the tests I have done so far seem to suggest that it isn't a timeout issue at all.....sooooo....if the connection hasn't timed out, why won't temporary table return results on a consistant basis?

Or.....should I abandon this idea and try and find another way....I'm confused!!! lol!!

Cheers,
Neil

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.