export to csv

[AndrewSimm]

I use the paid version of this mod
https://vborg.vbsupport.ru/showthrea...ght=word+links

It has an option to use a csv file instead of a manual entry in the admincp.

What it does it coverts text into links. I have another plugin on my site that I use for a player database. Has anyone come across some good scripts for exporting out of the mysql database into a csv file? I would need to not only export but export in a certain order and insert text so I can build the link.

Any other suggestions would be great as well.

--------------- Added [DATE]1392187340[/DATE] at [TIME]1392187340[/TIME] ---------------

$sql = "SELECT `entryid`, `title` FROM `adv_dyna_entries` WHERE 1 ";

This is the query I would need but would need to build a link for the csv file.

$title,http://www.website.com/dir/entry.php?e=$entryid

[kh99]

You could try something like this:

SELECT title, CONCAT( 'http://www.website.com/dir/entry.php?e=', entryid ) AS link
FROM adv_dyna_entries
INTO OUTFILE 'out.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'

But you need to change 'out.csv' to a path where you can write a file on the server.

[Adrian Schneider]

The MySQL rarely has write access to most areas... usually just /tmp and whatever else that user is granted to.

Another option is using the mysql CLI client. It automatically writes out as TSV which is largely compatible.

mysql dbname -u username -p -e "SELECT ..." > output.tsv

So, with the above:

mysql dbname -u username -p -e "
    SELECT title
         , CONCAT('http://www.website.com/dir/entry.php?e=', entryid) AS link
      FROM adv_dyna_entries
" > output.tsv

All spreadsheet programs can load up the TSV, then you can easily export it as csv. I use this all the time to export queries and pipe directly into my spreadsheet program for manipulation.

[AndrewSimm]

Originally Posted by kh99

You could try something like this:

SELECT title, CONCAT( 'http://www.website.com/dir/entry.php?e=', entryid ) AS link
FROM adv_dyna_entries
INTO OUTFILE 'out.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'

But you need to change 'out.csv' to a path where you can write a file on the server.

Thank, this is working for me now. I can only write it to my tmp dir in root which isn't a big deal unless that dir gets purged. Next I need to put this into a cron job that removes the file and then runs the query you give me. I think I can handle that.

You saved me a lot of time, thank you.