Tables | zsygab.com

About

Posts Tagged ‘tables’

Create Excel file from PHP with tables using headers

January 5th, 2011

admin

Did You ever needed to create pure and simple Excel spreadsheets from php? Are you tired with complicated classes, large and nonsense code? Here is a tiny code sample, showing how easy it is to create Excel files directly from a php file. I won’t comment the lines, as the code is obvious…

$file_name = “myfile.xls”;

header(‘Pragma: public’);
header(‘Expires: 0′);
header(‘Cache-Control: must-revalidate, post-check=0, pre-check=0′);
header(‘Cache-Control: public’);
header(‘Content-Description: File Transfer’);
header(‘Content-Type: text/xls; name=”‘ . $file_name . ‘”‘);
header(‘Content-Disposition: attachment; filename=”‘ . $file_name . ‘”‘);
header(‘Content-Transfer-Encoding: binary’);

$content = ‘<table>
<tr>
<td>Header1 (A)</td>
<td>Header2 (B)</td>
<td>Header3 (C)</td>
</tr>
<tr>
<td>Content 1A</td>
<td>Content 1B</td>
<td>Content 1C</td>
</tr>
<tr>
<td>Content 2A</td>
<td> </td>
<td>Content 2C</td>
</tr>
</table>’;

echo $content

Posted in Programming - Computers

Tags: content, create, excel, header, php, spreadsheet, tables

No Comments »

Copy / duplicate database with PHP

January 5th, 2011

admin

How to copy / clone / duplicate an entire database, with all the tables and data contained?

Here are some definitions for start…:

define(“DB_HOST”, “localhost”);
define(“DB_DATABASE”, “is_general”);
define(“DB_DATABASE_NEW”, “is_general”);
define(“DB_USERNAME”, “general”);
define(“DB_PASSWORD”, “g3n3r@l”);

Connect to the original database:

mysql_connect(DB_HOST, DB_USERNAME, DB_PASSWORD);
mysql_select_db(DB_DATABASE);

Create the new database and add an existing user to it (in our case, general) with all privileges:

mysql_query(“CREATE DATABASE “.DB_DATABASE_NEW);
mysql_query(“GRANT ALL PRIVILEGES ON “.DB_DATABASE_NEW.”.* TO ‘general’@'%’ WITH GRANT OPTION”);

Get all the table names from the original table (here you must be careful, because if there are dependecies, as foreign keys from one table the another, it can cause some problems, as the order of the tables is important when they will be recreated):

$r = mysql_query(“SELECT Table_name,Table_rows FROM information_schema.tables WHERE TABLE_SCHEMA = ‘is_default’”);
while ($d= mysql_fetch_assoc($r))
$tables[] = $d['Table_name'];

If the database is not empty, so it contains tables:

if (is_array($tables))
foreach ($tables as $v){

Connect to the new database and create the tables with their structure:

$r = mysql_query(“SHOW CREATE TABLE `”.$v.”`”);
$d= mysql_fetch_assoc($r);
mysql_select_db(DB_DATABASE_NEW);
mysql_query($d['Create Table']);

Copy the data from original table:

$data = ”;
mysql_select_db(DB_DATABASE);
$r = mysql_query(“SELECT * FROM `”.$v.”`”);
while ($d= mysql_fetch_assoc($r))
$data[] = $d;

Paste the data into the corresponding table in the new database:

mysql_select_db(DB_DATABASE_NEW);
if (is_array($data))
foreach ($data as $d)
// and here you will insert the data “$d” into the table “$v”, as this is not truly related to this post, and you should learn from yourself too…

}