Hello Everyone,

I have written code to create Temporary Table using PHP script. Now, when I run the script, it seems to have created the temporary table. Now, my problem is when I try to do INSERT statement in PhpMyAdmin into the temporary table I create, I am getting an error message in phpMyAdmin which says table not found. The thing is I do not know if I am creating the temp tables correctly or If I am not writing the code properly. Any help would be appreciated.

<?php

error_reporting(E_ALL);
ini_set('display_errors', '1');

$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>";
echo '4';
$query_createTemporaryTable = "CREATE TEMPORARY TABLE `Basket`(
temporary_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
ArtistName VARCHAR( 20 ) ,
NAMEOfTheDVD VARCHAR( 30 )
)Engine = MyISAM"; 

$result_createtemptable = mysql_query($query_createTemporaryTable) or die(mysql_error()); 
echo $result_createtemptable;
$query_insertintotable = "INSERT INTO Basket( ArtistName, NAMEOfTheDVD) VALUES ( 'R', 'SHAWooSHANK')"; 

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

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

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

 while($row_selecttemptable = mysql_fetch_array($result_selecttemptable)) 
      echo $row_selecttemptable['ArtistName'] . ' ,' . $row_selecttemptable['NAMEOfTheDVD'];




?>

Don't temporary tables get destroyed when connection is closed? From the MySQL website:
"If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing."
By the time you check with PhpMyAdmin it's gone.
I see you have already som debug echo statements, what was output?

With mysql_pconnect() you can create temporary tables, these will not be destroyed when the script ends, because the connection will stay open, unless you don't use mysql_close() or you drop the table:

Thank you cereal for pointing that out. So if Mohamed 26 changes his connect to pconnect he should see the table in PhpMyAdmin.

No, the temporary table is visible only by the current connection, if you start a connection from another mysql client (PHPMyAdmin or just a shell) the id will be different. This means that you can have more then one temporary table Basket at the same time:

When using persistent connections, these will be cached and reused if using the same credentials.

You think I should use pconnect?

I dont understand when you mention id? I am a bit confused. Can you explain it in simpler terms?

Don't temporary tables get destroyed when connection is closed? From the MySQL website:
"If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing."
By the time you check with PhpMyAdmin it's gone.
I see you have already som debug echo statements, what was output?

Don't temporary tables get destroyed when connection is closed? From the MySQL website:
"If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing."
By the time you check with PhpMyAdmin it's gone.
I see you have already som debug echo statements, what was output?

on't temporary tables get destroyed when connection is closed? From the MySQL website:
"If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing."
By the time you check with PhpMyAdmin it's gone.
I see you have already som debug echo statements, what was output?

You mean the echo '4'. I only wrote those stements because at the time, I was not sure if PHP was running the latest version of the file.

you also have an echo at line 25 and 35, did they print?

yupppppppp and anyways thank you very much for your comments. I think my problem has been resolved. As you mentioned earlier temporary tables are meant to be deleted immediatley.

Thank You again for all your help.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.