Sessions and Temporary Tables

mconway

I have been successful in using CREATE TEMPORARY TABLE in mysql to establish a temp table in a script. I have read that using sessions, this table can be made available for as long as the session lasts (rather than upon exiting the script). The question is, what do I have to register as a session to be able to access the table from another script? I am assuming that I need to use mysql_pconnect to ensure that I am not closing the connection on exiting the script. I am also assuming that using session_start in the next script is not in anyway affecting access to the temp table.

A matter of curiosity, where is the table actually stored for the duration of its use?

iamchris

what is the syntax on your create table statement? I am attempting to create a temp table but I don't seem to have the proper PHP syntax to do it. I have the MySQL part, but not sure how to embed that into a php statement. Help?

ahundiak

I think you may have heard wrong. A TEMPORARY table only lasts as long as the current connection.

While using mysql_pconnect does avoid closing the connection, there is no assurance that the next page will get the same connection. In fact, most of the time you won't.

What you need to do is to use the session id (or some other session unique bit of data) when generating the name for your table. Then just create a table with the session unique name.

mconway

For: iamchris

This is it:

$query = "CREATE TEMPORARY TABLE IF NOT EXISTS Table_Name (
col_name1 VARCHAR(15) NOT NULL,
col_name2 VARCHAR(6) NOT NULL,
col_name3 VARCHAR(7) NOT NULL
)";

// Run the query created above on the database through
// the connection
if (!($result = @ mysql_query ($query, $connection)))
showerror();

Understand that you will not be able to see the table from the command line in mysql since manipulating the table or adding contents are restricted to the user who established the connection and created the table (in my case noted by the unique session) and only within the script itself. I suppose you will need a cookie or session as a unique identifier of the user who created the table - but I am not certain.

Understand that my grasp of this subject is not firm, so take the following with a grain of salt: IF NOT EXISTS will prevent the table from being stomped on when you reaccess the script - and prevents an error making it impossible to proceed. You would probably not want the user to be able to CREATE a new table (supposing the other table data used to fill it has been altered) each time the script is accessed after the first instance since it would waste valuable processing time. I'm also not certain IGNORE or REPLACE works with the CREATE TABLE statement.

Therefore, you may need to use a combination of mysql_list_tables and mysql_tablename to determine if the table exists and (if it does) route to a query that INSERTs contents, UPDATEs contents, REPLACEs contents, or ALTERS the table rather than creating it again (you would remove the IF NOT EXISTS in doing this).
Note: After creating the table, you will have to INSERT contents, etc., as well as display results within this script. My question was how to access this table outside the script with the help of sessions, but I have recieved no replies.

If you want to immediately create a table using select column attributes and data contents from another table(s) you can use something like:

$query = "CREATE TEMPORARY TABLE IF NOT EXISTS Table_Name SELECT from Other_Table(s)
col_name1,
col_name2,
col_name3
WHERE col_name1 =\"" . $value1 . "\"
AND col_name2 =\"" . $value2 . "\"
AND col_name3 =\" . $value3 . "\"
GROUP BY col_name1 ORDER BY col_name2";

// Run the query created above on the database through
// the connection
if (!($result = @ mysql_query ($query, $connection)))
showerror();

Hope this helps.