Never been to DZone Snippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

« Newer Snippets
Older Snippets »
Showing 1-10 of 105 total  RSS 

MYSQL - column copy with string replacement

MySql snippet to copy a column in a table to another column, with some string manipulation.
Create new_col before executing this segment. 

   1  
   2  UPDATE table SET table.new_col=REPLACE(table.old_col,'str2replace','replace_with_str');
to sql mysql string copy replacement column by srgama on Aug 20, 2008

running total in PHP

// output running total
// userID,posts,runningTotal|
// 2,23434,28330|
// 6,3443,4896|
// 1,422,1453|
// 3,344,1031|
// 4,344,687|
// 5,343,343|

   1  
   2  $q = mysql_query("select * from `members` order by `posts` DESC");
   3  echo 'userID,posts,runningTotal|<br>';
   4  while($a = mysql_fetch_row($q)){
   5  echo "$a[0],$a[1],$total|<br>";
   6  $total = $total - $a[1];
   7  }
to mysql php by shantanuo on Aug 08, 2008

Create MySQL user

   1  grant all privileges on db_name.table_name (or .*) to 'username'@'localhost' identified by 'password';
to mysql sysadmin useradd by flynets on Jul 31, 2008

Compare Engines

// 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.

   1  
   2  CREATE TABLE test.`TABLES2` (
   3  `TABLE_CATALOG` varchar(512) default NULL,
   4  `TABLE_SCHEMA` varchar(64) NOT NULL default '',
   5  `TABLE_NAME` varchar(64) NOT NULL default '',
   6  `TABLE_TYPE` varchar(64) NOT NULL default '',
   7  `ENGINE` varchar(64) default NULL,
   8  `VERSION` bigint(21) default NULL,
   9  `ROW_FORMAT` varchar(10) default NULL,
  10  `TABLE_ROWS` bigint(21) default NULL,
  11  `AVG_ROW_LENGTH` bigint(21) default NULL,
  12  `DATA_LENGTH` bigint(21) default NULL,
  13  `MAX_DATA_LENGTH` bigint(21) default NULL,
  14  `INDEX_LENGTH` bigint(21) default NULL,
  15  `DATA_FREE` bigint(21) default NULL,
  16  `AUTO_INCREMENT` bigint(21) default NULL,
  17  `CREATE_TIME` datetime default NULL,
  18  `UPDATE_TIME` datetime default NULL,
  19  `CHECK_TIME` datetime default NULL,
  20  `TABLE_COLLATION` varchar(64) default NULL,
  21  `CHECKSUM` bigint(21) default NULL,
  22  `CREATE_OPTIONS` varchar(255) default NULL,
  23  `TABLE_COMMENT` varchar(80) NOT NULL default ''
  24  )
  25  ENGINE=FEDERATED DEFAULT CHARSET=latin1
  26  CONNECTION='mysql://root@172.172.172.172/information_schema/TABLES';
  27  
  28  SELECT b.TABLE_SCHEMA as remote_database, b.TABLE_NAME as remote_tableName, b.ENGINE as remote_engine, a.ENGINE AS local_engine 
  29  FROM test.TABLES2 AS a INNER JOIN information_schema.TABLES as b 
  30  ON a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.TABLE_NAME = b.TABLE_NAME AND a.ENGINE != b.ENGINE;
to mysql by shantanuo on Jul 22, 2008

