DATABASE

database
- set of tables
- 1 db = 1 application
- example: widget_corp
- access permissions are granted at database level

table
- set of columns and rows
- represents a single concept (a noun)
- examples: products, customers, orders
- relationships between tables

column
- set of data of a single simple type
- examples: first_name, last_name, email, password
- columns have types: strings, integers, etc.

row
- single record of data.
- example: "Kevin", "Skoglund", "ks@email.com", "secret"

field
- intersection of a column and a row
- example: first_name: "Kevin"


index
- data structure on a table to increase look up speed
- like the index at the back of the book

foreign key
- table column whose values reference rows in another table
- foundation of relational databases


CRUD
create
read
update
delete


command line (koding.com / linux)

mysql -u root --password=******
mysql -u root -p  /* doesn't show the password */

//note: the cursor does not move to the next space when you type the password. just type it.
//note 2: the default password for root is no password.
//if ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' //(2)
//try: sudo service mysql restart
//to check if mysql is running: ps -ef | grep mysql 

SHOW DATABASES;
CREATE DATABASE db_name;
USE db_name;
DROP db_bame;

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

SHOW GRANTS FOR 'username'@'localhost';

GRANT ALL PRIVILEGES ON widget_corp.*
TO 'widget_cms'@'localhost'
IDENTIFIED BY 'secretpassword'; 

exit;

mysql -u username -p db_name
mysql -u widget_cms -p widget_corp

CREATE TABLE table_name (
column_name1 definition,
column_name2 definition,
column_name3 definition,
options,
);

SHOW COLUMNS FROM table_name;
DROP TABLE table_name;

subjects //table
- id //columns, identifies specific rows
- menu_name //displayed name on the public side of the website
- position //change the sort order of the subjects
- visible //boolean true or false, on whether the subject should be shown to the public or not

mysql> CREATE TABLE subjects (                                                                                                                                 
    -> id INT(11) NOT NULL AUTO_INCREMENT,                                                                                                                     
    -> menu_name VARCHAR(30) NOT NULL,                                                                                                                         
    -> position INT(3) NOT NULL,
    -> visible TINYINT(1) NOT NULL,                                                                                                                            
    -> PRIMARY KEY (id)                                                                                                                                        
    -> );
Query OK, 0 rows affected (0.03 sec)


mysql> SHOW COLUMNS FROM subjects;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| menu_name | varchar(30) | NO   |     | NULL    |                |
| position  | int(3)      | NO   |     | NULL    |                |
| visible   | tinyint(1)  | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)


CRUD in MySQL

SQL SELECT (read)
SELECT *
WHERE column1 = 'some_text'
ORDER BY column1 ASC; //or DESC for descending order

SQL INSERT (create)
INSERT INTO table (column1, column2, column3)
VALUES (val1, val2, val3);

SQL UPDATE (update)
UPDATE table
SET column1 = 'some_text'
WHERE id = 1;

SQL DELETE (delete)
DELETE FROM table
WHERE id = 1;




mysql> INSERT INTO subjects (menu_name, position, visible)                                                                                                     
    -> VALUES ('About Widget Corp', 1, 1);                                       //visible: true or false , the same as 1 or 0.

                                                                              
Query OK, 1 row affected (0.01 sec)

mysql> select * from subjects;
+----+-------------------+----------+---------+
| id | menu_name         | position | visible |
+----+-------------------+----------+---------+
|  1 | About Widget Corp |        1 |       1 |
+----+-------------------+----------+---------+
1 row in set (0.00 sec)

mysql> show tables;                                                                                                                                            
+-----------------------+
| Tables_in_widget_corp |
+-----------------------+
| subjects              |
+-----------------------+
1 row in set (0.00 sec)

mysql> show columns from subjects;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| menu_name | varchar(30) | NO   |     | NULL    |                |
| position  | int(3)      | NO   |     | NULL    |                |
| visible   | tinyint(1)  | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)



**************************************
* NOTE TO SELF: ON WINDOWS     *
* go to C:\xampp\mysql\bin     *
* open mysqld                  *
* close cmd                    *
* cd C:\xampp\mysql\bin        *
* mysql.exe -u root -p         *
*                                    *
**************************************

