Tag Archive for mysql

MySql Load Data

load data local infile <filepath> into table <table name> fields terminated by "	" lines terminated by "
" (fields)

source

mysqltables

#!/bin/bash
# A shell script to delete / drop all tables from MySQL database.
# Usage: ./script user password dbnane
# Usage: ./script user password dbnane server-ip
# Usage: ./script user password dbnane mysql.nixcraft.in
# -------------------------------------------------------------------------
# Copyright (c) 2008 nixCraft project <<a href="http://www.cyberciti.biz/fb/>" >http://www.cyberciti.biz/fb/></a>
# This script is licensed under GNU GPL version 2.0 or above
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit <a href="http://bash.cyberciti.biz/" >http://bash.cyberciti.biz/</a> for more information.
# ----------------------------------------------------------------------
# See URL for more info:
# <a href="http://www.cyberciti.biz/faq/how-do-i-empty-mysql-database/" >http://www.cyberciti.biz/faq/how-do-i-empty-mysql-database/</a>
# ---------------------------------------------------
 
MUSER="$1"
MPASS="$2"
MDB="$3"
 
MHOST="localhost"
 
[ "$4" != "" ] && MHOST="$4"
 
# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)
PHP=$(which php)
 
# help
if [ ! $# -ge 3 ]
then
	echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name} [host-name]"
	echo "Drops all tables from a MySQL"
	exit 1
fi
 
# make sure we can connect to server
$MYSQL -u $MUSER -p$MPASS -h $MHOST -e "use $MDB"  &>/dev/null
if [ $? -ne 0 ]
then
	echo "Error - Cannot connect to mysql server using given username, password or database does not exits!"
	exit 2
fi
 
TABLES=$($MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )
 
# make sure tables exits
if [ "$TABLES" == "" ]
then
	echo "Error - No table found in $MDB database!"
	exit 3
fi
 
# let us do it
for t in $TABLES
do
	echo "Deleting $t table from $MDB database..."
	$MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -e "SET autocommit=0; SET unique_checks=0; set foreign_key_checks = 0;truncate table $t; SET autocommit=1; SET unique_checks=1; set foreign_key_checks = 1;"
done

source

Get UTF-8 hebrew date from date stamp ( mysql )

        $hebDate =  iconv('ISO-8859-8','UTF-8',jdtojewish( unixtojd(strtotime($date)),1));

source

create user with privilèges on mysql

create database foo_db;
create user foo_user identified by 'foo_password';
grant all on foo_db.* to 'foo_user'@'%';

source

MySQL customers invoices

SELECT DISTINCT
	invoice.invoice_id,
	invoice.invoice_total,
	invoice.invoice_paid,
	invoice.invoice_tax,
	invoice.ts_printed,
	invoice.ts_add,
	ROUND(invoice.invoice_total-invoice.invoice_paid, 2) AS to_pay,
	orders.customer_ref,
	delivery_note.delivery_note_id
	FROM
	invoice
	Inner Join order_items ON order_items.invoice_ref = invoice.invoice_id
	Inner Join orders ON order_items.order_ref = orders.order_id
	Inner Join delivery_note ON order_items.delivery_note_ref = delivery_note.delivery_note_id
	WHERE
	orders.customer_ref = '$cid'
	AND
	invoice.invoice_paid < invoice.invoice_total
	ORDER BY
	invoice.invoice_id DESC

source

Find duplicate records

SELECT id, COUNT(*) as n
FROM my_table
GROUP BY id
HAVING n >1;

source

Authentication class using cookies or sessions

<?php
/*
#
#        Copyright Iulian Ciobanu (CIGraphics) 2009
#        Email: <a href="mailto:cigraphics@gmail.com">cigraphics@gmail.com</a>
#        Please leave the copyright and email intact.
#

# DATABASE TABLE:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` varchar(200) NOT NULL,
  `password` varchar(40) NOT NULL,
  `email` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

# LETS INSERT SOME DATA FOR TESTING PURPOSES:
INSERT INTO `users` (`id`, `user`, `password`, `email`) VALUES (1, 'user', '20ccbe71c69cb25e4e0095483cb63bd394a12b23', '<a href="mailto:user@email.com">user@email.com</a>');

# FOR TESTING PURPOSES:
The user is: user
The password is: 123456

# USAGE:

$auth = new Auth('database', 'user', 'password', 'host'); // This must be placed at the top of your document you don't need to start the session this script will do it.
$auth->type = session or cookie; // If you want to use sessions you don't need to write it else write cookie.
$auth->emailAuth = false or true; // If you want users to login with email instead of username set it to true or don't write this because is set to false by default
$auth->minval = integer; // The minimum chars for username. Write this only if you want to change the value because it's set by default 6.
$auth->maxval = integer; // The maximum chars for username. Write this only if you want to change the value because it's set by default 22.
$auth->minpass = integer; // The minimum chars for password. Write this only if you want to change the value because it's set by default 6.
$auth->salt = 'LOTS OF CHARS OF ANY TYPE'; // Change this. This is for security hashing. I strongly recommed to change this in the script or write this with other random chars.

$auth->login($user, $password); // Place this in the part where you get the post vars from your login forms

$auth->logout(); // Place this after $auth = new Auth(..) or if you setup type and emailAuth place it below them. Like in example. If you add it without that then you will never be able to login

$auth->error(); // Place this in your document. This function will display the errors from validation and other like mysql errors.



*/
class Auth {
    