Claves Foraneas / Foreign key

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.

   1  
   2  <?php
   3  require_once("cabezera/funciones.php");
   4  conectar_mysql();
   5  require_once("cabezera/scripts/constantes.php");
   6  ?>
   7  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
   8  <html xmlns="http://www.w3.org/1999/xhtml">
   9  <head>
  10  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-2" />
  11  <title>Untitled Document</title>
  12  </head>
  13  
  14  <body>
  15  <?php
  16  $ejecutar = isset($_GET['ejecutar']) && $_GET['ejecutar']=='si';
  17  $tabla_padre = "album";
  18  $tabla_hijo = "fotos";
  19  $campo_padre = "idAlbum";
  20  $campo_hijo = "idAlbum";
  21  
  22  $sinIntegridad = 0;
  23  
  24  $sql="select * from ".$tabla_hijo;
  25  $resultado = mysql_query($sql);
  26  while($fila = mysql_fetch_array($resultado))
  27  {
  28  	$sql2 = "select * from ".$tabla_padre." where ".$campo_padre." = '".$fila[$campo_hijo]."'";
  29  	//echo $sql2."<br/>";
  30  	$resultado2 = mysql_query($sql2);
  31  	if(mysql_num_rows($resultado2) <= 0)
  32  	{
  33  		$sql3 = "DELETE FROM `".$tabla_hijo."` WHERE `".$campo_hijo."` = '".$fila[$campo_hijo]."' LIMIT 1;";
  34  		if($ejecutar)
  35  		{
  36  			if(mysql_query($sql3)) echo "OK query : ".$sql3."<br />";
  37  			else echo "Error en query : ".$sql3."<br />";
  38  		}
  39  		else
  40  		{
  41  			echo $sql3."<br/>";
  42  		}
  43  		$sinIntegridad++;
  44  	}
  45  }
  46  
  47  $sql1="ALTER TABLE `".$tabla_padre."` ADD INDEX ( `".$campo_padre."` ) ";
  48  $sql2="ALTER TABLE `".$tabla_hijo."` ADD INDEX ( `".$campo_hijo."` ) ";
  49  $sql3="ALTER TABLE ".$tabla_hijo." ADD FOREIGN KEY(".$campo_hijo.") REFERENCES ".$tabla_padre." (".$campo_padre.") ON DELETE CASCADE ON UPDATE CASCADE";
  50  if($ejecutar)
  51  {
  52  	if(mysql_query($sql1)) echo "OK query : ".$sql1."<br />";
  53  	else echo "Error en query : ".$sql1."<br />";
  54  	
  55  	if(mysql_query($sql2)) echo "OK query : ".$sql2."<br />";
  56  	else echo "Error en query : ".$sql2."<br />";
  57  	
  58  	if(mysql_query($sql3)) echo "OK query : ".$sql3."<br />";
  59  	else echo "Error en query : ".$sql3."<br />";
  60  }
  61  else
  62  {
  63  	echo $sql1."<br/>";
  64  	echo $sql2."<br/>";
  65  	echo $sql3."<br/>";
  66  }
  67  
  68  echo "<br/>".$sinIntegridad." filas sin integridad.<br/>";
  69  ?>
  70  <form id="form1" name="form1" method="get" action="">
  71    <label>
  72    <input name="ejecutar" type="hidden" id="ejecutar" value="si" />
  73    <input name="Button" type="button" onclick="location.href=location.href" value="Recargar sin ejecutar nada" />
  74    <input type="submit" name="button" id="button" value="Ejecutar SQL!" />
  75    </label>
  76  </form>
  77  <?php
  78  /*
  79  ALTER TABLE fotos_nueva_version ADD FOREIGN KEY(idUsuario) REFERENCES usuarios_portalcocinas (idUsuario) ON DELETE CASCADE ON UPDATE CASCADE
  80  
  81  ALTER TABLE relacion_usuario_localidad ADD FOREIGN KEY(idUsuario) REFERENCES usuarios_portalcocinas (idUsuario) ON DELETE CASCADE ON UPDATE CASCADE
  82  
  83  
  84  ALTER TABLE relacion_usuario_actividad ADD FOREIGN KEY(idUsuario) REFERENCES usuarios_portalcocinas (idUsuario) ON DELETE CASCADE ON UPDATE CASCADE
  85  
  86  
  87  ALTER TABLE fotos_nueva_version DROP FOREIGN KEY idUsuario_FK;
  88  
  89  SHOW CREATE TABLE fotos_nueva_version;
  90  */
  91  ?>
  92  </body>
  93  </html>
  94
to mysql php key foreign Claves Foraneas by Ricardo on Jul 16, 2008

Query MySQL accent insensitive in latin1_general_ci

