Ishaque Javed Ishaque Javed - 1 month ago 14
MySQL Question

How to add headers in CSV when using into out file

I am trying to generate CSV with custom headers like USRNAME , USER PROFILE , EMAIL .

This is the code I am using .

$path = getcwd() . '/uploads/data.csv' ;
$sql = "select username, CONCAT('<a href =\"$profile',username,'\">',username,'</a>') as profile_url ,email from `engine4_user` LIMIT 5000 into outfile '$path' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' ";


But I don't know how to add custom header , Or a custom row before the DB values. Please help me to add those fields .

I wants a CSV that will out put like:
USERNAME , PROFILE , EMAIL
Jonson1 , http://mysite/profile/jonson1 , jonson.test1@gmail.com
Jonson2 , http://mysite/profile/jonson2 , jonson.test2@gmail.com
Jonson3 , http://mysite/profile/jonson3 , jonson.test3@gmail.com
Jonson4 , http://mysite/profile/jonson4 , jonson.test4@gmail.com

Now I generate :
Jonson1 , http://mysite/profile/jonson1 , jonson.test1@gmail.com
Jonson2 , http://mysite/profile/jonson2 , jonson.test2@gmail.com
Jonson3 , http://mysite/profile/jonson3 , jonson.test3@gmail.com
Jonson4 , http://mysite/profile/jonson4 , jonson.test4@gmail.com

I need to add
USERNAME , PROFILE , EMAIL


Thank You in advance .

Answer

Well I have found 2 ways to do it .

1st:way

After generating the CSV I rewrite it .

$file = fopen($path,'r+'); //This to open the CSV
fputcsv($file,array(USRNAME , USER PROFILE , EMAIL)); //This to write a new row

2nd way(by using the UNION ALL) like this:

$sql = "SELECT 'New user name', 'profile name', 'story title', 'story link', 'link to delete article'
            UNION ALL
            SELECT `engine4_users`.username,
            CONCAT(`engine4_users`.username,'[$profile',`engine4_users`.username,']') as profile_url , `engine4_blog_blogs`.title,
            CONCAT('$blog_url',`engine4_users`.user_id,'\/',`engine4_blog_blogs`.blog_id) as blog_url ,
            CONCAT('$blog_url','delete/',`engine4_blog_blogs`.blog_id) as delete_url
            FROM engine4_blog_blogs INNER JOIN `engine4_users` ON `engine4_blog_blogs`.owner_id = `engine4_users`.user_id WHERE `engine4_users`.level_id = 4 AND `engine4_blog_blogs`.creation_date >= '$prevDate' into outfile '$path' FIELDS TERMINATED BY ','  ENCLOSED BY '\"'  LINES TERMINATED BY '\n\r' ";

I am using the 2nd way for sure using query directly is the best way ever :)

Comments