SELECT * FROM subjects WHERE visible = 0;

SELECT * FROM subjects WHERE visible = 1;

SELECT * FROM subjects WHERE visible = 1 ORDER BY position ASC;

SELECT * FROM subjects WHERE id = 2;

SELECT menu_name, position FROM subjects WHERE visible = 1;

UPDATE subjects
SET visible = 1
WHERE id = 4;

SELECT * FROM subjects;

UPDATE subjects SET visible = 0;

UPDATE subjects SET visible = 1 WHERE id < 4;

DELETE FROM subjects WHERE id = 4;


**********************

RELATIONAL DATABASES

one-to-many relationships


CREATE TABLE pages (
id INT(11) NOT NULL AUTO_INCREMENT,
subject_id INT(11) NOT NULL,
menu_name VARCHAR(30) NOT NULL,
position INT(3) NOT NULL,
visible TINYINT(1) NOT NULL,
content TEXT,
PRIMARY KEY (id),
INDEX (subject_id)
);

SHOW TABLES;

SHOW COLUMNS FROM pages;

****************************************
POPULATING THE RELATIONAL DATABASE

INSERT INTO pages (subject_id, menu_name, position, visible, content)
VALUES (1, 'Our Mission', 1, 1, 'Our mission has always been...');

INSERT INTO pages (subject_id, menu_name, position, visible, content)
VALUES (1, 'Our History', 2, 1, 'Founded in 1898 by two enterprising engineers...');

INSERT INTO pages (subject_id, menu_name, position, visible, content)
VALUES (1, 'Services', 3, 1, 'Our mission has always been...');

INSERT INTO pages (subject_id, menu_name, position, visible, content)
VALUES (2, 'Large Widgets', 1, 1, 'Our large widgets have to be seen to be believed...');

INSERT INTO pages (subject_id, menu_name, position, visible, content)
VALUES (2, 'Small Widgets', 2, 1, 'They say that big things come in small packages...');

INSERT INTO pages (subject_id, menu_name, position, visible, content)
VALUES (2, 'Retrofitting', 3, 1, 'We can certify any widget, not just our own...');

INSERT INTO pages (subject_id, menu_name, position, visible, content)
VALUES (3, 'Certification', 2, 1, 'We can certify any widget, not just our own...');

SELECT * FROM pages;

SELECT * FROM subjects;

SELECT * FROM pages WHERE id = 1;

SELECT * FROM subjects WHERE id = 1;

SELECT * FROM pages WHERE subject_id = 3;





DATABASE APIs

mysql - Original MySQL API
mysqli - MySQL improved API
PDO - PHP Data Objects
- works on other databases

PHP Database Interaction in Five Steps

1 Create a database connection
2 Perform database query
3 Use returned data (if any)
4 Release returned data
5 Close the database connection


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

mysqli_query()
mysqli_fetch_row()
mysqli_free_result()

mysqli_fetch_row
- results are in a standard array
- keys are integers

mysqli_fetch_assoc
- results are in an associative array
- keys are column names

mysqli_fetch_array
- results in either or both types of arrays
- MYSQL_NUM, MYSQL_ASSOC, MYSQL_BOTH

mysqli_insert_id()
$id = mysqli_insert_id($connection);


PROTECT FROM SQL INJECTION
mysqli_real_escape_string();
$menu_name = mysqli_real_escape_string($connection, $menu_name);




PREPARED STATEMENTS  ? ? ?

prepare statements once, reuse many times
faster
separate query from dynamic data
prevents SQL injection


$query = "SELECT id,first_name,last_name ";
$query .= "FROM users ";
$query .= "WHERE username = ? AND password = ?";
$stmt = mysqli_prepare($connection, $query);

mysqli_stmt_bing_param($stmt, 'ss', $username, $password);

mysqli_stmt_execute(stmt);

mysqli_stmt_bind_result($stmt, $id, $first_name, $last_name);

mysqli_stmt_fetch($stmt);

mysqli_stmt_close($stmt);