Query MySQL accent insensitive in latin1_general_ci

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


Source: Query MySQL accent insensitive in latin1_general_ci
to sql mysql by ki4ngel on Jun 21, 2008

export and import by php

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 :

<?php
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 :

<?php
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
to mysql php and import export by ajay1kumar1 by ajay1kumar1 on Jun 16, 2008

Processing large CSV files

   1  
   2    # this method allows fast CSV processing, it reads file_name, generates a new
   3    # CSV file and inserts all data in a mysql table using LOAD DATA INFILE
   4  
   5      # you can use it like this:
   6  
   7      load_csv_data(PRELOAD_DIR+'socios.csv', 'users') do |csv, thing_id, row|
   8        csv << [thing_id,
   9          row['NOMBRE'].to_s.to_permalink+'.'+row['APELLIDOS'].to_s.to_permalink, #  login
  10          row['E_MAIL'], #  email
  11          row['SEXO'],  #  gender
  12          (row['NO_DATOS'] == 'S' || row['NO_DATOS'] == 'VERDADERO' ? 1 : 0 ), #  no_datos
  13        ]
  14      end
  15  
  16  
  17    def load_csv_data(file_name, table_name)
  18      STDERR.print("\nParsing '#{file_name}' to insert data in '#{table_name}'\n")
  19  
  20      n_lines = (%x[wc -l #{file_name}]).split(' ')[0].to_f
  21      time_start = Time.now
  22      begin
  23        require 'fastercsv'
  24        count = 0.0
  25  
  26        csv_string = FasterCSV.open("data.csv", "w", { :col_sep => ";", :force_quotes => true })  do |csv|
  27        FasterCSV.open("#{file_name}", { :col_sep => "\t", :headers => :first_row }).each do |row|
  28          thing_id = row[0].to_i
  29          if thing_id && thing_id > 0
  30            
  31            yield csv, thing_id, row
  32  
  33            percentage = ((count += 1) * 100.0)/n_lines
  34            STDERR.print "%.0f%%..." % percentage if (percentage.modulo(2.0) < 0.001)
  35          end
  36          end
  37        end
  38      rescue EOFError
  39        f.close
  40      end
  41      total_lines = (%x[wc -l data.csv]).split(' ')[0].to_i
  42      STDERR.print("\nInserting #{total_lines} lines in table '#{table_name}'\n")
  43      ActiveRecord::Base.connection.execute("LOAD DATA INFILE '#{File.join(File.dirname(__FILE__), '..', '..', 'data.csv')}' IGNORE INTO TABLE #{table_name} FIELDS TERMINATED BY ';' ENCLOSED BY '\"';")
  44      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))
  45    end
  46  
  47
to ruby mysql csv simplelogica by demimismo on Jun 10, 2008

Getting ActiveRecord to auto reconnect after lost connection

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

   1  
   2  ActiveRecord::ConnectionAdapters::MysqlAdapter.module_eval do
   3    def execute_with_retry_once(sql, name = nil)
   4      retried = false
   5      begin
   6        execute_without_retry_once(sql, name)
   7      rescue ActiveRecord::StatementInvalid => exception
   8        ActiveRecord::Base.logger.info "#{exception}, retried? #{retried}"
   9  
  10        # Our database connection has gone away, reconnect and retry this method
  11        reconnect!
  12        unless retried
  13          retried = true
  14          retry
  15        end
  16      end
  17    end
  18  
  19    alias_method_chain :execute, :retry_once
  20  end
to rails mysql activerecord by bmarini on May 19, 2008 1 Comments

Copy MySQL table

   1  
   2  CREATE TABLE table_destination SELECT * FROM table_source ;



   1  
   2  CREATE TABLE table_destination LIKE table_source ;
   3  INSERT INTO table_destination SELECT * FROM table_source ;


Source: Asselin Benoit Development ( MySQL, SQL ) & Agence ici, agence de communication
to sql mysql by ki4ngel on May 13, 2008
« Newer Snippets
Older Snippets »
Showing 1-10 of 105 total  RSS