export your tables in one click

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
toyo
Forum Commoner
Posts: 42
Joined: Thu May 15, 2014 1:27 am

export your tables in one click

Post 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.
Last edited by Celauran on Mon Jul 07, 2014 9:04 am, edited 1 time in total.
Reason: Please wrap your code in syntax tags to keep things legible.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: export your tables in one click

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: export your tables in one click

Post 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;
	}
}
Post Reply