Tag Archive for mysql

snippets | May 22, 2012
No comments

Dump Database

mysqldump -uuser  -ppass --opt database > file directory

source

Category: Uncategorized | Tags: , ,
snippets | May 21, 2012
No comments

rename table

ALTER TABLE table_name
RENAME TO new_table_name;

source

Category: Uncategorized | Tags: , ,
snippets | May 17, 2012
No comments

50 States & Washington D.C.

CREATE TABLE `state` (
`id` tinyint(4) NOT NULL auto_increment,
`name` char(50) NOT NULL default '',
`abbreviation` char(2) NOT NULL default '',
PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=52 DEFAULT CHARSET=utf8;

INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('1','Alabama','AL');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('2','Alaska','AK');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('3','Arizona','AZ');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('4','Arkansas','AR');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('5','California','CA');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('6','Colorado','CO');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('7','Connecticut','CT');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('8','Delaware','DE');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('9','District of Columbia','DC');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('10','Florida','FL');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('11','Georgia','GA');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('12','Hawaii','HI');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('13','Idaho','ID');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('14','Illinois','IL');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('15','Indiana','IN');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('16','Iowa','IA');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('17','Kansas','KS');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('18','Kentucky','KY');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('19','Louisiana','LA');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('20','Maine','ME');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('21','Maryland','MD');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('22','Massachusetts','MA');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('23','Michigan','MI');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('24','Minnesota','MN');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('25','Mississippi','MS');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('26','Missouri','MO');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('27','Montana','MT');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('28','Nebraska','NE');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('29','Nevada','NV');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('30','New Hampshire','NH');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('31','New Jersey','NJ');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('32','New Mexico','NM');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('33','New York','NY');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('34','North Carolina','NC');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('35','North Dakota','ND');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('36','Ohio','OH');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('37','Oklahoma','OK');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('38','Oregon','OR');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('39','Pennsylvania','PA');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('40','Rhode Island','RI');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('41','South Carolina','SC');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('42','South Dakota','SD');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('43','Tennessee','TN');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('44','Texas','TX');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('45','Utah','UT');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('46','Vermont','VT');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('47','Virginia','VA');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('48','Washington','WA');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('49','West Virginia','WV');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('50','Wisconsin','WI');
INSERT INTO `state` (`id`,`name`,`abbreviation`) VALUES ('51','Wyoming','WY');

source

Category: Uncategorized | Tags: , ,
snippets | May 15, 2012
No comments

OOP MySQL Database Class

<?php
class database {

var $host = NULL;
var $username = NULL;
var $password = NULL;
var $databaseName = NULL;
var $link = NULL;
var $queries = NULL;
var $errors = NULL;

var $databaseExtras = NULL;

function __construct($host, $username, $password, $database) {
$this->database($host, $username, $password, $database);
}

function database($host, $username, $password, $database) {
/*$this->database = array (
"host" => $host,
"username" => $username,
"password" => $password,
"database" => $database,
"link" => "",
"queries" => array (),
"errors" => array ()
);*/

$this->host = $host;
$this->username = $username;
$this->password = $password;
$this->databaseName = $database;
$this->link = "";
$this->queries = array ();
$this->errors = array ();

$this->databaseExtras = new stdClass;

$this->link = mysql_connect($this->host, $this->username, $this->password) or die("Could not connect to Database");
mysql_select_db($this->databaseName);
}

function justquery($sql) {
$this->queries[] = $sql;
return mysql_query($sql, $this->link);
}

function loadResult($sql) {
if (!($cur = $this->justquery($sql))) {
return null;
}
$ret = null;
if ($row = mysql_fetch_row( $cur )) {
$ret = $row[0];
}
mysql_free_result( $cur );
return $ret;
}

function loadFirstRow($sql) {
if (!($cur = $this->justquery($sql))) {
return null;
}
$ret = null;
if ($row = mysql_fetch_object( $cur )) {
$ret = $row;
}
mysql_free_result( $cur );
return $ret;
}

function insertid() {
return mysql_insert_id( $this->link );
}

function query($sql, $key = "", $returns = true, $batch = false) {
$result = array ();

switch ($batch) {
default:
case true:
foreach ($sql as $index => $query) {
$this->queries[] = $query;
$answer = mysql_query($query, $this->link);

if (!$answer) {
$this->errors[] = mysql_error($this->link);
}
else {
if ($returns != false) {
if (mysql_num_rows($answer) > 0){
while ($row = mysql_fetch_object($answer)) {
if ($key != ""){
$result[$index][$row->$key] = $row;
}
else {
$result[$index][] = $row;
}
}
} else {}
} else {}
}
}
break;

case false:
$this->queries[] = $sql;
$answer = mysql_query($sql, $this->link);

if (!$answer) {
$this->errors[] = mysql_error($this->link);
$result = false;
}
else {
if ($returns != false) {
if (mysql_num_rows($answer) > 0){
while ($row = mysql_fetch_object($answer)) {
if ($key != ""){
$result[$row->$key] = $row;
}
else {
$result[] = $row;
}
}
} else {}
}
else {
$result = true;
}
}
break;
}

return $result;
}

function loadObject( $sql, &$object ) {
if ($object != null) {
if (!($cur = $this->justquery($sql))) {
return false;
} else {}
if ($array = mysql_fetch_assoc( $cur )) {
mysql_free_result( $cur );
$this->bindArrayToObject( $array, $object);
return true;
}
else {
return false;
}
}
else {
if ($cur = $this->justquery($sql)) {
if ($object = mysql_fetch_object( $cur )) {
mysql_free_result( $cur );
return true;
}
else {
$object = null;
return false;
}
}
else {
return false;
}
}
}

function bindArrayToObject( $array, &$obj) {
if (!is_array( $array ) || !is_object( $obj )) {
return (false);
}

foreach (get_object_vars($obj) as $k => $v) {
if( substr( $k, 0, 1 ) != '_' ) {
$ak = $k;
if (isset($array[$ak])) {
$obj->$k = $array[$ak];
}
}
}

return true;
}

function formatCSVCell($data) {
$useQuotes = false;

$quotable = array (
""" => """",
"," => ",",
"
" => "
"
);

foreach ($quotable as $char => $repl) {
if (eregi($char, $data)) {
$useQuotes = true;
} else {}
}

if ($useQuotes == true) {
foreach ($quotable as $char => $repl) {
$data = str_replace($char, $repl, $data);
}

$data = """ . $data . """;
}
else {

}

return $data;
}
}
?>

source

Category: Uncategorized | Tags: , , , ,
snippets | May 14, 2012
No comments

Query – SELECT

$query = "SELECT * FROM table";
$ergebnis = mysql_query( $query );
while($row = mysql_fetch_array( $result ))
{
echo $row['field'];
}
mysql_free_result( $result );

source

Category: Uncategorized | Tags: ,
snippets | May 3, 2012
No comments

Show mysql query log with firebug

if (!function_exists('debug_mysql_query')) {

function debug_mysql_query($query)
{
$js_query = str_replace(array('', "'"), array("\", "'"), $query);
$js_query = preg_replace('#([x00-x1F])#e', '"x" . sprintf("%02x", ord("1"))', $js_query);
echo '<script>console.log("'.$js_query.'");</script>'."
";
return mysql_query($query);
}
}

source

Category: Uncategorized | Tags: , , ,
snippets | May 2, 2012
No comments

MySQL connection with PHP

<?php
mysql_connect("localhost","root","asdf") or die (mysql_error());
mysql_select_db("DB") or die (mysql_error());

$result = mysql_query('SELECT * FROM tabellename') or die (mysql_error());

while($entry = mysql_fetch_assoc($result)){
$entry['headline']
}

mysql_free_result($result);

$entry= "INSERT INTO tabellename (feld1, feld2) VALUES ('111111s', '2222222s');";
$init = mysql_query($entry);

$change = "UPDATE blog Set tag = 'Cinema 4D' WHERE id = '2'";
$update = mysql_query($change);
?>

source

Category: Uncategorized | Tags: , ,
snippets | May 2, 2012
No comments

dataList – Listar dados

include("conx.php");

//----depois o RS
$Tabela = "clientes";

$SQL = "SELECT * FROM $Tabela ORDER BY Nome ASC";
$rs = mysql_query("$SQL");

//-----> Depois o Loop

<? while ($x = mysql_fetch_assoc($rs)){  ?>
<? echo $x['campo1'] ?>
<? } ?>

source

Category: Uncategorized | Tags: , , ,
snippets | May 2, 2012
No comments

MySQL Connection

<?

//arquivo de configuração do sistema...
$endereco_host = 'localhost';
$usuario_host = 'usuario';
$senha_host = 'senha13';
$banco_de_dados = 'meu_banco';

// Conexao
$conectar = mysql_connect($endereco_host, $usuario_host, $senha_host) or die ("NAO FOI POSSIVEL CONECTAR A BASE DE DADOS");
$banco = mysql_select_db($banco_de_dados);

?>

source

Category: Uncategorized | Tags: , ,
snippets | May 1, 2012
No comments

Backup your MySQL database

backup_tables('localhost','username','password','blog');

/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{

$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);

//get all of the tables
if($tables == '*')
{
$tables = array();
$result = mysql_query('SHOW TABLES');
while($row = mysql_fetch_row($result))
{
$tables[] = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(',',$tables);
}

//cycle through
foreach($tables as $table)
{
$result = mysql_query('SELECT * FROM '.$table);
$num_fields = mysql_num_fields($result);
for ($i = 0; $i < $num_fields; $i++)
{
$return.= 'DROP TABLE '.$table.';';

$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
$return.= "

".$row2[1].";

";

while($row = mysql_fetch_row($result))
{
$return.= 'INSERT INTO '.$table.' VALUES(';
for($j=0; $j<$num_fields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = ereg_replace("
","n",$row[$j]);
if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
if ($j<($num_fields-1)) { $return.= ','; }
}
$return.= ");
";
}
}
$return.="

";
}

//save file
$handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
fwrite($handle,$return);
fclose($handle);
}

source

Category: Uncategorized | Tags: , ,
« Older Entries