Ask your WordPress questions! Pay money and get answers fast! (more info)

WP_List_Table to Array then to CSV, Datetime field not exporting WordPress

  • SOLVED

Hi There,

I have a table that is being exported to an array that pushes all the rows and data out to a CSV file. All of the data that gets pushed out in the array are varchar but the client wants the date of the submission pushed out which is in datetime format. I have put the field name into the array but it calls nothing into the rows. Any ideas?


<?php

include_once($_SERVER['DOCUMENT_ROOT'].'/wp-load.php' );

function array2csv(array &$array) {

$ja_csv_headers = array(
'date_submitted',
'job_pos',
'referred_nulsen_employee',
'referred_nulsen_employee_name',
'last_name',
'first_names',
'preferred_name',
'dob',
'your_address',
'state',
'postcode',
'daytime_phone_number',
'mobile',
'email_address',
'aboriginal_torres',
'other_languages',
'languages_list',
'legally_entitled',
'visa_details',
'visa_expire',
'drivers_license_number',
'license_state_of_issue',
'license_class',
'license_expiry_date',
'license_type',
'disqualified_driving',
'disqualified_driving_details',
'reliable_vehicle',
'criminal_record',
'criminal_record_details',
'workers_comp',
'workers_comp_details',
'medical_conditions',
'medical_conditions_details',
'hear_about_position',
'hear_about_from_where',
'dsc_worked_12_months',
'dsc_capacity_employed',
'dsc_date_terminated',
'applied_before',
'qualification_01',
'institution_01',
'year_completed_01',
'qualification_02',
'institution_02',
'year_completed_02',
'work_experience',
'shifts_anytime',
'monday',
'tuesday',
'wednesday',
'thursday',
'friday',
'saturday',
'sunday',
'public_holidays',
'experience_disability_services',
'page_url',
'page_referrer'
);

// $first = true;
$rows = '';

foreach ($ja_csv_headers as $header) {
$rows .= '"' . $header . '",';
}

$rows = substr($rows, 0, -1);
$rows .= "\n";

foreach ($array as $row) {

$application = unserialize($row->application_data);

unset($application['submit']);

/*if ($first) {
foreach ($application as $key => $value) {
$rows .= '"' . $key . '",';
}
$rows = substr($rows, 0, -1);
$rows .= "\n";
$first = false;
}

foreach ($application as $key => $value) {
$rows .= '"' . $value . '",';
}
$rows = substr($rows, 0, -1);
$rows .= "\n";
*/

foreach ($ja_csv_headers as $header) {

$value = isset($application[$header]) ? $application[$header] : '';

$rows .= '"' . $value . '",';

}

$rows = substr($rows, 0, -1);
$rows .= "\n";

}

return $rows;
}

function download_send_headers($filename) {
// disable caching
$now = gmdate("D, d M Y H:i:s");
header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
header("Last-Modified: {$now} GMT");

// force download
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");

// disposition / encoding on response body
header("Content-Disposition: attachment;filename={$filename}");
header("Content-Transfer-Encoding: binary");
}

global $wpdb;

$query = "SELECT
*
FROM wp_job_applications
";

if (isset($_GET['application']) && !empty($_GET['application'])) {
$query .= " WHERE id = '" . $_GET['application'] . "'";
}

$result = $wpdb->get_results($query);

if (isset($_GET['application']) && !empty($_GET['application'])) {
download_send_headers("job_application_" . $_GET['application'] . "_" . date("Y-m-d") . ".csv");
} else {
download_send_headers("job_applications_" . date("Y-m-d") . ".csv");
}

echo array2csv($result);
die();

Answers (3)

2015-12-01

Firoja_Imtosupport answers:

Hi Just use
echo array2csv($result);

//with
echo "<pre>";
print_r($result);


midsummas comments:

Hi Firoja,

Sorry that just makes dump like Rempty answer with <pre> appended to first row. Not a nicely laid out CSV like:

"date_submitted","job_pos","referred_nulsen_employee","referred_nulsen_employee_name","last_name","first_names","preferred_name","dob","your_address","state","postcode","daytime_phone_number","mobile","email_address","aboriginal_torres","other_languages","languages_list","legally_entitled","visa_details","visa_expire","drivers_license_number","license_state_of_issue","license_class","license_expiry_date","license_type","disqualified_driving","disqualified_driving_details","reliable_vehicle","criminal_record","criminal_record_details","workers_comp","workers_comp_details","medical_conditions","medical_conditions_details","hear_about_position","hear_about_from_where","dsc_worked_12_months","dsc_capacity_employed","dsc_date_terminated","applied_before","qualification_01","institution_01","year_completed_01","qualification_02","institution_02","year_completed_02","work_experience","shifts_anytime","monday","tuesday","wednesday","thursday","friday","saturday","sunday","public_holidays","experience_disability_services","page_url","page_referrer"
"","Support Worker Jobs","","","Goromonzi","Kudzanayi","Kudzi","09/11/1987","3 Teano Place Marangaroo","Western Australia","6064","+61 406 362 720","+61 406 362 720","[email protected]","No","No","","Yes - I have a current work permit/visa","Working Holiday Visa ","20/04/2014","G66374386876109 (Canadian)","Western Australia","Auto","08/08/2016","Full","No","","Yes","No","","No","","No","","","","","","","No","Bachelor of Commerce","University of Calgary","2011","","","","1-2 years","","","","","","","","","","No",""


