Create a new database connection and using this connection for the current browser

[wardrobe]

The database is MySQL
I would like to ask if it is possible to create a new database connection (new connection ID: XX). This connection (ID:XX) will be used for the different pages within the same browser.

If the user open another new browser, it will create another database connection (ID:YY).

mysql_pconnect does not work because it will use the same connection for all browser. I want to have a specific connection for each browser.

Thanks for your response in advance!

[Drakla]

One of the flags for [man]mysql_connect[/man] is for a new connection even when you supply the same login details.

I'm not sure if you mean different connections within the same script, though. That is the same running instance of the script. Can you explain the problem a bit more.

[MarkR]

The shared-nothing model that PHP uses does not allow you to reserve connections for an individual user. You should either connect and disconnect on every hit, or find an alternative way of doing it.

Mark

[wardrobe]

Thanks for the response first!

Let me explain the problem in detail.

When the user accesses the webpage first time, the page will have a new database connection (new connection ID). And this connection should be kept to use for the whole site with the same browser. For example, the user accesses the pageA -> pageB -> pageC -> pageD. The database connection id should be identical for visiting all the A, B, C & D pages. (mysql_thread_id()).

The reason to use the same database connection is that the system will copy a lot of data from the table to the memory (temp heap table) in the first page (A). It is needed to keep the same connection for the rest of pages (B, C & D) to get the data from the temp table. The temp table will be dropped automatically by the system when the connection is lost.

Is it possible to do so?

[Roger_Ramjet]

No. Connections are closed as soon as the script finishes

Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution.

Persistent connections will not work either

People who aren't thoroughly familiar with the way web servers work and distribute the load may mistake persistent connects for what they're not. In particular, they do not give you an ability to open 'user sessions' on the same link, they do not give you an ability to build up a transaction efficiently, and they don't do a whole lot of other things. In fact, to be extremely clear about the subject, persistent connections don't give you any functionality that wasn't possible with their non-persistent brothers.

Additionally, even if you could somehow reserve a connection, the resources associated with it would not be available

Due to the reference-counting system introduced with PHP 4's Zend Engine, it is automatically detected when a resource is no longer referred to (just like Java). When this is the case, all resources that were in use for this resource are made free by the garbage collector.

Bottom line is that you will have to try another approach.

I should point out that there is some hope for you, the mysql query cache

From version 4.0.1 on, MySQL Server features a query cache. When in use, the query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.

So it depends on the nature of your app as to whether the cache will solve your problem.

[MarkR]

No, it is not possible! You can't reserve a connection for an individual user. Persistent connections are an optimisation, and ideally should provide no different semantics from non-persistent ones. Ideally, temporary tables would be dropped even when using persistent connections (which may or may not happen in reality).

This is simply not how PHP works. You should not use a temporary table for this purpose - use a permanent table instead, and put the user ID into a column so that you can distinguish data belonging to different users.

Mark

[wardrobe]

Thanks for all the responses.

I will try to figure out other ways to solve the problem.

[Roger_Ramjet]

Mark's solution looks best. The only drawback to that is detecting when the user has finished so that you can clear the data. If you have room enough for it then you can just leave it there untill their next visit and just replace it using the ON DUPLICATE KEY UPDATE in the insert query.