PHP and SQL to generate CSV.

[chaturvedi]

hi,

 

      The following attached is the csv file i have to make.

 

where 1,2,3..11 are the days of month and blank are the sundays.

 

I could get the unique fields i.e. emp_id, emp_name, proj_name, proj_id , activity etc...in the format specified above but am having problem in gettin hours spent in each project

daily throughout the month in horizontal row. i.e. corresponding to the project name. The following is my code:

 

 

<?

 

session_start();

 

include("SessionCheck.php");

 

include("Connection.php");

 

 

    $stdt1=$_GET['sdate'];

 

  $enddt1=$_GET['edate'];

 

$empid=$_GET['eid'];

 

 

 

$s1="Select * from emp_master where emp_no='$empid'";

 

$r1=mysql_query($s1);

 

$row=mysql_fetch_row($r1);

 

$empnm=$row[1];

 

 

 

?>

 

 

<?

 

    $host = 'localhost'; 

    $csv_terminated = "\n";

    $csv_separator = ",";

    $csv_enclosed = '"';

    $csv_escaped = "\\";

 

    // $result = mysql_query("Select distinct emp_no, emp_name, proj_id, proj_nm from tres having proj_nm=(Select wrkhrs from tres) where emp_no='$empid' and dt>='$stdt1' and dt<='$enddt1'");

  $result = mysql_query("Select distinct emp_no, emp_name, proj_id, proj_nm from tres where emp_no='$empid' and dt>='$stdt1' and dt<='$enddt1'");

      $fields_cnt = mysql_num_fields($result);

      $schema_insert = '';

 

//For the headings from SQL table

 

  for ($i = 0; $i < $fields_cnt; $i++)

    {

        $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,

            stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;

        $schema_insert .= $l;

        $schema_insert .= $csv_separator;         

 

    } // end for

 

 

    $out = trim(substr($schema_insert, 0, -1));

    $out .= $csv_terminated;

 

 

    // Format the data

    while ($row = mysql_fetch_array($result))

    {

 

        $schema_insert = '';

        for ($j = 0; $j < $fields_cnt; $j++)

        {

            if ($row[$j] == '0' || $row[$j] != '')

            {

               

                if ($csv_enclosed == '')

                {

                    $schema_insert .= $row[$j];

                } else

                {

                    $schema_insert .= $csv_enclosed .

str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;              

                }

            } else

            {

                $schema_insert .= '';

            }

 

            if ($j < $fields_cnt - 1)

            {

                $schema_insert .= $csv_separator;

            }

        } // end for

 

        $out .= $schema_insert;

        $out .= $csv_terminated;

 

    } // end while

 

while($row = mysql_fetch_array())

 

 

 

$filename = $file."_".date("Y-m-d_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 $out;

exit;

?>

 

and the table in SQL consists of all the fields in vertical.

 

 

 

Please consider the environment before you print / Merci de penser à l'environnement avant d'imprimer

 

 

 

[attachment deleted by admin]