Page 1 of 1

export your tables in one click

Posted: Mon Jul 07, 2014 8:59 am
by toyo
This code is very handy when you want to export tables from your database with just a click. It is exported in xls format.

NOTE: Change all "//t" to "/t" and all "//r" to "/r".
Please if you find any bug, fix it and repost the code. I luv this forum...

Code: Select all

<?php
    function export_excel_csv()
    {
        $conn = mysql_connect("localhost","root","");
        $db = mysql_select_db("database",$conn);
       
        $sql = "SELECT * FROM table";
        $rec = mysql_query($sql) or die (mysql_error());
       
        $num_fields = mysql_num_fields($rec);
       
        for($i = 0; $i < $num_fields; $i++ )
        {
            $header .= mysql_field_name($rec,$i)."\\t";
        }
       
        while($row = mysql_fetch_row($rec))
        {
            $line = '';
            foreach($row as $value)
            {                                           
                if((!isset($value)) || ($value == ""))
                {
                    $value = "\\t";
                }
                else
                {
                    $value = str_replace( '"' , '""' , $value );
                    $value = '"' . $value . '"' . "\\t";
                }
                $line .= $value;
            }
            $data .= trim( $line ) . "\\n";
        }
       
        $data = str_replace("\\r" , "" , $data);
       
        if ($data == "")
        {
            $data = "\\n No Record Found!\n";                       
        }
       
        header("Content-type: application/octet-stream");
        header("Content-Disposition: attachment; filename=reports.xls");
        header("Pragma: no-cache");
        header("Expires: 0");
        print "$header\\n$data";
    }
    ?>
What you need to do is…
1) Copy above function and paste it into your file.
2) Change MYSQL connection settings in mysql_connect("localhost","root","").
3) Change database name in mysql_select_db("database",$conn)
4) Change table name in $sql = "SELECT * FROM table".
5) Thats it.

Re: export your tables in one click

Posted: Mon Jul 07, 2014 9:07 am
by Celauran
You're using mysql_ functions, which are deprecated. You're hardcoding credentials into the function. You're establishing your connection inside the function, meaning a new connection will be created every time the function is called. You're using die() so if something goes wrong with the query, your whole application stops working. Your function prints rather than returning values.

Re: export your tables in one click

Posted: Mon Jul 07, 2014 9:29 am
by Celauran
I just threw this together very quickly, so it definitely has plenty of room for improvement -- no error handling at all, for instance -- but this is, I think, a better approach.

Code: Select all

class DBExport {
	protected $pdo;

	public function __construct(\PDO $pdo) {
		$this->pdo = $pdo;
	}

	public function exportTable($table_name) {
		$columns = $this->getColumnNames($table_name);
		$columns_list = implode(', ', $columns);

		$query = "SELECT {$columns_list} FROM {$table_name}";
		$stmt = $this->pdo->prepare($query);
		$exec = $stmt->execute();

		$data = [];
		if ($exec) {
			while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
				$data[] = $row;
			}
		}

		$results = [
			'columns' => $columns,
			'data' => $data,
		];

		return $results;
	}

	protected function getColumnNames($table_name) {
		$query = "DESCRIBE {$table_name}";
		$stmt = $this->pdo->prepare($query);
		$exec = $stmt->execute();

		$columns = [];
		if ($exec) {
			$columns = $stmt->fetchAll(\PDO::FETCH_COLUMN);
		}

		return $columns;
	}
}