List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 23 2005 8:07pm
Subject:Re: Temp table doesn't seem to work
View as plain text  
Brian Dunning <brian@stripped> wrote on 10/23/2005 03:00:26 PM:

> If I say this, I get all my data:
> <?php
> $sql = "select * from myTable;";
> $result = mysql_query($sql);
> ?>
> But if I say this, I get no results at all:
> <?php
> $sql = "create temporary table xxx select * from myTable;";
> $result1 = mysql_query($sql);
> $sql = "select * from xxx";
> $result2 = mysql_query($sql);
> ?>
> Seems pretty straightforward. What am I missing?

I am not a PHP expert but it could be that mysql_query() is not using the 
same connection between invocations. From my copy of the PHP documentation

resource mysql_query ( string query [, resource link_identifier] )

mysql_query() sends a query to the currently active database on the server 
that's associated with the specified link identifier. If link_identifier 
isn't specified, the last opened link is assumed. If no link is open, the 
function tries to establish a link as if mysql_connect() was called with 
no arguments, and use it. The result of the query is buffered. 

The query string should not end with a semicolon. 

Only for SELECT,SHOW,EXPLAIN or DESCRIBE statements mysql_query() returns 
a resource identifier or FALSE if the query was not executed correctly. 
For other type of SQL statements, mysql_query() returns TRUE on success 
and FALSE on error. A non-FALSE return value means that the query was 
legal and could be executed by the server. It does not indicate anything 
about the number of rows affected or returned. It is perfectly possible 
for a query to succeed but affect no rows or return no rows. 

You should always check the return values for errors. You should also 
ensuer you are using the same, continuously open link_identifier as 
temporary tables are specific to the connection that forms them. If you 
disconnect and reconnect, you lose not just your temporary tables but any 
user-defined variables as well.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Temp table doesn't seem to workBrian Dunning23 Oct
  • Re: Temp table doesn't seem to workSGreen23 Oct
    • Re: Temp table doesn't seem to workBrian Dunning24 Oct
      • Re: Temp table doesn't seem to workSGreen24 Oct