    var $type = 'cookie';
    private $connection;
    private $errors = array();
    var $minval = 6;
    var $maxval = 22;
    var $minpass = 6;
    var $salt = '#@()DIJK#)(F#&*()DS#@JKS)@(I()#@DU)*(&@#)(#U)J';
    var $emailAuth = false;
    
    function __construct($db, $user, $pass, $host) {
        if ( $this->type == 'session' ) {
            session_start();
        }
        $this->mysqlconnect($user, $pass, $host);
        $this->mysqldb($db);
        $this->check();
    }
    
    private function mysqlconnect($user, $pass, $host) {
        $conn = @mysql_connect($host, $user, $pass);
        if ( !$conn ) {
            die('There is a problem with your mysql connection');
        } else {
            $this->connection = $conn;
        }
    }
    
    private function mysqldb($db) {
        if ( !@mysql_select_db($db, $this->connection) ) {
             die('The database doesn't exist');
        }
        
    }
    
    private function query($sql) {
        $result = @mysql_query($sql, $this->connection);
        if ( !$result ) {
            $this->errors[] = 'SQL Error';
        } else {
            return $result;
        }
    }
    
    private function fobj($result) {
        return mysql_fetch_object($result);
    }
    
    private function fnum($result) {
        return mysql_num_rows($result);
    }
    
    private function fescape($value) {
        return mysql_real_escape_string($value);
    }
    
    public function login($user, $pass) {
        $email = $this->emailAuth;
        $err = false;
        $user = strtolower($user);
        $password = $this->encrypt($pass);
        if ( $email == true ) {
            if ( !$this->email($user) ) {
                $this->errors[] = 'Email invalid.';
                $err = true;
            } else {
                $col = 'email';
            }
        } else {
            if ( !$this->name($user) ) {
                $this->errors[] = 'Name invalid. Min chars: '.$this->minval.'. Max chars: '.$this->maxval;
                $err = true;
            } else {
                $col = 'user';
            }
        }
        if ( strlen($pass) < $this->minpass ) {
            $this->errors[] = 'Password min value is 6 chars.';
            $err = true;
        }
        
        if ( $err == false ) {
            
            $sql = sprintf("SELECT * FROM users WHERE %s = '%s'", $col, $this->fescape($user));
            $result = $this->query($sql);
            if ( $this->fnum($result) == 0 ) {
                $this->errors[] = ucfirst($col).' doesn't exist.';
            } else {
                $row = $this->fobj($result);
                if ( $row->password == $password ) {
                    if ( $this->type == 'session' ) {
                        $this->set_session($col, $user);
                        $this->set_session('password', $password);
                    } elseif ( $this->type == 'cookie' ) {
                        $this->set_cookie($col, $user);
                        $this->set_cookie('password', $password);
                    }
                    header('Location: ./auth.php');
                } else {
                    $this->errors[] = 'Incorrect password';
                }
            }
                        
        }
    }
    
    private function encrypt($value) {
        $enc = md5($this->salt.md5($value));
        return sha1($enc);
    }
    
    // Email validation
    private function email($email) {
        $reg = "#^(((([a-zd][.-+_]?)*)[a-z0-9])+)@(((([a-zd][.-_]?){0,62})[a-zd])+).([a-zd]{2,6})$#i";
        if ( !preg_match($reg, $email) ) {
            return false;
        } else {
            return true;
        }
    }
    
    // Name validation
    private function name($name) {
        $min = $this->minval - 2;
        if ( !preg_match("#^[a-z][da-z_]{".$min.",".$this->maxval."}[a-zd]$#i", $name) ) {
            return false;
        } else {
            return true;
        }
    }
    
    private function set_session($name, $value) {
        $_SESSION[$name] = $value;
    }
    
    private function destroy_session() {
        session_unset();
        session_destroy();
    }
    
    private function set_cookie($name, $value, $time = 3600 ) {
        setcookie($name, $value, time()+$time, '/');
    }
    
    private function destroy_cookie($name) {
        setcookie($name, '', time()-1, '/');
    }
    
    public function logout() {
        if ( $this->emailAuth == false ) {
            $col = 'user';
        } else {
            $col = 'email';
        }
        if ( $this->type == 'session' ) {
            $this->destroy_session();
        } elseif ( $this->type == 'cookie' ) {
            $this->destroy_cookie('password');
            $this->destroy_cookie($col);
        }
        header ( 'Location: ./auth.php' );
    }
    
    private function check() {
        if ( $this->emailAuth == false ) {
            $col = 'user';
        } else {
            $col = 'email';
        }
        if ( $this->type == 'cookie' ) {
            if ( isset($_COOKIE['password']) ) {
                $sql = sprintf("SELECT * FROM users WHERE %s = '%s'", $col, $this->fescape($_COOKIE[$col]) );
                $result = $this->query($sql);
                $row = $this->fobj($result);
                if ( $row->{$col} !== $_COOKIE[$col] || $row->password !== $_COOKIE['password'] ) {
                    $this->logout();
                }
            } 
        } elseif ( $this->type == 'session' ) {
            if ( isset($_SESSION['password']) ) {
                $sql = sprintf("SELECT * FROM users WHERE %s = '%s'", $col, $this->fescape($_SESSION[$col]) );
                $result = $this->query($sql);
                $row = $this->fobj($result);
                if ( $row->{$col} !== $_SESSION[$col] || $row->password !== $_SESSION['password'] ) {
                    $this->logout();
                }
            }
        }
    }
    
    public function error() {
        if ( is_array($this->errors) && !empty($this->errors) ) {
            echo '<div style="border:1px solid #CCC; background-color:#FAFAFA; color:#FF0000">';
            foreach ( $this->errors as $value ) {
                echo $value."<br />";
            }
            echo '</div>';
        }
    }
    
    public function isLoggedIn() {
        $ret = false;
        if ( $this->emailAuth == false ) {
            $col = 'user';
        } else {
            $col = 'email';
        }
        if ( $this->type == 'cookie' ) {
            if ( isset($_COOKIE['password']) ) {
                $ret = true;
            }
        } elseif ( $this->type == 'session' ) {
            if ( isset($_SESSION['password']) ) {
                $ret = true;
            }
        }
        return $ret;
    }
    
}
?>





Example:
login.php
<?php
include 'class_auth.php';
$auth = new Auth('database', 'user', 'password', 'host'); // This order: Database User Password Host

if ( isset($_GET['logout']) ) {
    $auth->logout();
}

if ( isset($_POST['login']) ) {
    $auth->login($_POST['user'], $_POST['pass']); // This order: User/Email Password True/False (if you want to use email as auth
}
?>

HERE HTML STUFF

<?php if ( $auth->isLoggedIn() ) : ?>
<h1>Welcome</h1>
<a href="<?=$_SERVER['PHP_SELF'];?>?logout=true">Logout</a>
<?php else : ?>
<h1>Please login</h1>
<form action="<?=$_SERVER['PHP_SELF'];?>?auth" method="post">
    <input type="text" name="user" /> User/Email<br />
  <input type="password" name="pass" /> Password<br />
  <input type="submit" name="login" value="Login" />
</form>
<?php $auth->error(); endif; ?>

source

Change User Password

UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='username';
FLUSH PRIVILEGES;

source

Function to convert dates to and from Mysql

<?php

/**
* function dateconvert
*
* dateconvert is a handy function to take the aches and pains out of mysqls stupidity
* by converting data from a variable (posted from a form or just stored)
* into a format mysql will be able to store and converting the
* database date back into the british standard of date month year.
* The Script accepts day.month.year or day/month/year or day-month-year.
* example:
*
* <code>
* <?php // using type 1
* $date = "19.12.2005";
* $date = dateconvert($date, 1);
* echo $date; // Would echo 2005-12-19 which is the format stored by mysql
* ?>
* </code>
* <code>
* <?php // using type 2
* $date = $row['date']; //your mysql date
* $realdate = dateconvert($date,2);
* echo $realdate; // would display 19/12/2005
* ?>
* </code>
*
* @author Chris McKee <<a href="mailto:pcdevils@gmail.com">pcdevils@gmail.com</a>>
*
* @param string $date - Date to be converted
* @param string $func - which function is to be used (1 for input to mysql, 2 for output from mysql)
*/
function dateconvert($date,$func) {
if ($func == 1){ //insert conversion
list($day, $month, $year) = split('[/.-]', $date);
$date = "$year-$month-$day";
return $date;
}
if ($func == 2){ //output conversion
list($year, $month, $day) = split('[-.]', $date);
$date = "$day/$month/$year";
return $date;
}

}

?>

source

Manejo de fechas MYSQL

Fecha/Hora :NOW() 
Fecha Corta : CURRENT_DATE()

source