DZone Snippets: mysql code

Compare Engines

shantanuo (shantanu oak) — Tue, 22 Jul 2008 05:47:46 GMT

// If you have 2 servers with identical database structure, and some of the tables have different engine type, then create a federated table to connect to the original server and compare the engines type with the current table's engine.



CREATE TABLE test.`TABLES2` (

`TABLE_CATALOG` varchar(512) default NULL,

`TABLE_SCHEMA` varchar(64) NOT NULL default '',

`TABLE_NAME` varchar(64) NOT NULL default '',

`TABLE_TYPE` varchar(64) NOT NULL default '',

`ENGINE` varchar(64) default NULL,

`VERSION` bigint(21) default NULL,

`ROW_FORMAT` varchar(10) default NULL,

`TABLE_ROWS` bigint(21) default NULL,

`AVG_ROW_LENGTH` bigint(21) default NULL,

`DATA_LENGTH` bigint(21) default NULL,

`MAX_DATA_LENGTH` bigint(21) default NULL,

`INDEX_LENGTH` bigint(21) default NULL,

`DATA_FREE` bigint(21) default NULL,

`AUTO_INCREMENT` bigint(21) default NULL,

`CREATE_TIME` datetime default NULL,

`UPDATE_TIME` datetime default NULL,

`CHECK_TIME` datetime default NULL,

`TABLE_COLLATION` varchar(64) default NULL,

`CHECKSUM` bigint(21) default NULL,

`CREATE_OPTIONS` varchar(255) default NULL,

`TABLE_COMMENT` varchar(80) NOT NULL default ''

)

ENGINE=FEDERATED DEFAULT CHARSET=latin1

CONNECTION='mysql://root@172.172.172.172/information_schema/TABLES';



SELECT b.TABLE_SCHEMA as remote_database, b.TABLE_NAME as remote_tableName, b.ENGINE as remote_engine, a.ENGINE AS local_engine 

FROM test.TABLES2 AS a INNER JOIN information_schema.TABLES as b 

ON a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.TABLE_NAME = b.TABLE_NAME AND a.ENGINE != b.ENGINE;

Claves Foraneas / Foreign key

Ricardo (Ricardo m. García) — Wed, 16 Jul 2008 12:18:14 GMT

Integridad de datos mediantes claves Foraneas, script que genera las SQL necesarios para garantizar la integridad entre tablas. Poco a poco vamos relacionando parejas de padre e hijo y podemos realizar un arbol de claves foraneas, cuando si borramos un registro del padre, borra y actualiza a los hijos en cascada, si no me explico bien decirme en los comentarios.



require_once("cabezera/funciones.php");

conectar_mysql();

require_once("cabezera/scripts/constantes.php");

?>









Untitled Document







$ejecutar = isset($_GET['ejecutar']) && $_GET['ejecutar']=='si';

$tabla_padre = "album";

$tabla_hijo = "fotos";

$campo_padre = "idAlbum";

$campo_hijo = "idAlbum";



$sinIntegridad = 0;



$sql="select * from ".$tabla_hijo;

$resultado = mysql_query($sql);

while($fila = mysql_fetch_array($resultado))

{

	$sql2 = "select * from ".$tabla_padre." where ".$campo_padre." = '".$fila[$campo_hijo]."'";

	//echo $sql2."
";

	$resultado2 = mysql_query($sql2);

	if(mysql_num_rows($resultado2) <= 0)

	{

		$sql3 = "DELETE FROM `".$tabla_hijo."` WHERE `".$campo_hijo."` = '".$fila[$campo_hijo]."' LIMIT 1;";

		if($ejecutar)

		{

			if(mysql_query($sql3)) echo "OK query : ".$sql3."
";

			else echo "Error en query : ".$sql3."
";

		}

		else

		{

			echo $sql3."
";

		}

		$sinIntegridad++;

	}

}



$sql1="ALTER TABLE `".$tabla_padre."` ADD INDEX ( `".$campo_padre."` ) ";

$sql2="ALTER TABLE `".$tabla_hijo."` ADD INDEX ( `".$campo_hijo."` ) ";

$sql3="ALTER TABLE ".$tabla_hijo." ADD FOREIGN KEY(".$campo_hijo.") REFERENCES ".$tabla_padre." (".$campo_padre.") ON DELETE CASCADE ON UPDATE CASCADE";

if($ejecutar)

{

	if(mysql_query($sql1)) echo "OK query : ".$sql1."
";

	else echo "Error en query : ".$sql1."
";

	

	if(mysql_query($sql2)) echo "OK query : ".$sql2."
";

	else echo "Error en query : ".$sql2."
";

	

	if(mysql_query($sql3)) echo "OK query : ".$sql3."
";

	else echo "Error en query : ".$sql3."
";

}

