www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 30

Thread: Not using temporary tables correctly.

Hybrid View

  1. #1
    Join Date
    Jan 2014
    Posts
    27

    Not using temporary tables correctly.

    I know this because the temp table is not deleted after closing the connection. I am using PHPMyAdmin. Any help is appreciated

    PHP Code:
    <?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();

    ?>

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,252
    What's up with the "#" in the "#temp" table name? Could that be your problem?
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Jan 2014
    Posts
    27
    umm, no i just added the '#' now. I dont think thats causing the problem

  4. #4
    Join Date
    Dec 2013
    Posts
    63
    Try to close your browser and reopen PHPMyAdmin. Does it work?

    Another idea: does your script work to the end without any error? If there is a break during execution and mysql_close isn't called connection to database still exists.

  5. #5
    Join Date
    Jan 2014
    Posts
    27
    Yh, it only gives me the error, but thats to do with the primary key. So what do you suggest?

  6. #6
    Join Date
    Jan 2014
    Posts
    27
    Let me explain, that code should create a temp table but when I run the code, its saying the table does not exist and I have to log into phpmyadmin and crreate a table and then the code works. I really dont understand why its not creating the table....UGHH!!!!!!!

  7. #7
    Join Date
    Dec 2013
    Posts
    63
    But what's the problem now? The fact that your temporary table isn't removed after running the script? If so, before calling "CREATE TEMPORARY TABLE ..." you can always call "DROP TEMPORARY TABLE IF EXISTS [table_name]". This is it?

  8. #8
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,252
    If you are truly trying to call the temp table #temp, then that statement is probably failing. Add some defensive coding and find out:
    PHP Code:
    $result_createtemptable mysql_query($query_createTemporaryTable ,$dbhandle );
    if(
    $result_createtemptable == false) {
        throw new 
    Exception("Create temp table failed.\n".mysql_error($dbhandle)."\n".$query_createTemporaryTable);

    Note: it might work if you quote the name (using back-ticks in MySQL):
    Code:
    `#temp`
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  9. #9
    Join Date
    Jan 2014
    Posts
    27
    Quote Originally Posted by NogDog View Post
    If you are truly trying to call the temp table #temp, then that statement is probably failing. Add some defensive coding and find out:
    PHP Code:
    $result_createtemptable mysql_query($query_createTemporaryTable ,$dbhandle );
    if(
    $result_createtemptable == false) {
        throw new 
    Exception("Create temp table failed.\n".mysql_error($dbhandle)."\n".$query_createTemporaryTable);

    Note: it might work if you quote the name (using back-ticks in MySQL):
    Code:
    `#temp`

    I removed the # sign becuase that was not meant to be there. I just thought it would work. Because in some of the tutorials I was going through, the #tbl_name meant temporary tables.

  10. #10
    Join Date
    Jan 2014
    Posts
    27
    However, I read that if the temp table is not dropped automatically after the connection is terminated, then the code is not written correctly. I am not sure if that's true.

    Sorry for annoying you.

  11. #11
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,252
    Well, I'm pretty confused at this point as far as knowing for sure what you have actually done. Start with the least amount of code possible to verify that you can create a temporary table, and then verify that it goes away after the connection is closed. Use a unique table name that you know for sure does not already exist in that database. Test all return values for false, and generate debug info when they are false. Make liberal use of error_log() or whatever techniques you prefer to log status info, so you can tell what's going on at each critical point in the code.

    However, the first thing to do, perhaps, is ask yourself why you are using a temp table in the first place. Are you sure it couldn't be handled more simply and efficiently using sub-queries and/or joins?
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  12. #12
    Join Date
    Jan 2014
    Posts
    27
    Cheers, Thanks for the advice. Well, unfortunately I have to use a temp table. Its part of a assignment. I have to make one.

  13. #13
    Join Date
    Dec 2013
    Posts
    63
    Before running this, call 'DROP TABLE temp' in PHPMyAdmin, because you have created table 'temp' in PHPMyAdmin, I think.

  14. #14
    Join Date
    Jan 2014
    Posts
    27
    Quote Originally Posted by blasphemy View Post
    Before running this, call 'DROP TABLE temp' in PHPMyAdmin, because you have created table 'temp' in PHPMyAdmin, I think.
    Ok, I think I should start all over again, cos I am gettin confused myself. Give me 5 to 10 mins

  15. #15
    Join Date
    Dec 2013
    Posts
    63
    You have no ending ')' in '$query_createTemporaryTable'!

    And you can't call
    Code:
    echo $row_selecttemptable;
    because '$row_selecttemptable' is an array, you can call for example:
    Code:
    echo $row_selecttemptable['ArtistName'];

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles