creating website

sometime we need export data to excel file. i have simple helper for write data to xls file . this script writing By Cleiton Wasen (wasenbr).

1. create file xls.php to  app/views/helpers/xls.php

2. create view templates file app/views/…/xls.thtml

setHeader('text_'.date('Y_m_d').'.xls');
// XLS Data Cell
$xls->BOF();
$xls->writeLabel(1,0,"Student Register");
$xls->writeLabel(2,0,"COURSENO : ");
$xls->writeLabel(2,1,"123");
$xls->writeLabel(3,0,"TITLE : ");
$xls->writeLabel(3,0,"BlaBlaBla");
$xls->writeLabel(4,0,"SETION : ");
$xls->writeLabel(6,0,"NO");
$xls->writeLabel(6,1,"ID");
$xls->writeLabel(6,2,"Gender");
$xls->writeLabel(6,3,"Name");
$xls->writeLabel(6,4,"Lastname");
$xls->writeLabel(7,0,"NO");
$xls->writeLabel(7,1,"ID");
$xls->writeLabel(7,2,"Gender");
$xls->writeLabel(7,3,"Name");
$xls->writeLabel(7,4,"Lastname");
$xls->EOF();

exit();
?>

this script read the numeric and writing to the sentence. the data numeric can be covert using function. if you will use this function,  you must call function with parameter included. output this script for indonesian format sentence…

terbilang.php

using function :

ajax tree,… we need that if we have parent and child field data on database. and we showing this data to tree form. ok we have simple script to do that.

follow this instruction :

1. create javascript code and place to javascript directory

ajax.js

		//

class.database.php

rows = 0;
if($_SERVER['HTTP_HOST'] == 'localhost' || ereg('^192\.168\.0\.[0-9]+$', $_SERVER['HTTP_HOST'])) {
/* Local connetion vars */
$this->host = "localhost";
$this->password = "root";
$this->user = "root";
$this->database = "database";
} else {
/* Internet connetion vars */
$this->host = "server";
$this->password = "password";
$this->user = "user";
$this->database = "database";
}
} // Method : end

function OpenLink()  { // Method : begin
$this->link = @mysql_connect($this->host,$this->user,$this->password); // or die (print "Class Database: Error while connecting to DB (link)");
if(mysql_error()) {
$this->connected = false;
$this->error = mysql_error();
} else {
}
return $this->connected;
} // Method : end

function SelectDB() { // Method : begin
if(!@mysql_select_db($this->database,$this->link)) {   //; or die (print "Class Database: Error while selecting DB");
$this->connected = false;
$this->error = mysql_error();
} else {
$this->connected = true;
}
} // Method : end

function CloseDB() { // Method : begin
mysql_close();
} // Method : end

function Query($query) { // Method : begin
$this->OpenLink();
$this->SelectDB();
$this->query = $query;
$this->result = mysql_query($query,$this->link) or die (print "Class Database: Error while executing Query");
$this->error = mysql_error();
// $rows=mysql_affected_rows();

if(ereg("SELECT",$query)) {
$this->rows = mysql_num_rows($this->result);
}
$this->CloseDB();
} // Method : end

// Return true if there was an error
function is_error() {
return (!empty($this->error)) ? true : false;
}

function fetchRow() { // Method : begin
return mysql_fetch_array($this->result);
$this->error = mysql_error();
} // Method : end

function getResult($row,$field) { // Method : begin
return mysql_result($this->result,$row,$field);
} // Method : end

function getError() { // Method : begin
return mysql_error();
} // Method : end

function getNumRows() { // Method : begin
return $this->rows;
} // Method : end

} // Class : end

?>

class.tree.php

<?phpid = $id;
$this->database = new Database();
if(!isset($this->id))
$this->id = 0;
}

// ********************** METODO LIST

function countSub($id) {
$sql =  "SELECT COUNT($this->fieldId) AS subOptions FROM $this->table";
$sql.= " WHERE $this->fieldFrom = $id;";
//echo $sql;
$this->database->rows = $this->database->getResult($this->database->query($sql),"subOptions");
}

function listSub($id) {
$sql =  "SELECT $this->fieldId,$this->fieldView FROM $this->table";
$sql.= " WHERE $this->fieldFrom = $id;";
//echo $sql;
return $this->database->query($sql);
}

function display() {
$this->listSub($this->id);
if($this->database->getNumRows() > 0) {
while($row=$this->database->fetchRow()) {

$tree_sub=new Tree($row[$this->fieldId]);
$tree_sub->countSub($row[$this->fieldId]);

echo "
";
echo (($tree_sub->database->getNumRows() > 0)?"fieldId].")\">[+]":"[-]");
echo " ".$row[$this->fieldView]."\n";
echo "
fieldId]."\" class=\"fld_ln\" style=\"display:none;\">

";
echo "


";
}
}
}

} // clase : fin

?>

3. create tree.php and tree_sub.php code

tree.php

display();
?>

tree_sub.php

display();
?>

CREATE TABLE `carpetas` (
`idCarpeta` int(11) NOT NULL auto_increment,
`chrNombre` char(20) default NULL,
`intDeCarpeta` int(11) default NULL,
`intEstado` tinyint(4) NOT NULL default '0',
PRIMARY KEY  (`idCarpeta`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `carpetas` VALUES (1, '/raiz', 0, 1);
INSERT INTO `carpetas` VALUES (2, 'primera', 1, 1);
INSERT INTO `carpetas` VALUES (3, 'tercera', 2, 1);
INSERT INTO `carpetas` VALUES (4, 'cuarta', 1, 1);
INSERT INTO `carpetas` VALUES (5, 'quinta', 2, 1);
INSERT INTO `carpetas` VALUES (6, 'sexta', 3, 1);
INSERT INTO `carpetas` VALUES (7, 'septima', 5, 1);
INSERT INTO `carpetas` VALUES (8, 'octava', 6, 1);
INSERT INTO `carpetas` VALUES (9, 'novena', 2, 1);
INSERT INTO `carpetas` VALUES (10, 'decima', 7, 1);
INSERT INTO `carpetas` VALUES (11, 'onceava', 5, 1);
INSERT INTO `carpetas` VALUES (12, 'doceava', 7, 1);
INSERT INTO `carpetas` VALUES (13, 'treceava', 12, 1);
INSERT INTO `carpetas` VALUES (14, 'catorceava', 7, 1);

6. to complete download code you can go to this link

we have discused about dumping structure table into sql file before. now we can discus about dumping data table script using php.

this is a function to export table to data sql  file :

Usage Example:

for usage, you must connect to the database, then calling _mysqldump_table_data(‘table’); and enter the  table name into parameter function.

header(‘Content-type: text/plain’);
header(‘Content-Disposition: attachment; filename=”dumpdata.sql”‘);

header script used to create file sql and saved on your directory.

Sometimes you might need a function to create sql table structure to entire sql file . This function will help you do that.

this is a function to export table to structure sql  file :

Usage Example:

for usage, you must connect to the database, then calling _mysqldump_table_structure(‘table’); and enter the  table name into parameter function.

header(‘Content-type: text/plain’);
header(‘Content-Disposition: attachment; filename=”dumpstructure.sql”‘);

header script used to create file sql and saved on your directory.

Author : Ravi Valmikam
this is script function import sql to the database

we have 2 function
1. mysql_import_file : simply imports a file as is
2. mysql_install_db : creates a given DB as well as imports a given SQL file for that DB

Usage Example:

this script is simple. you can try use on cake php by placing that function on your controller. you must call the function use $this->mysql_import_file(); if the script use on cake php controllers.