#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    56
    Rep Power
    15
    I currently have an outfile setup as follows:

    mysql_query("SELECT first_name,last_name,company,fax from members WHERE fax IS NOT NULL INTO OUTFILE '/usr/local/etc/httpd/vhosts/mysql/pr_fax.csv'");

    I have to delete the file everytime it's executed.

    Is there a way to overwrite the outfile without physically deleting it or is there a way instead of saving on the server that it can be saved directly to your pc through a save as popup?

    Thanks
  2. #2
  3. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,646
    Rep Power
    4492
    From the manual:

    The file is created on the server host and cannot already exist (among other things, this prevents database tables and files such as `/etc/passwd' from being destroyed).

    You can use uniquid() to give it a unique name, or base it on the timestamp. That way each time it saves, you don't have to worry about another file having the same name.

    Your best bet for the download would be to save the file to the server, then create a hyperlink to download the file, then maybe an option to "click here" to delete the file. Just be careful how you handle the delete so that only the files you generated are deleted.

    ---John Holmes...

    ------------------
    *************************************************************
    * The manual can probably answer 90% of your questions...
    *
    * PHP Manual. www.php.net/manual
    * MySQL Manual: www.mysql.com/documentation/mysql/bychapter
    *************************************************************
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    56
    Rep Power
    15
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by SepodatiCreations:
    From the manual:

    The file is created on the server host and cannot already exist (among other things, this prevents database tables and files such as `/etc/passwd' from being destroyed).

    You can use uniquid() to give it a unique name, or base it on the timestamp. That way each time it saves, you don't have to worry about another file having the same name.

    Your best bet for the download would be to save the file to the server, then create a hyperlink to download the file, then maybe an option to "click here" to delete the file. Just be careful how you handle the delete so that only the files you generated are deleted.

    ---John Holmes...

    [/quote]

    Thanks for the reply. I've determined how to implement the save feature. As for the uniquid(), I've searched the manuals and also the boards and see no reference to it. If I have the query, have is the uniquid() used in place of outfile.txt?

    mysql_query("SELECT if(isnull(first_name), ' ', first_name), if(isnull(last_name), ' ', last_name),if(isnull(company), ' ', company), if(isnull(fax), ' ', fax) from members WHERE fax IS NOT NULL INTO OUTFILE '/usr/local/etc/httpd/vhosts/mysql/outfile.txt' FIELDS TERMINATED BY '|'");

    thanks
  6. #4
  7. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,646
    Rep Power
    4492
    uniqid(), sorry.
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    srand();
    $filename = md5(uniqid(rand())) . ".txt";
    $result = mysql_query("SELECT if(isnull(first_name), ' ', first_name), if(isnull(last_name), ' ', last_name),if(isnull(company), ' ', company), if(isnull(fax), ' ', fax) from members WHERE fax IS NOT NULL INTO OUTFILE '/usr/local/etc/httpd/vhosts/mysql/$filename' FIELDS TERMINATED BY '|'");
    [/code]

    That'll give you a 32 character unique filename each time you run this, that way you don't have to worry about overwriting a file.

    Why are you selecting a space if the column is null?

    ---John Holmes...
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    56
    Rep Power
    15
    Thanks so much for the help. I ended up with the following:


    $date1 = date("F d, Y");
    $filename = ($date1) ." Fax List" . ".csv";
    mysql_query("SELECT if(isnull(first_name), ' ', first_name), if(isnull(last_name), ' ', last_name),if(isnull(company), ' ', company), if(isnull(fax), ' ', fax) from members WHERE fax IS NOT NULL INTO OUTFILE '/usr/local/etc/httpd/vhosts/mylan-ir/$filename' FIELDS TERMINATED BY '|'");

    The reason I used the space is because I'm importing into an excel spreadsheet and was tired of looking at all the N when the field was empty.

    Thanks again


    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by SepodatiCreations:
    uniqid(), sorry.
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    srand();
    $filename = md5(uniqid(rand())) . ".txt";
    $result = mysql_query("SELECT if(isnull(first_name), ' ', first_name), if(isnull(last_name), ' ', last_name),if(isnull(company), ' ', company), if(isnull(fax), ' ', fax) from members WHERE fax IS NOT NULL INTO OUTFILE '/usr/local/etc/httpd/vhosts/mysql/$filename' FIELDS TERMINATED BY '|'");
    [/code]

    That'll give you a 32 character unique filename each time you run this, that way you don't have to worry about overwriting a file.

    Why are you selecting a space if the column is null?

    ---John Holmes...
    [/quote]

Similar Threads

  1. Running SELECT INTO OUTFILE as a CRON job
    By JAMIII in forum MySQL Help
    Replies: 6
    Last Post: October 14th, 2004, 07:09 AM
  2. Problem with select into outfile
    By Mr Boo in forum MySQL Help
    Replies: 8
    Last Post: December 9th, 2003, 09:25 AM
  3. Selecting info from a table to outfile
    By bryricha in forum MySQL Help
    Replies: 2
    Last Post: September 20th, 2003, 02:50 AM
  4. Exporting data from MySQL using OUTFILE
    By siric in forum MySQL Help
    Replies: 1
    Last Post: December 18th, 2002, 09:06 PM
  5. SELECT * INTO OUTFILE needs special rights?
    By Maglight in forum MySQL Help
    Replies: 1
    Last Post: July 13th, 2002, 08:59 PM

IMN logo majestic logo threadwatch logo seochat tools logo