Temporary tables with AJAX

Hello!

I have been busting my head with a problem, that goes like this:
1. with a first ajax call to the server(apache 2.0 + php), I create a
temporary table like this:
create temporary table peter select * from some_table limit 0,0; That
creates an empty temporary table with the same structure as some_table.
I use mysql 4.023. I use php call mysql_connect to connect to the
mysql database.
2. I create a second ajax call to the server using the same
mysql_connect call with the third parameter new_link set to false and
make this query:
select count(*) as 'count' from peter; This time I receive an error
that this table does not exist.

Any ideas on where a problem could lie?

Thanks in advance.

Re: Temporary tables with AJAX

wrote in message
news:1142982130.967298.150640@u72g2000cwu.googlegroups.com.. .
> 2. I create a second ajax call to the server using the same
> mysql_connect call with the third parameter new_link set to false and
> make this query:
> select count(*) as 'count' from peter; This time I receive an error
> that this table does not exist.

Evidently the connection is being closed and reopened somehow. Temp tables
only exist in the current database connection session.

Read the explanation of persistent connections here:
http://www.php.net/manual/en/features.persistent-connections .php

If you use PHP in CGI mode, you can never get persistent connections because
the PHP script is run in a new process for every request. Resources like
database connections can't be persisted from one process to another.

If you use PHP in Apache-module mode, you aren't guaranteed that the same
Apache handler process is used from one request to another. In other words,
there is no HTTP session affinity for Apache handlers. So you may get use
new_link to avoid having to reopen a connection if that handler already had
a connection, but you won't necessarily get the _same_ connection as you
used in the previous request. You don't have any way of making successive
Ajax requests (or any HTTP requests) go to the same handler. The temp table
you created is associated with one particular connection, and that
connection is associated with only one of the Apache request handler
processes.

Note also that your temp table 'peter' _will_ persist as long as its
connection is alive, and it'll likely be meaningless to most of the clients
using that connection. The temp tables may accumulate and needlessly occupy
memory or storage. In any case, they'll rapidly contain obsolete data, and
the creater of the temp table won't have access to that connection to drop
the temp table.

To do what you want, you'd need "session affinity" to make sure successive
requests from a given HTTP client go to the same Apache handler process.
Google for "apache session affinity php". FastCGI_SA may be a solution for
you.
http://www.tfarmbruster.com/fcgi_sa.htm

You might find it's easier to rethink the way you're fetching data with
Ajax, and eliminate the need for temp tables to live from one request to the
next. And be sure to drop your temp tables when you're done with them, or
else they'll accumulate.

Regards,
Bill K.