SELECT ... INTO variable

[cags]

SERVER VERSION:  5.1.33-community (if that makes any difference).

 

Ok, so I was doing a bit of research in order to help out another member in a different thread. The other member has now managed to resolve their problem, but I came across something I was convinced should be possible but I can't make work. To create delimited file you can use the following...

 

SELECT * FROM table INTO OUTFILE '/file.txt'
FIELDS TERMINATED BY '   ' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

 

I also know that it's possible to select something into a variable, which I'd imagine is something like..

 

SELECT * FROM table INTO var

 

Are the FIELDS TERMINATED BY, ENCLOSED BY and LINES TERMINATED BY specific to OUTFILE syntax? Or should it be possible to select the delimited file into a variable rather than stored as a file? If so how? I'm just curious. I've tried a few variations but it's probably not helping I've never used user-defined variable. I've tried...

 

mysql_query("SET @test=''") or trigger_error("Line 1 " . mysql_error(), E_USER_ERROR);
mysql_query("SELECT * FROM @test INTO test FIELDS TERMINATED BY '\\t' ENCLOSED BY '\"' LINES TERMINATED BY '\\r\\n';") or trigger_error("Line 2 " . mysql_error(), E_USER_ERROR);

But I get the error... "Line 2 You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to usenear 'FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES TERMINATED BY'\r\n'' at line". This leads me to believe that the syntax is only valid with OUTFILE, but I just thought I'd get confirmation.

 

Also whilst I'm at it, when messing about with OUTFILE I see that the file is created inside the mysql file of my localhost. What's the correct way to ensure it's put somewhere that it can then easily be served up as a file to a client PC? Checking out the documentation it suggests...

 

In that case, you should instead use a command such as mysql -e "SELECT ..." >          file_name to generate the file on the client host.       

But frankly I didn't understand it. It doesn't seem to be something that would easily be applied to PHP.

 

[mwasif]

The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host' date=' so you must have the [b']FILE privilege[/b] to use this syntax.

So the output can not be stored to a variable.

 

In PHP, you should be able to run the below command using system() or exec() and provide the output file location accessible by PHP and from there you can read the file.

mysql -e "SELECT ..." > file_name

[cags]

The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host' date=' so you must have the [b']FILE privilege[/b] to use this syntax.

So the output can not be stored to a variable.

Whilst that may in fact be true, that statement in no way answers my question. It simply states that SELECT ... INTO OUTFILE is used for storing into a file (which I already know). If you read my post you'll see that I wasn't attempting to use SELECT ... INTO OUTFILE to store it in a variable. I was attempting to use SELECT ... INTO var, which is perfectly valid syntax in and of itself, but I don't know if formatting in the same manner as OUTFILE is valid (it would seem it's not by my experiments).

 

In PHP, you should be able to run the below command using system() or exec() and provide the output file location accessible by PHP and from there you can read the file.

mysql -e "SELECT ..." > file_name

Yes I could perhaps use system to do that. But that still doesn't explain how to create a tab delimited file/variable enclosed within quotes using that particular syntax, which was the whole point of the thread.

[premiso]

I gave it a few tries and then looked that the manual more closely:

 

[iNTO OUTFILE 'file_name' export_options

      | INTO DUMPFILE 'file_name'

      | INTO var_name [, var_name]]

 

You can do into a variable name, but you cannot do with the export options, that is the major difference. So I do not believe it is possible to put this into a variable using those commands. So the answer to the other threads question is, no you cannot get it into a variable. As for the file, you just have to define the path using DOCUMENT_ROOT server variable to set the absolute path to save the file.

 

Hope that helps.

[cags]

I gave it a few tries and then looked that the manual more closely:

 

[iNTO OUTFILE 'file_name' export_options

      | INTO DUMPFILE 'file_name'

      | INTO var_name [, var_name]]

 

You can do into a variable name, but you cannot do with the export options, that is the major difference. So I do not believe it is possible to put this into a variable using those commands. So the answer to the other threads question is, no you cannot get it into a variable. As for the file, you just have to define the path using DOCUMENT_ROOT server variable to set the absolute path to save the file.

 

Hope that helps.

Ahh, good spot, I never noticed that the export option was listed only after OUTFILE like that. As for the file location...