HELP ON USING TEMP TABLES!!!

[Mohamed_26]

Hello

Temporary table is not being deleted once the connection is closed. I am not sure if the code is used correctly. Because when I run the code it just says table not found. SO, i had to create a table manually and then it worked.

Thank you

<?php
$hostname = "localhost"; 
$username = "1104107";
$password = "r940c1";
$database = "db1104107";
//echo '24';

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password);
$db = mysql_select_db($database, $dbhandle)
  or die("Unable to connect to MySQL");
//echo "Connected to MySQL<br>";

$query_createTemporaryTable = "CREATE TEMPORARY TABLE #temp(temp_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
                                                           ArtistName VARCHAR(20), NAMEOfTheDVD VARCHAR(30)"; 

$result_createtemptable = mysql_query($query_createTemporaryTable ,$dbhandle );

$query_insertintotable = "INSERT INTO temp (ArtistName, NAMEOfTheDVD) VALUES ('R', 'SHAWooSHANK')";

$result_insertintotable = mysql_query($query_insertintotable ,$dbhandle) or die(mysql_error());

$query_selecttemptable = "SELECT ArtistName,NAMEOfTheDVD FROM temp";

$result_selecttemptable = mysql_query( $query_selecttemptable,$dbhandle) or die(mysql_error());



while($row_selecttemptable = mysql_fetch_array($result_selecttemptable)){
      echo $row_selecttemptable;

  }
 mysql_close();

?>

[pritaeas]

Shouldn't that be:

CREATE TEMPORARY TABLE temp ...

Without the #

[Mohamed_26]

I only added the # recently. Anyother suggestions?

[cereal]

Maybe I know what is happening, in the other thread I suggested you to create the table manually, maybe the one you created through PHPMyAdmin was myisam instead of a temporary table. This would explain why is not deleted.

Drop it and then adjust the create query in your script, this currently won't work because the closing parenthesis is missing, so change it with this:

$query_createTemporaryTable = "CREATE TEMPORARY TABLE temp(temp_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, ArtistName VARCHAR(20), NAMEOfTheDVD VARCHAR(30))";

$result_createtemptable = mysql_query($query_createTemporaryTable , $dbhandle) or die(mysql_error());

Bye!

[Mohamed_26]

Yh, I have changed that. Its still not woring. But I will tyr to figure it out