DZone Snippets: export code

export and import by php

ajay1kumar1 (Ajay kumar) — Mon, 16 Jun 2008 07:34:06 GMT

There are at least three ways to backup your MySQL Database :

Execute a database backup query from PHP file.
Run mysqldump using system() function.
Use phpMyAdmin to do the backup.

Execute a database backup query from PHP file

Below is an example of using SELECT INTO OUTFILE query for creating table backup :

include 'config.php';
include 'opendb.php';

$tableName = 'mypet';
$backupFile = 'backup/mypet.sql';
$query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query);

include 'closedb.php';
?>
To restore the backup you just need to run LOAD DATA INFILE query like this :

include 'config.php';
include 'opendb.php';

$tableName = 'mypet';
$backupFile = 'mypet.sql';
$query = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";
$result = mysql_query($query);

include 'closedb.php';
?>
It's a good idea to name the backup file as tablename.sql so you'll know from which table the backup file is

Run mysqldump using system() function

Changing the Bash prompt

jrobertson (James Robertson) — Fri, 11 Apr 2008 22:43:21 GMT

The following shell command changes the bash prompt from this "james@cryton:~/projects/pear_housekeeping2/housekeeping$" to this "james@cryton> "



export PS1="\n\u@\H>"

I tried the above example on Ubuntu 7.10.

Reference:
Tip: Prompt magic [ibm.com]
Bash Prompt HOWTO [tldp.org]

PHP : Exportar CSV a mySQL / Export CSV to mySQL

Ricardo (Ricardo m. García) — Mon, 23 Jul 2007 10:39:41 GMT

Cóodigo fuente / Source code :



function exportarCSV_a_mySQL($fileCSV)

{

	$registros=0;



	$ruta=$fileCSV['tmp_name'];



	if(!file_exists($ruta))

	{return false;}



	$tabla=quitar_extension($fileCSV['name']);

	

	$borra_tabla="DROP TABLE `".$tabla."`";

	mysql_query($borra_tabla);

	$f=fopen($ruta,"r");

	if($f)

	{

		echo "Guardando CSV en la BDD :
";

		$contenido=fread($f,filesize($ruta));

		fclose($f);

		$contenido=ereg_replace("\r\n", "\n" , $contenido); // convertimos windows a unix

		$lineas=explode("\n",$contenido);

		$titulo=explode(";",$lineas[0]);

		$NUM_CAMPOS=count($titulo);

		$sql_generado_para_eliminar="";

		$crear_tabla_campos="";

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

		{

		$titulo[$i]=ereg_replace("\"", "" , $titulo[$i]); // kitamos comillas

		$sql_generado_para_eliminar.=" AND `".$titulo[$i]."` =''";

		$crear_tabla_campos.="`".$titulo[$i]."` varchar(60) NOT NULL";

			if($i+1!=$NUM_CAMPOS)// si no es el ultimo , ponemos coma

			{

			$crear_tabla_campos.=",";

			}

		}

		$crear_tabla="CREATE TABLE `".$tabla."` (".$crear_tabla_campos.") ENGINE=MyISAM DEFAULT CHARSET=latin1;";

		mysql_query($crear_tabla);

		$linea=1;

		do

		{

			$insertar_titulos="";

			$insertar_campos="";

			$campo=explode(";",$lineas[$linea]);

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

			{

			$campo[$i]=ereg_replace("\"", "" , $campo[$i]);

			$insertar_titulos.=" `".$titulo[$i]."` ";

			$insertar_campos.=" '".$campo[$i]."' ";

				if($i+1!=$NUM_CAMPOS)// si no es el ultimo , ponemos coma

				{

				$insertar_titulos.=",";

				$insertar_campos.=",";

				}

			}

			$sql="INSERT INTO `".$tabla."` ( ".$insertar_titulos." ) VALUES ( ".$insertar_campos." );";

			if(mysql_query($sql))

			{

			echo ". ";

			$registros++;

			}

			else

			{echo "X ";return false;}

		$linea++;

		}while(next($lineas));



	$sql="DELETE FROM `".$tabla."` WHERE 1".$sql_generado_para_eliminar;mysql_query($sql);

	echo "
";

	return $tabla;

	}

	else

	{

	return false;

	}

}



function quitar_extension($archivo)

{

	$extension = strrchr($archivo,".");

	$pos=strpos($archivo,$extension);

	return substr($archivo,0,$pos);

}

Código ejemplo de llamada / Code exmple call :



$tabla = exportarCSV_a_mySQL($_FILES['archivo_csv']);

