Export MySQL data to .csv using PHP

Question

I'm exporting data to a .csv file and it's working perfectly but I have one small issue. I fetch name and gender from a table but for gender I save id in my database (i.e., 1 = Male, 2 = Female). My below code gives me id for gender, how can I fix it? Return 1 for Male and 2 for Female:

$rows = mysql_query("SELECT `name`, `gender` FROM TABLE");

while ($row = mysql_fetch_assoc($rows)) {
    fputcsv($output, $row);
}

Apparently not, luckily for them someone will always post a solution anyway. — CompuChip
– CompuChip, Commented Mar 7, 2014 at 13:57

ncrocfer · Accepted Answer · 2014-03-07 13:55:20Z

6

Try this :

$rows = mysql_query("SELECT `name`, `gender` FROM TABLE");

while ($row = mysql_fetch_assoc($rows)) {

    if($row['gender'] == 1) {
        $row['gender'] = 'Male'; 
    } else {
        $row['gender'] = 'Female'; 
    }

    // Or ternary condition
    // $row['gender'] = ($row['gender'] == 1 ? 'Male' : 'Female');

    fputcsv($output, $row);
}

answered Mar 7, 2014 at 13:55

ncrocfer

2,5504 gold badges33 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Over a year ago

Ternary condition seems nicely to me @ncrocfer ;)

Ferrakkem Bhuiyan · Accepted Answer · 2014-03-07 14:03:55Z

<?php

    $db_record = 'yourRecod';
    // optional where query
    $where = 'WHERE 1 ORDER BY 1';
    // filename for export
    $csv_fileName = 'db_export_'.$db_record.'_'.date('Y-m-d').'.csv';

    // database variables
    $hostname = "localhost";
    $user = "yourUserName";
    $password = "yourPassword";
    $database = "yourDataBase";

    // Database connecten voor alle services
    mysql_connect($hostname, $user, $password)
    or die('Could not connect: ' . mysql_error());

    mysql_select_db($database)
    or die ('Could not select database ' . mysql_error());

    $csv_export = '';

    $query = mysql_query("SELECT * FROM ".$db_record." ".$where);
    $field = mysql_num_fields($query);

    // create line with field names
    for($i = 0; $i < $field; $i++) {
      $csv_export.= mysql_field_name($query,$i).';';
    }
    $csv_export.= '';
    while($row = mysql_fetch_array($query)) {
      // create line with field values
      for($i = 0; $i < $field; $i++) {
        $csv_export.= '"'.$row[mysql_field_name($query,$i)].'";';
      } 
      $csv_export.= ''; 
    }

    // Export the data and prompt a csv file for download
    header("Content-type: text/x-csv");
    header("Content-Disposition: attachment; filename=".$csv_fileName."");
    echo($csv_export);
?>

i give you full sample code which i am using to Export MySql data to .CSV using PHP

reformed · Accepted Answer · 2016-07-28 06:08:27Z

2

Use the IF statement in your SQL to minimize PHP logic:

SELECT `name`, IF(gender=1,'Male','Female') as 'gender' FROM TABLE

edited Jul 28, 2016 at 6:08

reformed

4,82813 gold badges69 silver badges94 bronze badges

answered Aug 12, 2014 at 20:36

John Ezell

722 bronze badges

Collectives™ on Stack Overflow

Export MySQL data to .csv using PHP

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related