Conditionals in MySql queries

NOT BETWEEN 10 AND 20
NOT IN (10,11,12)

Create a Foreign Key

FOREIGN KEY (blog_userID) REFERENCES blog_users(userId) ON DELETE CASCADE

MySQL TimeStamps

MySQL TimeStamps can have 2- 14 digits (where 2 = YY and 10 would equal YYMMDDHHSS).

Regenerate

/**
 * SQL Creation Code Generator
 * 
 * This script will create SQL code for recreating
 * a database and table structures.
 * 
 * To use this script, simply edit the values of the
 * variables in the SETTINGS section.
 * 
 * @author Ralfe Poisson <ralfepoisson@gmail.com>
 * @version 1.0
 * @license GPLv3
 */

# =================================================
# SETTINGS
# =================================================

$mysql_host		= "test";
$mysql_user		= "test";
$mysql_pass		= "test";
$mysql_db		= "test";

# =================================================
# GET SQL
# =================================================

# Get List of Tables
$tables			= array();
$result			= query("SHOW TABLES");
while ($row		= mysql_fetch_row($result)){
	$tables[]	= $row[0];
}

# Get SQL Code For Tables
$code			= array();
foreach($tables as $table){
	$sql		= "SHOW CREATE TABLE `$table`";
	$result		= query($sql);
	$row		= mysql_fetch_row($result);
	$code[$table]	= $row[1];
}

# =================================================
# OUTPUT SQL CODE
# =================================================

# Output Database Creation Code
print "CREATE DATABASE IF NOT EXISTS `$mysql_db`;\n\n";
print "USE `$mysql_db`;\n\n";

# Output Table Creation Code
foreach($code as $key => $sql){
	print $sql . "\n\n";
}

# =================================================
# FUNCTIONS
# =================================================

/**
 * This function connects to the mysql server, executes the query and returns the result set.
 * @param String $sql The SQL query to execute.
 * @return mysql_resultset $result The SQL Result Set.
 */
function query($sql){
	# Global Variables
	global $mysql_host, $mysql_user, $mysql_pass, $mysql_db;
	
	# Connect to Database Server
	$link = mysql_connect($mysql_host, $mysql_user, $mysql_pass);
	err(mysql_error());
	
	# Select Database
	mysql_select_db($mysql_db, $link);
	err(mysql_error());
	
	# Execute SQL
	$result = mysql_query($sql);
	err(mysql_error());
	
	# Return Result Set
	return $result;
}

/**
 * This function displays a database error, if one exists, and stops the script.
 * @param String $err The MySQL error.
 */
function err($err){
	if (strlen($err) > 0) {
		print "DB ERROR : $err\n";
		die();
	}
}

# =================================================
# THE END
# =================================================

Tips

  • Use indexes in the database tables will increase speed of queries.
  • EXPLAIN shows performance differences.

You should also create separate MySQL users for different types of activity. For example, the administrator user for the site will need SELECT, INSERT, and UPDATE permissions. They may also need DELETE, but it’s best not to allow that unless absolutely necessary. Conversely, almost everything a customer will do on an e-commerce site will only require a MySQL user with SELECT privileges. Browsing and searching the catalog are simple SELECT queries. It may not be until the user starts to complete an order—actually check out—that an INSERT is required. An UPDATE would be needed if they can change their password or other personal information. DELETE permissions would never be appropriate. In theory, you could create three distinct types of MySQL users with specific permissions:

  • Public: SELECT
  • Customer: SELECT, INSERT, UPDATE
  • Admin: SELECT, INSERT, UPDATE, DELETE

Use Fulltext search on fields which are searched

ADD FULLTEXT(product,productDescription) ... MATCH() AGAINST ()

Wildcard Searches

% finds zero or more characters 
- find a single character

LIKE '_frogs%'

MySQL Users

You should also create separate MySQL users for different types of activity. For example, the administrator user for the site will need SELECT, INSERT, and UPDATE permissions. They may also need DELETE, but it’s best not to allow that unless absolutely necessary. Conversely, almost everything a customer will do on an e-commerce site will only require a MySQL user with SELECT privileges. Browsing and searching the catalog are simple SELECT queries. It may not be until the user starts to complete an order—actually check out—that an INSERT is required. An UPDATE would be needed if they can change their password or other personal information. DELETE permissions would never be appropriate. In theory, you could create three distinct types of MySQL users with specific permissions:

  • Public: SELECT
  • Customer: SELECT, INSERT, UPDATE
  • Admin: SELECT, INSERT, UPDATE, DELETE

MySQL Table Schema

function mysql_tb_schema($tb) {
   if(!$res = mysql_query("SHOW CREATE TABLE `$tb`")){
      return @mysql_error();
   }
   if($schema = mysql_result($res, 0, 1)){
      return $schema.';';
   }
   return 'Table description not found';
};

echo '

'.mysql_tb_schema("snippets").'

';

MySQL Find and Replace

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’);

update client_table set company_name = replace(company_name, ‘Old Company’, ‘New Company’)

The above statement will replace all instances of ‘Old Company’ to ‘New Company’ in the field of company_name of client_table table.

Grant Table level access


If you want to grand a new user table-level access you need an SQL statement like this one:


INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv,Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv,Process_priv, File_priv, Grant_priv, References_priv, Index_priv,Alter_priv) VALUES (‘localhost’, ‘juan’, ‘password’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’,‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’)
GRANT delete,insert,select,update on [dbname].* to nobody@localhost identified by -----
FLUSH priviledges