else

{

	echo $sql1."
";

	echo $sql2."
";

	echo $sql3."
";

}



echo "
".$sinIntegridad." filas sin integridad.
";

?>



  

  

  

  

  



/*

ALTER TABLE fotos_nueva_version ADD FOREIGN KEY(idUsuario) REFERENCES usuarios_portalcocinas (idUsuario) ON DELETE CASCADE ON UPDATE CASCADE



ALTER TABLE relacion_usuario_localidad ADD FOREIGN KEY(idUsuario) REFERENCES usuarios_portalcocinas (idUsuario) ON DELETE CASCADE ON UPDATE CASCADE





ALTER TABLE relacion_usuario_actividad ADD FOREIGN KEY(idUsuario) REFERENCES usuarios_portalcocinas (idUsuario) ON DELETE CASCADE ON UPDATE CASCADE





ALTER TABLE fotos_nueva_version DROP FOREIGN KEY idUsuario_FK;



SHOW CREATE TABLE fotos_nueva_version;

*/

?>

Query MySQL accent insensitive in latin1_general_ci

ki4ngel (Benoit Asselin) — Sat, 21 Jun 2008 10:34:59 GMT

Query MySQL accent insensitive in latin1_general_ci



SELECT * FROM `table`WHERE `text` LIKE CONVERT(_utf8 '%cinema%' USING utf8) COLLATE utf8_general_ci ;

Source: Query MySQL accent insensitive in latin1_general_ci

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

Processing large CSV files