if($tabla)

{

echo "Export OK in mysql table : ".$tabla;

}

else

{

echo "Error in export ...";

}

Typo export to Wordpress WXR (script version)

laurelfan (Laurel Fan) — Wed, 18 Jul 2007 23:41:54 GMT

I tried to use this method here: http://snippets.dzone.com/posts/show/3264 however my typo blog was too slow and it kept timing out (which is why we're switching to wordpress anyway...). So I stole the code and hacked it up a bit so it could be run as a script.

Put this in $RAILS_ROOT/script/wp_export.rb, and run it like:



RAILS_ENV=production ./script/wp_export.rb > out.wxr

Import it with the "Import Wordpress" tool in your wordpress blog's admin interface.



#!/usr/bin/env ruby

require File.dirname(__FILE__) + '/../config/boot'

require 'environment'



require 'application'



# we need a controller to generate html output

class FakeController < ApplicationController

  include ActionView::Helpers::TextHelper

  include ActionView::Helpers::TagHelper

end

fake_controller = FakeController.new



articles = Article.find(:all)



xml = Builder::XmlMarkup.new(:target => STDOUT, :indent => 2)



xml.instruct! :xml, :version=>"1.0", :encoding=>"UTF-8"

xml.rss 'version' => "2.0",

        'xmlns:content' => "http://purl.org/rss/1.0/modules/content/",

        'xmlns:wfw' => "http://wellformedweb.org/CommentAPI/",

        'xmlns:dc' => "http://purl.org/dc/elements/1.1/",

        'xmlns:wp' => "http://wordpress.org/export/1.0/" do

  xml.channel do

    xml.title "The Robot Co-op"

    xml.link "http://www.robotcoop.com"

    xml.language "en-us"

    xml.ttl "40"

    xml.description "Robot coop blog"



    articles.each do |a|

        xml.item do

          xml.title a.title

          xml.content(:encoded) { |x| x << a.html(fake_controller, :all) }

          xml.pubDate a.published_at.rfc2822

          xml.guid "urn:uuid:{a.guid}", "isPermaLink" => "false"

          author = a.user.name rescue a.author

          xml.author author

          xml.dc :creator, author

          for category in a.categories

            xml.category category.name

          end

          for tag in a.tags

            xml.category tag.display_name

          end

          xml.wp :post_id, a.id

          xml.wp :post_date, a.published_at.strftime("%Y-%m-%d %H:%M:%S")

          xml.wp :comment_status, 'closed'

          xml.wp :ping_status, 'closed'

          xml.wp :post_name, a.permalink

          xml.wp :status, 'publish'

          xml.wp :post_parent, '0'

          xml.wp :post_type, 'post'

          for comment in a.comments

            xml.wp(:comment) do

              xml.wp :comment_id, comment.id

              xml.wp :comment_author, comment.author

              xml.wp :comment_author_email, comment.email

              xml.wp :comment_author_url, comment.url

              xml.wp :comment_author_IP, comment.ip

              xml.wp :comment_date, comment.published_at.strftime("%Y-%m-%d %H:%M:%S")

              xml.wp(:comment_content) { |x| x << comment.body }

              xml.wp :comment_approved, '1'

              xml.wp :comment_parent, '0'

            end

          end

       end

    end

  end

end

Export to Text

jdmays () — Tue, 05 Sep 2006 00:21:39 GMT

// description of your code here



// insert code here..



   1. Database db = DatabaseFactory.CreateDatabase();

   2. DBCommandWrapper selectCommandWrapper = db.GetStoredProcCommandWrapper("sp_GetLatestArticles");

   3. DataSet ds = db.ExecuteDataSet(selectCommandWrapper);

   4. StringBuilder str = new StringBuilder();

   5. for(int i=0;i<=ds.Tables[0].Rows.Count - 1; i++)

   6. {

   7. for(int j=0;j<=ds.Tables[0].Columns.Count - 1; j++)

   8. {

   9. str.Append(ds.Tables[0].Rows[i][j].ToString());

  10. }

  11. str.Append("
");

  12. }

  13. Response.Clear();

  14. Response.AddHeader("content-disposition", "attachment;filename=FileName.txt");

  15. Response.Charset = "";

  16. Response.Cache.SetCacheability(HttpCacheability.NoCache);

  17. Response.ContentType = "application/vnd.text";

  18. System.IO.StringWriter stringWrite = new System.IO.StringWriter();

  19. System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

  20. Response.Write(str.ToString());



Response.End();