CODEWITHSUNDEEP
phpmysqlcsvexport
Joe Jensen
Joe Jensen

Reputation: 13

interpret integer as string csv

I found this code to output a SQL table into CSV, which works fine, but in the table I'm pulling from we use integers to represent two different values in two different columns, e.g., '0' should read as 'All' in column A, and '0' should read as 'New' in Column B. I'm having trouble figuring out how to interpret 0 as two different things, as I can't just say '0 = X' for all values. Any insight would be appreciated: 

    <?php
    session_start();
    if (isset($_POST['export']) && !empty($_POST['export'])){
    if($_POST['export'] == 'safefilter') {

            $colnames = [
            'team' => "Team",
            'name' => "INI",
            'inidesc' => "INI Description",
            'item' => "Item",
            'itemdesc' => "Item Description",
            'newonly' => "New Only?",
            ];

            $filter = $_POST['filter'];
            $name = $filter . 'StandardFiles';

            if (in_array($filter, $_SESSION['teamarray'], true)) {

            $sql = "SELECT DISTINCT teams.team, safecheck.name, safecheck.inidesc, safecheck.item, safecheck.itemdesc, safecheck.newonly FROM safecheck INNER JOIN teams ON safecheck.teamid=teams.id WHERE team = '$filter' ORDER BY name ASC, team ASC, item ASC;";

            } elseif (in_array(strtoupper($filter), $_SESSION['iniarray'], true)) {

            $sql = "SELECT DISTINCT teams.team, safecheck.name, safecheck.inidesc, safecheck.item, safecheck.itemdesc, safecheck.newonly FROM safecheck INNER JOIN teams ON safecheck.teamid=teams.id WHERE name = '$filter' ORDER BY name ASC, team ASC, item ASC;";       

            }

    } elseif($_POST['export'] == 'authfilter') {

            $colnames = [
            'team' => "Team",
            'name' => "INI",
            'fullini' => "INI Description",
            ];

            $filter = $_POST['filter'];
            $name = $filter . 'AuthorizingTeams';

            if (in_array($filter, $_SESSION['teamarray'], true)) {

            $sql = "SELECT DISTINCT teams.team, authcheck.name, authcheck.fullini FROM authcheck INNER JOIN teams ON authcheck.teamid=teams.id WHERE team = '$filter' ORDER BY name ASC, team ASC;";

            } elseif (in_array(strtoupper($filter), $_SESSION['iniarray'], true)) {

            $sql = "SELECT DISTINCT teams.team, authcheck.name, authcheck.fullini FROM authcheck INNER JOIN teams ON authcheck.teamid=teams.id WHERE name = '$filter' ORDER BY name ASC, team ASC;";       

            }

    } elseif($_POST['export'] == 'safeview') {

            $colnames = [
            'team' => "Team",
            'name' => "INI",
            'inidesc' => "INI Description",
            'item' => "Item",
            'itemdesc' => "Item Description",
            'newonly' => "New Only?",
            ];

            $name = 'AllStandardFiles';
            $sql = "SELECT DISTINCT teams.team, safecheck.name, safecheck.inidesc, safecheck.item, safecheck.itemdesc, safecheck.newonly FROM safecheck INNER JOIN teams ON safecheck.teamid=teams.id ORDER BY name ASC, team ASC, item ASC";

    } elseif($_POST['export'] == 'auth') {

            $colnames = [
            'team' => "Team",
            'name' => "INI",
            'fullini' => "INI Description",
            ];

            $name = 'AllAuthorizingTeams';
            $sql = "SELECT DISTINCT teams.team, authcheck.name, authcheck.fullini FROM authcheck INNER JOIN teams ON authcheck.teamid=teams.id ORDER BY name ASC, team ASC;";

    }
}

function cleanData(&$str) {
if($str == 't') $str = 'TRUE';
if($str == 'f') $str = 'FALSE';
if(preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) {
  $str = "'$str";
}
if(strstr($str, "'")) $str = "'" . str_replace("'", '', $str) . "'";
}

function map_colnames($input) {
global $colnames;
return isset($colnames[$input]) ? $colnames[$input] : $input;
}

   // filename for download
   $filename = $name . date('mdY') . ".csv";

   header("Content-Disposition: attachment; filename=\"$filename\"");
   header("Content-Type: text/csv");

   $out = fopen("php://output", 'w');

$flag = false;
include('dbconnect.php');
$result = mysqli_query($conn, $sql) or die('Query failed!');

while($row = mysqli_fetch_assoc($result)) {
if(!$flag) {
  // display field/column names as first row
  $firstline = array_map(__NAMESPACE__ . '\map_colnames', array_keys($row));
  fputcsv($out, $firstline, ',', '"');
  $flag = true;
}
array_walk($row, __NAMESPACE__ . '\cleanData');
fputcsv($out, array_values($row), ',', '"');
}

fclose($out);
exit;
?>

Upvotes: 0

Views: 139

Answers (1)

Inna Tichman
Inna Tichman

Reputation: 626

in your sql statements do the following for those columns:

SELECT IF(column_name_A = 0, 'All', column_name_A) FROM your_table

SELECT IF(column_name_B = 0, 'New', column_name_B) FROM your_table

Upvotes: 1

Related Questions