How can i extract mail list of all my users/members

xasharma

I want to extract the mailing list of my all users. Please guide.

luceos

I faintly remember someone making or having made an extension. But I cannot see it on flarum.org/extensions

But yeah you can probably export from the database directly.

clarkwinkelmann

treyb an even safer method would be to just put that script outside of the webroot, hard-code the password (or include it from Flarum's config.php) and run the script over the command line with php scriptname.php. The same code should work just fine over the CLI. You don't even need the header() calls, and you could directly write to a file, or continue writing to PHP output and then redirect it on the shell like php scriptname.php > output.csv 😉

To address the original question, if you have a tool like phpMyAdmin, you can easily export an entire table to CSV, ODS or XLS. In phpMyAdmin, you can also run the same SQL query as suggested by treyb and then choose export at the bottom of the results page. It'll produce the same result as the PHP script.

You might want to use SELECT email FROM users WHERE is_email_confirmed = 1 query to only select users with a confirmed email address.

luceos maybe you were thinking of my Mailchimp extension? It's not compatible anymore and was for some very special use case.

xasharma

clarkwinkelmann

treyb

luceos

Is there any easy way to extract all mails of my all users from phpmyadmin ?

treyb

Here is what I used in a password restricted folder.


<?php
// Database connection parameters
$servername = "localhost";
$username = "username";
$password = "password";
$database = "dbname";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Fetch data from the database
$sql = "SELECT username, email FROM frm_users";
$result = $conn->query($sql);

// Check if there are any rows returned
if ($result->num_rows > 0) {
    // Set headers for CSV file
    header("Content-Type: text/csv");
    header("Content-Disposition: attachment; filename=mysql_data_export_" . date('Y-m-d') . ".csv");
    

// Open file handle to write to output
$output = fopen("php://output", "w");

// Write header row
fputcsv($output, array('username', 'email'));
    

// Write data rows
while($row = $result->fetch_assoc()) {
    fputcsv($output, $row);
}

// Close file handle
fclose($output);
} else {
    echo "No data found.";
}

// Close connection
$conn->close();
?>

treyb

treyb actually, I have made an update that requires you to input the DB password. I feel like this is a bit safer.


<?php
if (!isset($_POST['password'])) {
    // Show the form to enter the password
    ?>
    <!DOCTYPE html>
    <html>
    <head>
        <title>Enter Password</title>
        <script>
            function submitPassword() {
                var password = document.getElementById('password').value;
                if (password === '') {
                    alert('Please enter a password.');
                    return false;
                }
                return true;
            }
        </script>
    </head>
    <body>
        <form method="post" onsubmit="return submitPassword();">
            <label for="password">Enter Password for the DB:</label>
            <input type="password" id="password" name="password">
            <input type="submit" value="Submit">
        </form>
    </body>
    </html>
    <?php
    exit;
}

// Get the password from the form submission
$password = $_POST['password'];

// Database connection parameters
$servername = "localhost";
$username = "USERNAME";
$database = "DATABASE";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Fetch data from the database
$sql = "SELECT username, email FROM frm_users";
$result = $conn->query($sql);

// Check if there are any rows returned
if ($result->num_rows > 0) {
    // Set headers for CSV file
    header("Content-Type: text/csv");
    header("Content-Disposition: attachment; filename=mysql_data_export_" . date('Y-m-d') . ".csv");
    

// Open file handle to write to output
$output = fopen("php://output", "w");

// Write header row
fputcsv($output, array('username', 'email'));

// Write data rows
while($row = $result->fetch_assoc()) {
    fputcsv($output, $row);
}

// Close file handle
fclose($output);
} else {
    echo "No data found.";
}

// Close connection
$conn->close();
?>

Be sure to update your information here:

// Database connection parameters $servername = "localhost"; $username = "USERNAME"; $database = "DATABASE";

xasharma

treyb Sorry but its not working ...

treyb

xasharma As my script stands, you would need to visit via the browser, then enter the password for your DB, as long as the other DB credentials are saved in the php.

xasharma In phpMyAdmin, you can go into the table and hit export, then choose csv.

xasharma

treyb Thank you very much.