demimismo (David Arango) — Tue, 10 Jun 2008 11:17:48 GMT



  # this method allows fast CSV processing, it reads file_name, generates a new

  # CSV file and inserts all data in a mysql table using LOAD DATA INFILE



    # you can use it like this:



    load_csv_data(PRELOAD_DIR+'socios.csv', 'users') do |csv, thing_id, row|

      csv << [thing_id,

        row['NOMBRE'].to_s.to_permalink+'.'+row['APELLIDOS'].to_s.to_permalink, #  login

        row['E_MAIL'], #  email

        row['SEXO'],  #  gender

        (row['NO_DATOS'] == 'S' || row['NO_DATOS'] == 'VERDADERO' ? 1 : 0 ), #  no_datos

      ]

    end





  def load_csv_data(file_name, table_name)

    STDERR.print("\nParsing '#{file_name}' to insert data in '#{table_name}'\n")



    n_lines = (%x[wc -l #{file_name}]).split(' ')[0].to_f

    time_start = Time.now

    begin

      require 'fastercsv'

      count = 0.0



      csv_string = FasterCSV.open("data.csv", "w", { :col_sep => ";", :force_quotes => true })  do |csv|

      FasterCSV.open("#{file_name}", { :col_sep => "\t", :headers => :first_row }).each do |row|

        thing_id = row[0].to_i

        if thing_id && thing_id > 0

          

          yield csv, thing_id, row



          percentage = ((count += 1) * 100.0)/n_lines

          STDERR.print "%.0f%%..." % percentage if (percentage.modulo(2.0) < 0.001)

        end

        end

      end

    rescue EOFError

      f.close

    end

    total_lines = (%x[wc -l data.csv]).split(' ')[0].to_i

    STDERR.print("\nInserting #{total_lines} lines in table '#{table_name}'\n")

    ActiveRecord::Base.connection.execute("LOAD DATA INFILE '#{File.join(File.dirname(__FILE__), '..', '..', 'data.csv')}' IGNORE INTO TABLE #{table_name} FIELDS TERMINATED BY ';' ENCLOSED BY '\"';")

    warn("Finished importing '#{file_name}'.\nLines: #{n_lines.to_i.to_s} (#{(n_lines.to_i - total_lines.to_i).to_s} lost)\nTime: %.2f minutes\n" % ((Time.now - time_start)/60))

  end

Getting ActiveRecord to auto reconnect after lost connection

bmarini (Ben Marini) — Mon, 19 May 2008 15:47:42 GMT

I puts this code in a file called active_record_hacks.rb in config/initializers but it could easily be a plugin.



ActiveRecord::ConnectionAdapters::MysqlAdapter.module_eval do

  def execute_with_retry_once(sql, name = nil)

    retried = false

    begin

      execute_without_retry_once(sql, name)

    rescue ActiveRecord::StatementInvalid => exception

      ActiveRecord::Base.logger.info "#{exception}, retried? #{retried}"



      # Our database connection has gone away, reconnect and retry this method

      reconnect!

      unless retried

        retried = true

        retry

      end

    end

  end



  alias_method_chain :execute, :retry_once

end

Copy MySQL table

ki4ngel (Benoit Asselin) — Tue, 13 May 2008 17:31:35 GMT



CREATE TABLE table_destination SELECT * FROM table_source ;



CREATE TABLE table_destination LIKE table_source ;

INSERT INTO table_destination SELECT * FROM table_source ;

Source: Asselin Benoit Development ( MySQL, SQL ) & Agence ici, agence de communication

PHP Dynamic Checkbox Table Creator (data retrieved from MySQL DB)

dneuma (Daniel Neumann) — Fri, 02 May 2008 12:09:34 GMT

Hi All.
This is a function for Dynamically Create a Checbox Table retrieving information for from a MySQL Database.
As it is quite commented, it's also good for learning how this things work :)
Hope you find it useful.
Feedback is welcome.
Cheers
Dan



function dynamic_checkbox_table ($sql_str, $col_label, $col_name, $val_checked="S", $cant_cols_tbl=3){

/*

	by Daniel Neumann

	this script creates dynamically permite a table containing checkboxes 

	getting the data for the checkboxes from a MySQL DB

	$sql_str, SQL select string to retrieve data from DB (see example in last comment line)

	$col_label, DB column that has values for the checkbox label 

	$col_name, DB column that has values for the checkbox name

	$val_checked="S", value when checked (value="" attribute) it uses the same value for all of them. If you whish to use a dynamic value from a DB, you should comment the line (it´s explained next to the code in the middle of the function) and de-comment the other line (check the code,. you'll understand what I mean). Also, you should use this parameter to specify the column name for the values

	$cant_cols_tbl=3, quantity of columns for the table, it defaults to 3

	usage example: dynamic_checkbox_table("SELECT * FROM keywords", "Keyword", "ID_Keywrd");

*/

	

	//connect DB and run query

	$db="MyDB";

	$db_user="MyUser";

	$pass="MyPass";

	$host="localhost";

	@mysql_connect($host,$db_user,$pass);

	@mysql_select_db($db) or die ("cannot connect to DB");

	$q_resultado = mysql_query($sql_str);

	mysql_close();

	if (mysql_num_rows($q_resultado)==0) exit("no rows returned");

	

	$next_row = mysql_fetch_array($q_resultado); //fetch first row

	

	$output = "\n"; //open table tag

	do {

		$output .= "\n"; //open row tag

		for ($i=1 ; $i <= $cant_cols_tbl ; $i++ ){ //loops as many times as $cant_cols_tbl

			$row=$next_row; //assign $row, next row will be checking next one, that avoids starting a new row when it's gonna be empty

			$output .= "\n"; //close TD

		} //close for loop

		$output .= "\n"; //close row

	} while ($next_row); //close do-while (and checks if there's another row)

	$output .= ""; //open TD tag

			$output .= (!$row) ? "" : ''.$row[$col_label]; //create checkbox and data from $row (**** you should comment this line if you whish to use dynamic $val_checked****)

//			echo (!$row) ? "" : ''.$row[$col_label]; //create checkbox and data from $row (**** you should de-comment this line if you whish to use dynamic $val_checked****)

			$next_row = mysql_fetch_array($q_resultado); //retrieve next row

			$output .= "\n"; //close table

	return $output; 

}

Transfer Wordpress MySQL database to new webhost

SimonDorfman (Simon Dorfman) — Mon, 14 Apr 2008 07:54:58 GMT

// Transfer Wordpress MySQL database to new webhost in 3 steps (assumes: A. files have already been copied over, B. database created on new server, C. database user created on new server, D. wp-config.php updated on new server)
// 1. login to old host via ssh, run this command:



mysqldump -h DB_HOST -u DB_USER -p DB_NAME > dump.sql

// replace DB_HOST, DB_USER, & DB_NAME with info from the local wp-config.php file
// when asked for password, enter DB_PASSWORD from the local wp-config.php file

// 2. copy dump.sql to your new host using sftp

// 3. login go new host via ssh, run this command:



mysql -h DB_HOST -u DB_USER -p DB_NAME < dump.sql

// replace DB_HOST, DB_USER, & DB_NAME with info from the local wp-config.php file
// when asked for password, enter DB_PASSWORD from the local wp-config.php file

// adopted from this page: http://technosailor.com/2007/04/06/wordpress-faq-how-do-i-move-my-blog-to-a-new-host/

how to create a new user in MySQL

jm3 (john manoogian III) — Mon, 24 Mar 2008 04:06:23 GMT

// first, mysql -uroot mysql
// then:





  mysql% GRANT ALL PRIVILEGES ON *.* TO 'jm3_spoon'@'localhost' IDENTIFIED BY 'stirthatshit' WITH GRANT OPTION;

  mysql% create database jm3_agitator;