1

I currently have the following script that someone else wrote for me that exports custom Magento data to a CSV file. It takes data from the two tables 'perfume_fragrance' & 'perfume_fragrance_blend' and adds it to the customer's email address.

I would like to add a further column to the CSV file that includes the customer's newsletter subscription status.

I've found the 'newsletter_subscriber' table which has the 'customer_id' and 'subscriber_status' but am not sure how to add it to the SQL query.

<?php
ini_set('display_errors', 1); 
error_reporting(E_ALL);
date_default_timezone_set('Europe/London');

chdir(dirname($_SERVER['SCRIPT_FILENAME']));

$db = mysqli_connect("localhost", "removed", "removed", "removed");

$data = array();
$data[] = array('Email', 'Fragrance', 'Location', 'Date', 'Creator', 'Blend');

$sql = "SELECT CE.*, PF.fragrance_id, PF.fragrance_name, PF.created_at, PF.created_on, PF.created_by FROM customer_entity CE JOIN perfume_fragrance PF ON PF.customer_id = CE.entity_id AND PF.created_at = '- PA EVENT'";
$result = $db->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {

        $sql3 = "SELECT * FROM perfume_fragrance_blend WHERE fragrance_id = ".$row["fragrance_id"];
        $result3 = $db->query($sql3);
        if ($result3->num_rows > 0) {
            $blend_desc = '';
            $i = 1;
            while($row3 = $result3->fetch_assoc()) {
                $blend_desc .= $row3['blend_name'].' x '.$row3['blend_qty'];
                if($i < $result3->num_rows){ $blend_desc .= ', '; } 
                $i++;                   
            }
            //echo "id: " . $row["entity_id"]. " - Email: " . $row["email"]. " - Fragrance: " . $row["fragrance_name"]. " - Location: " . $row["created_at"]. " - Date: " . $row["created_on"]. " - Creator: " . $row["created_by"]. " - Blend: " . $blend_desc."<br>";
            $data[] = array($row["email"], $row["fragrance_name"], $row["created_at"], $row["created_on"], $row["created_by"], $blend_desc);
        }

    }
}

echo "Beginning CSV Creation...<br />";

$fp = fopen('database.csv', 'w');

foreach ($data as $fields) {
    fputcsv($fp, $fields);
}

fclose($fp);

echo "Script Finished";
0

You should join the newsletter_subscriber table on the customer_id. I would suggest a left join since there can be customers without newsletter subscriptions.

Replace your 3 lines with the following code (first are CSV header, second is the main SQL statement and third is the csv line):

$data[] = array('Email', 'Fragrance', 'Location', 'Date', 'Creator', 'Blend','Status');

$sql = "SELECT CE.*, PF.fragrance_id, PF.fragrance_name, PF.created_at, PF.created_on, PF.created_by, NL.subscriber_status FROM customer_entity CE 
JOIN perfume_fragrance PF ON PF.customer_id = CE.entity_id AND PF.created_at = '- PA EVENT'
LEFT JOIN newsletter_subscriber NL on CE.entity_id = NL.customer_id";


$data[] = array($row["email"], $row["fragrance_name"], $row["created_at"], $row["created_on"], $row["created_by"], $blend_desc, $row["subscriber_status"]);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.