Firoja_Imtosupport comments:

Hi,

Then use
echo array2csv($result);
print_r($result['date_submitted']);

Let me know what you get after it


midsummas comments:

Hi Firoja,

It echos the array alright as per normal, but 'date_submitted" columns in all rows is still empty...

Cheers,

Les


Firoja_Imtosupport comments:

Hi,

Can you import table wp_job_applications from your database and provide me .sql file.


midsummas comments:

Hi Firoja,

Here you go: https://www.dropbox.com/s/hggtjnqo6xo1gpd/wp_job_applications.sql?dl=0

Thanks,

Les


Firoja_Imtosupport comments:

Hi Les,

Thanks for providing SQl file, My analysis is: date_submitted is not stored in application data and you are using only
$application = unserialize($row->application_data); so it gives blank for date

Just see single record array once
Array
(
[0] => stdClass Object
(
[id] => 20
[job_pos] => Support Worker Jobs
[date_submitted] => 2013-07-17 07:44:16
[first_names] => Kudzanayi
[last_name] => Goromonzi
[email_address] => [email protected]
[mobile] => +61 406 362 720
[application_data] => a:42:{s:6:"submit";s:9:"apply-now";s:7:"job_pos";s:19:"Support Worker Jobs";s:9:"last_name";s:9:"Goromonzi";s:11:"first_names";s:9:"Kudzanayi";s:14:"preferred_name";s:5:"Kudzi";s:3:"dob";s:10:"09/11/1987";s:12:"your_address";s:24:"3 Teano Place Marangaroo";s:5:"state";s:17:"Western Australia";s:8:"postcode";s:4:"6064";s:20:"daytime_phone_number";s:15:"+61 406 362 720";s:6:"mobile";s:15:"+61 406 362 720";s:13:"email_address";s:21:"[email protected]";s:17:"aboriginal_torres";s:2:"No";s:15:"other_languages";s:2:"No";s:14:"languages_list";s:0:"";s:16:"legally_entitled";s:39:"Yes - I have a current work permit/visa";s:12:"visa_details";s:21:"Working Holiday Visa ";s:11:"visa_expire";s:10:"20/04/2014";s:22:"drivers_license_number";s:26:"G66374386876109 (Canadian)";s:22:"license_state_of_issue";s:17:"Western Australia";s:19:"license_expiry_date";s:10:"08/08/2016";s:13:"license_class";s:4:"Auto";s:12:"license_type";s:4:"Full";s:20:"disqualified_driving";s:2:"No";s:28:"disqualified_driving_details";s:0:"";s:16:"reliable_vehicle";s:3:"Yes";s:15:"criminal_record";s:2:"No";s:23:"criminal_record_details";s:0:"";s:12:"workers_comp";s:2:"No";s:20:"workers_comp_details";s:0:"";s:18:"medical_conditions";s:2:"No";s:26:"medical_conditions_details";s:0:"";s:14:"applied_before";s:2:"No";s:16:"qualification_01";s:20:"Bachelor of Commerce";s:14:"institution_01";s:21:"University of Calgary";s:17:"year_completed_01";s:4:"2011";s:16:"qualification_02";s:0:"";s:14:"institution_02";s:0:"";s:17:"year_completed_02";s:0:"";s:15:"work_experience";s:9:"1-2 years";s:30:"experience_disability_services";s:2:"No";s:14:"privacy_policy";s:2:"on";}
[cv_path] => cv_1374047056.doc
[cl_path] => cl_1374047056.doc
)

If you see application data you will understand


Firoja_Imtosupport comments:

Hi,

Here is your working solution, Just will have to modify some lines:

<?php
include_once($_SERVER['DOCUMENT_ROOT'].'/wp-load.php' );
function array2csv(array &$array) {



$ja_csv_headers = array(

'date_submitted',

'job_pos',

'referred_nulsen_employee',

'referred_nulsen_employee_name',

'last_name',

'first_names',

'preferred_name',

'dob',

'your_address',

'state',

'postcode',

'daytime_phone_number',

'mobile',

'email_address',

'aboriginal_torres',

'other_languages',

'languages_list',

'legally_entitled',

'visa_details',

'visa_expire',

'drivers_license_number',

'license_state_of_issue',

'license_class',

'license_expiry_date',

'license_type',

'disqualified_driving',

'disqualified_driving_details',

'reliable_vehicle',

'criminal_record',

'criminal_record_details',

'workers_comp',

'workers_comp_details',

'medical_conditions',

'medical_conditions_details',

'hear_about_position',

'hear_about_from_where',

'dsc_worked_12_months',

'dsc_capacity_employed',

'dsc_date_terminated',

'applied_before',

'qualification_01',

'institution_01',

'year_completed_01',

'qualification_02',

'institution_02',

'year_completed_02',

'work_experience',

'shifts_anytime',

'monday',

'tuesday',

'wednesday',

'thursday',

'friday',

'saturday',

'sunday',

'public_holidays',

'experience_disability_services',

'page_url',

'page_referrer'

);



// $first = true;

$rows = '';



foreach ($ja_csv_headers as $header) {

$rows .= '"' . $header . '",';

}



$rows = substr($rows, 0, -1);

$rows .= "\n";
//echo "<pre>";
//print_r($array);
//exit;

foreach ($array as $row) {

$applicationdata=addItem($row->application_data,$row->date_submitted);
//$application= unserialize($row->application_data);
$application= unserialize($applicationdata);



unset($application['submit']);



/*if ($first) {

foreach ($application as $key => $value) {

$rows .= '"' . $key . '",';

}

$rows = substr($rows, 0, -1);

$rows .= "\n";

$first = false;

}



foreach ($application as $key => $value) {

$rows .= '"' . $value . '",';

}

$rows = substr($rows, 0, -1);

$rows .= "\n";

*/



foreach ($ja_csv_headers as $header) {



$value = isset($application[$header]) ? $application[$header] : '';


$rows .= '"' . $value . '",';



}



$rows = substr($rows, 0, -1);

$rows .= "\n";



}



return $rows;

}



function download_send_headers($filename) {

// disable caching

$now = gmdate("D, d M Y H:i:s");

header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");

header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");

header("Last-Modified: {$now} GMT");



// force download

header("Content-Type: application/force-download");

header("Content-Type: application/octet-stream");

header("Content-Type: application/download");



// disposition / encoding on response body

header("Content-Disposition: attachment;filename={$filename}");

header("Content-Transfer-Encoding: binary");

}



global $wpdb;



$query = "SELECT

*

FROM wp_job_applications

";



if (isset($_GET['application']) && !empty($_GET['application'])) {

$query .= " WHERE id = '" . $_GET['application'] . "'";

}



$result = $wpdb->get_results($query);



if (isset($_GET['application']) && !empty($_GET['application'])) {

download_send_headers("job_application_" . $_GET['application'] . "_" . date("Y-m-d") . ".csv");

} else {

download_send_headers("job_applications_" . date("Y-m-d") . ".csv");

}


echo "<pre>";
echo array2csv($result);

print_r($result);
function addItem($serializedArray, $item)
{
$a = unserialize($serializedArray);
$a['date_submitted'] = $item;
return serialize($a);
}
?>


Firoja_Imtosupport comments:

Hello,

Let me know this works for you, I am very sure it will work as i tested and got response as below:

"date_submitted","job_pos","referred_nulsen_employee","referred_nulsen_employee_name","last_name","first_names","preferred_name","dob","your_address","state","postcode","daytime_phone_number","mobile","email_address","aboriginal_torres","other_languages","languages_list","legally_entitled","visa_details","visa_expire","drivers_license_number","license_state_of_issue","license_class","license_expiry_date","license_type","disqualified_driving","disqualified_driving_details","reliable_vehicle","criminal_record","criminal_record_details","workers_comp","workers_comp_details","medical_conditions","medical_conditions_details","hear_about_position","hear_about_from_where","dsc_worked_12_months","dsc_capacity_employed","dsc_date_terminated","applied_before","qualification_01","institution_01","year_completed_01","qualification_02","institution_02","year_completed_02","work_experience","shifts_anytime","monday","tuesday","wednesday","thursday","friday","saturday","sunday","public_holidays","experience_disability_services","page_url","page_referrer"
"2013-07-17 07:44:16","Support Worker Jobs","","","Goromonzi","Kudzanayi","Kudzi","09/11/1987","3 Teano Place Marangaroo","Western Australia","6064","+61 406 362 720","+61 406 362 720","[email protected]","No","No","","Yes - I have a current work permit/visa","Working Holiday Visa ","20/04/2014","G66374386876109 (Canadian)","Western Australia","Auto","08/08/2016","Full","No","","Yes","No","","No","","No","","","","","","","No","Bachelor of Commerce","University of Calgary","2011","","","","1-2 years","","","","","","","","","","No","",""

Also once you find all working remove <pre> and print_r

Thanks


midsummas comments:

Awesome Firoja,

That works exactly as it should!

Thanks you very much.

Les

2015-11-30

Rempty answers:

Check the field name for date of submission.

For test you can

//replace this
echo array2csv($result);
//with
print_r($result);

and check the 'date of submission field'


midsummas comments:

Hi Rempty,

Thanks for that, but doesn't work as it should, it now prints everything out in into 1 giant column with multiple rows instead of nicely formatted like it was before in multiple columns and rows with headers.

Can I some how use print_r just for the 'date_submitted' (correct table name) field value?

Cheers,

Les

2015-12-01

Arnav Joy answers:

Hi ,

Is your problem solved ?


midsummas comments:

Hi Arnav,

Nope not yet.

Thanks,

Les