[RESOLVED] Want to use a field name as a dump file name

jkurrle

I have a MySQL table that has files loaded as BLOBs into the table. The BLOB itself is in a field called EndUserForm. It's original name is saved in a field called EndUserFormName. If I were to run this code, for example, I get the file extracted out and saved into the tmp directory:

select EndUserForm into dumpfile "/opt/lampp/htdocs/GV-HS/tmp/exported1.txt" from ClientBillingInfo where ClientID = 1;

What I'd like to do is pull the file name out of the EndUserFormName and use it for the name of the exported file. Can I do this within the MySQL statement, or will I have to write a PHP script that first pulls the text name, then writes out the dump file? If I can do it within the script, how do I handle file names that have spaces in them?

bradgrafelman

You might be able to do it in two statements; first SELECT the filename into a variable, and then use then use something like CONCAT() to insert that filename into the path in the statement you've shown above.

jkurrle

Tried the following, but it errors out:

SELECT EndUserForm 
INTO    DUMPFILE 
CONCAT("/opt/lampp/htdocs/GV-HS/tmp/",EndUserFormName) 
FROM   ClientBillingInfo 
WHERE ClientID = 1;

What am I doing wrong?

bradgrafelman

Well I did say "might", y'know.. :o

You might instead be able to dynamically build up the SQL query (without using CONCAT) inside a variable and using MySQL's PREPARE and EXECUTE commands. A quick Google turned up this MySQL forum post that should give you an example of how these two commands are used.

jkurrle

I ended up just writing out the logic in PHP and running it that way, instead of trying to figure out the "magic bullet" in SQL. It would be nice to know, but I'm trying to get a "Hail Mary" done and don't have the time to spend playing around with it.

Thanks anyways!