CODEWITHSUNDEEP
phpmysql
JackB
JackB

Reputation: 53

Changing a table to a temporary table - php/mysqli

I have created the table that I want and how I want it. I have just realised I want to it to destroy upon session_destroy().

I know I can do this easily with CREATE TEMPORARY TABLE. When i edit the following code by adding TEMPORARY the table isnt created. It is created when TEMPORARY is not present.

$sep = '';

$sql = "CREATE TABLE `".$unique_id."`(";

$sql.= PHP_EOL;

$sql.= "`Name` VARCHAR(255) NOT NULL,".$sep;

foreach ($year_range as $year) {

  //ADD SEPERATOR BETWEEN LINES
  if ($year != end($year_range)) {
      $sep = ',';
  } else {
      $sep = '';
  }

  $sql.= "`".$year."` VARCHAR(255) NOT NULL".$sep;
}
$sql.= PHP_EOL;
$sql.=')';

I've added the TEMPORARY in and it doesnt register nor create the table. Can anyone shed any light on what I might be doing wrong? Thank you.

Upvotes: 1

Views: 72

Answers (1)

GrumpyCrouton
GrumpyCrouton

Reputation: 8621

Instead of dynamically creating tables like this, you should look into better uses of relational databases.

I think you just need 2 tables with a One to Many relationship

Something like this setup should work:

Table 1: User - basically a replacement for multiple dynamic tables. Stores an identifer for the user of the site.
Columns: unique_id (auto increment) | user (user identifier)

Table 2: years - contains a row for each year
Columns: unique_id (auto increment) | users_identifier (unique_id from users table) | year | stages_match

This utilizes something called a "foreign key". Basically, it means "this row belongs to the unique_id from another table"

You can get rid of the data the same way you delete the table now; when the user ends their session, delete all of the rows in the user table belonging to that user. If you set up the foreign key right, this would also remove all of the rows for that user from the years table.

Upvotes: 2

Related Questions