Cacher is the code snippet organizer for pro developers

1. Export database
mysqldump -h DB_HOST -u DB_USER -p DB_PASSWORD --opt --quote-names --skip-set-charset --default-character-set=latin1 DB_NAME > DB_NAME-dump.sql


2. Reimport Database
mysql -h DB_HOST -u DB_USER -p DB_PASSWORD --default-character-set=utf8 DB_NAME < DB_NAME-dump.sql

SHOW DATABASES;

CREATE DATABASE db_name;

USE db_name;

DROP DATABASE db_name;

GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'localhost' IDENTIFIED  BY 'password';

SHOW GRANTS FOR 'username'@'localhost'

CREATE TABLE table_name (
	column_name1 definition,
	column_name2 definition,
	column_name3 definition,
	options
);
		column_name (defined by creator)
		definition: data_type(size), NULL/NOT NULL, AUTO_INCREMENT

DATA TYPES
	INT(size)
	TINYINT(size)
	VARCHAR(size) (STRING)
	TEXT

DATA SETTINGS
	NULL/NOT NULL
	AUTO_INCREMENT

	PRIMARY KEY (field) // Primary key is always indexed
	INDEX (field) // indexing a field enables faster lookups

SHOW COLUMNS FROM table_name;

DROP TABLE table_name;

CRUD = CREATE, READ, UPDATE, DELETE

SQL INSERT (CREATE)
--------------------
INSERT INTO table (column1, column2, column3) // don't need to do all columns
VALUES (val1, val2, val3); // correspond to columns above

SQL SELECT (READ)
--------------------
SELECT *
FROM table
WHERE column1 = 'some_text'
ORDER BY column1 ASC;
ORDER BY column1 DESC;

SQL UPDATE (UPDATE)
--------------------
UPDATE table
SET column1 = 'some_text' // key value pair seperated by equal sign
WHERE id = 1; // necessary or it will do to entire table.

SQL DELETE (delete)
--------------------
DELETE FROM table // deletes row
WHERE id = 1; // can be any column 


PHP DATABASE INTERACTION IN FIVE STEPS
1. Create database connection
2. Perform database query
3. Use returned data (if any)
4. Release returned data
5. Close database connection

mysqli_connect()
mysqli_connect_errno()
mysqli_connect_error()
mysqli_close()

mysqli_query()
mysqli_fetch_row() // results are in standard array. Keys are integers
mysqli_fetch_assoc() // results are in associative array. Keys are column names.
mysqli_fetch_array() // results are returned in either or both types of arrays. Specify which with MYSQL_INT/MYSQL_ASSOC/MYSQL_BOTH. Default is both
mysqli_fetch_object() // results are returned as object
mysqli_free_result()

mysqli_insert_id() // returns id of most recently inserted record
	$id = mysqli_insert_id($connection)


RETURNS FROM MYSQL COMMANDS
Command Success  Failure
SELECT  resource false
INSERT  true     false
UPDATE  true     false
DELETE  true     false


ESCAPING STRINGS FOR MYSQL
addslashes($string) // old method for escaping passed in text
mysqli_real_escape_string($db, $string);

PREPARED STATEMENTS
Seperates SQL commands from data (substituted by question marks)



PHP SESSIONS

Most Useful For:
	User authentication ($logged_in, $user_id)
	Holding data during a redirect ($message, $errors)
	Frequently referred to data ($username, $account_type)

ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';