Cleaning up API and DB Query Results Combined in Table with Session ID

This may also be a MySQL question. A part of it is something of a best practices question.

I have a form with PHP code that calls an API and puts the results into a MySQL table with the Session ID, then makes a DB query and puts those results in the same table with the same Session ID.

If possible, on the server side, I would like to be able to see if a session with a given Session ID is still active and, if not, delete the corresponding records from the table.

Also, I wonder if this is not a good practice. The table in question will get a lot of inserts and deletes, meaning it will become very fragmented, I think. Ideally, I would like to use a temp table for this. I would like to be able to make multiple calls to the same stored procedure to insert data into the same temp table as I loop through the results returned from the API and then from the MySQL query. But, MySQL doesn’t allow for global temp tables like MS SQL does. So, I don’t think I can do this with a temp table…or can I? Maybe a MySQL session (not the PHP session to which I referred earlier) is different from a session in MS SQL. In other words, I am under the impression that I can’t make a PHP call to a stored procedure that checks for the existence of a temp table and creates it if it doesn’t exist and writes to that same temp table on subsequent calls to the procedure. Is this correct?

Another alternative I have considered is, instead of inserting rows to a table with the Session ID each time the form is submitted and deleting them later, to dynamically create a new table with the PHP Session ID as part of the table name each time the form is submitted. Then drop the table when it is no longer needed. What are the performance implications or other implications for doing this?

Let me know if this makes sense and/or if there is another, better solution for what I am trying to accomplish.

Thanks in advance for any assistance you can offer.

-Mike

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.