Set column alias before exporting csv

[Russia]

Hey guys I am trying to export a csv in my script and it works perfectly. Now what I need to do, is rename the columns to clean names and proper names before fully exporting the csv.

 

Here is a picture of the database table:

 

 

Here is the code:

<?php

include_once "config.php";

$table = 'patients'; // table you want to export
$file  = 'export'; // csv name.

$result = mysql_query("SHOW COLUMNS FROM " . $table . ""); 
$i      = 0;

if (mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) {
        $csv_output .= $row['Field'] . ",";
        $i++;
    }
}
$csv_output .= "\n";
$values = mysql_query("SELECT * FROM " . $table . "");

while ($rowr = mysql_fetch_row($values)) {
    for ($j = 0; $j < $i; $j++) {
        $csv_output .= $rowr[$j] . ", ";
    }
    $csv_output .= "\n";
}

$filename = $file . "_" . date("d-m-Y_H-i", time());

header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=" . $filename . ".csv");

print $csv_output;
exit;
?>

As you can see it fetches the table names from this code:

 

$result = mysql_query("SHOW COLUMNS FROM " . $table . ""); 
$i      = 0;

if (mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) {
        $csv_output .= $row['Field'] . ",";
        $i++;
    }
}

 

Now what I am trying to do is allow it to rename the database tables from

pat_id to Patient ID

pat_fname to Patient First Name

pat_lname to Patient Last name

and so on. To make it looks neat and readable in the csv file.

 

How am I able to do that? I already tried a few codes like:

$query = 'SHOW COLUMNS pat_id AS "user_id", pat_fname AS "first name", pat_lname AS "last name" FROM ' . $table;

But it didnt work and gives off errors.

 

Could someone with some knowledge help me out please.

[litebearer]

http://php.about.com/od/learnmysql/qt/change_column_name.htm

[Russia]

I managed to figure out that part:
 

$result = mysql_query("SHOW COLUMNS FROM " . $table . ""); 


$i      = 0;

if (mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) {
        //$csv_output .= $row['Field'] . ",";
        $csv_output = "IME Date, Location, patientLast, Firstname, IMEcompany, Show Y/N, Billed";
        $i++;
    }
}

Now how do I rearange around the second part where it lists the rows, I would like the column Last Name to come before First Name and Location to come after IME Date.

 

This is the part that gets the rows:

$csv_output .= "\n";
$values = mysql_query("SELECT * FROM " . $table . "");

while ($rowr = mysql_fetch_row($values)) {
    for ($j = 0; $j < $i; $j++) {
        $csv_output .= $rowr[$j] . ", ";
    }
    $csv_output .= "\n";
}

Can I change $rowr[$j] to instead look like $rowr['pat_name'] and list in order the way I want the rows to look like.

Where would I put the

[Russia]

I managed to fix that too by changing it into

$values = mysql_query("SELECT pat_id,pat_lname,pat_fname,pat_date,pat_loc,pat_ins,pat_show FROM " . $table . "");

but one last thing I am stuck on is making it so it shows the name of the number in the offices table that is stored as a number in the patients table as pat_ins and pat_loc

 

I did:

$values = mysql_query("SELECT pat_id,pat_lname,pat_fname,pat_date,pat_loc,pat_ins,pat_show FROM patients INNER JOIN offices ON office_id = pat_loc INNER JOIN insurance ON ins_id = pat_ins ");

Could someone show me the problem?

 

Here is how the offices table looks like

Edited June 30, 2013 by Russia

[Barand]

my preferred method

Code tags appear to be FUBAR so here it is without them

 

$db = new mysqli(HOST, USERNAME, PASSWORD, DATABASE );

$sql = "SELECT
     pat_id as `Patient ID`
    ,pat_lname as `Patient Last Name`
    ,pat_fname as `Patient First Name`
    ,pat_date as `Date`
    ,office_name as `Patient Location`
    ,pat_ins
    ,pat_show
    FROM patients
        INNER JOIN offices ON office_id = pat_loc
        INNER JOIN insurance ON ins_id = pat_ins ";

sql2csv($db, $sql, 'mydata.csv', 1);


//
// function to download csv
//
function sql2csv($mysqli, $sql, $filename='', $headings=1)
{
    if (!$filename)
        $f = 'download_' . date('ymdhi') . '.csv';
    else
         $f = $filename;

    if ($fp) {
        $res = $mysqli->query($sql);
        if ($res) {
            header('Content-Type: text/csv');
            header('Content-Disposition: attachment; filename="'.$f.'"');
            header('Pragma: no-cache');
            header('Expires: 0');
            $row = $res->fetch_assoc();
            if ($headings) {
                fputcsv($fp, array_keys($row));
            }
            do {
                fputcsv($fp, $row);
            } while ($row = $res->fetch_assoc());
            
        }
        else echo "Error in query";
        fclose($fp);
    }
    
}
 

Edited June 30, 2013 by Barand

[litebearer]

Its a new stealth feature

[Russia]

Thanks for that code, but how do I join 2 tables to set a value of another table for a number of patients?