Never been to TextSnippets 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 (or not, you can keep them private!)

« Newer Snippets
Older Snippets »
61 total  XML / RSS feed 

Test php mysql pdo_mysql

// description of your code here

<?php $link = mysql_connect('localhost','mydbuser','mydbpassword');
if (!$link) {
        die('Could not connect to MySQL: ' . mysql_error());
} echo 'mySql Connection OK';

$dbh = new PDO('mysql:host=localhost;dbname=test','mydbuser','mydbpassword');
if ($dbh) {
        echo 'mysql_pdo CONNECTION OK';
else {echo 'mysql_pdo ERROR';}

import data into mysql

Things to note:
Mysql may not have access to the directory you've put the datafile. Use /tmp to overcome this problem.
Datafile name must be the same as the mysql table you're importing to.

mysqlimport -u username -p --columns=column_1,column_2 --fields-terminated-by=',' databasename /tmp/data_file_same_as_mysql_table_name

mysql jdbc access conf

url : jdbc:mysql://host_name:port/dbname

driver class name : com.mysql.jdbc.Driver

Manually Starting & Stopping MySQL in Leopard

This is for software built from source according to:

Start MySQL
sudo launchctl unload -w /Library/LaunchDaemons/com.mysql.mysqld.plist

Stop MySQL
sudo launchctl load -w /Library/LaunchDaemons/com.mysql.mysqld.plist

MySQL 5 macports setup

You have to symlink mysql to the location on osx

first find out where mysql has put the socket:
mysql_config5 --socket

Next symlink /tmp/mysql.sock to the right location of the socket
sudo ln -s path-of-mysql.sock-from-above /tmp/mysql.sock

Start MySQL
sudo /opt/local/share/mysql5/mysql/mysql.server start


Stop MySQL
sudo /opt/local/share/mysql5/mysql/mysql.server stop

Setup LAMP on Ubuntu with MyODBC support.

Go to terminal:

sudo apt-get install apache2 mysql-server php5 php5-odbc libapache2-mod-php5 php5-xsl php5-gd php-pear libapache2-mod-auth-mysql php5-mysql libmyodbc

Now hand edit (or create, if it does not exist already) /etc/odbc.ini

Here's an example odbc.ini:

[ODBC Data Sources]
odbcname     = MyODBC 3.51 Driver DSN

Driver       = /usr/lib/odbc/
Description  = MyODBC 3.51 Driver DSN
SERVER       =
PORT         =
USER         = USER
Password     = XXXXXX
Database     = DBNAME
OPTION       = 3
SOCKET       =

Driver       = /usr/local/lib/
Description  = MyODBC 3.51 Driver DSN
SERVER       = localhost
PORT         =
USER         = root
Password     =
Database     = test
OPTION       = 3
SOCKET       =

Remember to start and stop mysql and apache:

sudo /etc/init.d/mysql start
sudo /etc/init.d/apache2 start

sudo /etc/init.d/mysql stop
sudo /etc/init.d/apache2 stop
sudo /etc/init.d/mysql restart
sudo /etc/init.d/apache2 restart

Give remote access to mysql

mysql -u root -p (your password)

then write the following




Remove Existing MySQL Installations

If you already have MySQL installed and used the package installer from MySQL to install it, you need to remove a single file (actually a symlink) to disable it:

sudo rm /usr/local/mysql

If you also installed the StartupItem package, you’ll want to remove it as well. Keep in mind that if you ever want to auto-start the old version of MySQL later on, you’ll need to re-download the package installer and reinstall the StartupItem.

sudo rm -rf /Library/StartupItems/MySQLCOM/

Change Root user password

mysql -h localhost -u root
mysql> USE mysql;
mysql> UPDATE user SET Password=PASSWORD('root-pwd') WHERE user='root';
mysql> EXIT

MySQL add user

mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

Macports MySQL 5 startup on launch

A startup item has been generated that will aid in starting mysql5 with launchd. It is disabled by default. Execute the following command to start it, and to cause it to launch at startup:
sudo launchctl load -w /Library/LaunchDaemons/org.macports.mysql5.plist

Starting and stopping MySQL on leopard.

#starting SQL:
sudo /usr/local/mysql/support-files/mysql.server start

Stoping SQL:
sudo /usr/local/mysql/support-files/mysql.server stop

Convert MySQL tables to InnoDB

// Slower than MyISAM, but row locking is clutch if you're doing a lot of writes amirite?

for t in $(mysql --batch --column-names=false -e "show tables" mydbname |grep -v "exclude_this");
mysql -e "alter table $t type=InnoDB" mydbname;


Script to setup ssh tunnel to a remote mysql server

#!/usr/bin/env ruby

require 'yaml'
require 'rubygems'
require_gem 'net-ssh'

$stdout.sync = true

def help
  puts <<-USAGE
        This tool allows you to setup an ssh tunnel to a remote mySQL server

    Please specify configuration in ~/.dbsshrc


    example ~/.dbsshrc:   

        username: user_id
        password: password_cleartext
        remote_port: 3306
        local_port: 3307

app_name = ARGV[0] || help
$config = YAML::load("~/.dbsshrc")))[app_name]

puts "connecting to #{$config["server"]}"

exec("ssh -2 -f -c blowfish -N -C #{$config["username"]}@#{$config["server"]} -L #{$config["local_port"]}/{$config["remote_port"]}")

puts ""
puts "success!"

Change many tables at once

I had to ALTER TABLE a lot of tables. This code runs a certain ALTER TABLE statement on each table in a database

echo 'SHOW TABLES' |mysql -N -u <user> <db> | sed -e "s/^\(.*\)$/ALTER TABLE \`\1\` ADD (created TIMESTAMP DEFAULT '2007-12-01', modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);/g" | mysql -u <user> <db>

mySQL Find Duplicates

id,count(*) as n

Delete duplicate rows from a table

// Whoops, didja forgot to add a unique index to that column? No problem

SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];
DROP TABLE old_table;
RENAME TABLE new_table TO old_table;

Limit equivelent for MSSQL

Props to this site for the answer:
// MySQL limit clause
SELECT emp_id,lname,fname FROM employee LIMIT 20,10

// MSSQL equivelent
select * from (
 select top 10 emp_id,lname,fname from (
    select top 30 emp_id,lname,fname
    from employee
   order by lname asc
 ) as newtbl order by lname desc
) as newtbl2 order by lname asc

"The main thing to remember is to deal with all your ORDER BY clauses and also to use the inner TOP as the sum of what you are looking for. In this example - 30 since you start at 20 and want 10 rows (20+10=30)."

add mysql symbolic link to OS X MacPorts installed mysql

instead of having to type out mysql5 each time I want to run mysql, make a symbolic link to mysql5

ln -s ../lib/mysql5/bin/mysql /opt/local/bin/mysql

mysql on OS X

mysql installed via ports by default resides in


from this location you can start/stop mysql with the following

./mysql.server stop
./mysql.server start

to have a my.cnf file to edit

sudo cp my-small.cnf my.cnf

« Newer Snippets
Older Snippets »
61 total  XML / RSS feed