0

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;
?>

1 Answer 1

1

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
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks so much! Worked great.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.