PHP - PDO MySQL

<?php

$hostname = 'localhost';
$username = 'root';
$password = 'indonesia';

try{
    $db = new PDO("mysql:host=$hostname;dbname=a", $username, $password);
    echo 'Connected to database';
    $db = null;
}catch(PDOException $e){
    echo $e->getMessage();
}

PHP - PDO MySQL

PHP - Get the result of MySQL

mysql_fetch_row

    Array ( [0] => 2 [1] => corolla [2] => toyota ) 

mysql_fetch_assoc

    Array ( [id] => 3 [brand] => accord [owner] => honda ) 

mysql_fetch_array

    Array ( [0] => 1 [id] => 1 [1] => saab [brand] => saab [2] => gm [owner] => gm ) 

mysql_fetch_object

    stdClass Object ( [id] => 4 [brand] => kijang [owner] => toyota )  

PHP - Get the result of MySQL

MySQL - View

If you have a base table like this one:
Table car:

   id INT NOT NULL AUTO_INCREMENT KEY,

   brand CHAR(50) NOT NULL UNIQUE

   owner CHAR(50) NOT NULL DEFAULT ''

Then you can have the view like this one:

mysql> CREATE VIEW v_car

       AS SELECT * FROM car

mysql> CREATE VIEW v_car

                 AS SELECT DISTINCT(owner) FROM car

mysql> CREATE VIEW v_car (nama, pemilik)

       AS SELECT brand, owner FROM car

MySQL - View

MySQL - Function

mysql> CREATE FUNCTION f()

       RETURNS int DETERMINISTIC

       RETURN 2;

mysql> SELECT f();

mysql> CREATE FUNCTION addTwo(x INT)

       RETURNS int DETERMINISTIC

       RETURN x + 2;

mysql> SELECT addTwo(3);

mysql> CREATE FUNCTION maxId()

       RETURNS int DETERMINISTIC

       RETURN (SELECT MAX(id) FROM data);

mysql> SELECT maxId();

MySQL - Function

MySQL - Stored Procedure

Look directly to this example:

mysql> DELIMITER //

mysql> CREATE PROCEDURE p()

       BEGIN

           SELECT * FROM data;

       END //

mysql> DELIMITER //

mysql> CALL p()

Other example

mysql> CREATE PROCEDURE p(a INT)

       BEGIN

           SELECT * FROM data WHERE id = a;

       END //

mysql> CREATE PROCEDURE p(a INT)

       BEGIN

           SET @add = 1;

           SELECT * FROM data WHERE id = @add + a;

       END //

MySQL - Stored Procedure

MySQL - Trigger

For example you have two table:
1. data
    id int not null auto_increment key,
    username char(50) not null default ''
2. meta
    value int

The syntax for trigger is:

mysql> CREATE TRIGGER t BEFORE INSERT ON data

       FOR EACH ROW INSERT INTO meta VALUES (5);

Trigger time can be one: BEFORE or AFTER
Trigger even can be one: UPDATE, INSERT OR DELETE

MySQL - Trigger

MySQL - Export Import Data File

Here's a code for export table data from mysql to a file:

mysql> SELECT * FROM data INTO OUTFILE 'C:/hello.sql'

And here's on how to import data from a file:
mysql> LOAD DATA LOCAL INFILE 'C:/hello.sql' INTO TABLE data;

MySQL - Export Import Data File

MySQL - INNER JOIN

mysql> CREATE TABLE user

       (id INT NOT NULL AUTO_INCREMENT KEY,

       name CHAR(20));

mysql> CREATE TABLE sayur

       (id INT NOT NULL AUTO_INCREMENT KEY,

       name CHAR(10),

       uid INT);

mysql> SELECT user.name, sayur.name FROM user,sayur WHERE user.id

       = sayur.uid

MySQL - INNER JOIN

MySQL - LEFT OUTER JOIN

mysql> CREATE TABLE user

       id INT NOT NULL AUTO_INCREMENT KEY,

       name CHAR(20));

mysql> CREATE TABEL sayur

       (id INT NOT NULL AUTO_INCREMENT KEY,

       name CHAR(10));

mysql> CREATE TABLE connect

       (id INT NOT NULL AUTO_INCREMENT KEY,

       uid INT,

       sid INT);

The you use left outer join to select the related data

mysql> SELECT name.user, name.sayur FROM user, sayur, connect

       WHERE user.id = normal.uid AND sayur.id = normal.sid;

MySQL - LEFT OUTER JOIN

MySQL - ALTER TABLE

There are numerous MySQL syntax on ALTER TABLE and this is powerful one:
1. Add a column (first, before, after)

mysql> ALTER TABLE data ADD city CHAR(50) NOT NULL DEFAULT '' AFTER name;

2. Remove a column

mysql> ALTER TABLE data DROP city

3. Redefine and rename a column

mysql> ALTER TABLE data CHANGE city town char(50) not null default '';

4. Make a column to a new type
mysql> ALTER TABLE data MODIFY city INT NOT NULL DEFAULT 0;

5. Create an index

msyql> ALTER TABLE data ADD INDEX index_id (id);

6. Create a unique

mysql> ALTER TABLE data ADD UNIQUE name_people(name);

7. Define a default value
mysql> ALTER TABLE data ALTER name SET DEFAULT 'no name';

8. Drop primary key
mysql> ALTER TABLE data DROP PRIMARY KEY;

9. Drop index

mysql> ALTER TABLE data DROP INDEX index_id;

10. Remove default value
mysql> ALTER TABLE data ALTER city DROP DEFAULT

11. Change table type
mysql> ALTER TABLE data TYPE=MERGE

MySQL - ALTER TABLE

MySQL - Granting The User

To give all grant privileges for all database, use
mysql> GRANT ALL PRIVILEGES ON *.* TO 'luna'@'localhost'
       WITH GRANT OPTION;

To grant SELECT, UPDATE, INSERT for account database, use
mysql> GRANT SELECT, UPDATE, INSERT, DELETE, CREATE, DROP ON
       account.* TO 'luna'@'localhost';

To check the grant a user has, use
mysql> SHOW GRANTS FOR 'luna'@'localhost';

MySQL - Granting The User

MySQL - Creating User

There are many ways to create user in MySQL database:
without password:

mysql> CREATE USER 'luna'@'localhost';

with password:

mysql> CREATE USER 'luna'@'localhost' IDENTIFIED BY 'maya'

If you want the user can access from anywhare of the client host, use
mysql> CREATE USER 'luna'@'%' IDENTIFIED BY 'maya';

MySQL - Creating User

MySQL - Setting User Password

To set user password, for example the user is root:
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('indonesia')

MySQL - Setting User Password

MySQL Revoke User

With REVOKE statement, you hope that some grants will be removed.

mysql> REVOKE insert ON *.* FROM 'luna'@'localhost';

Query OK, 0 rows affected (0.02 sec)

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'luna'@'localhost';

Query OK, 0 rows affected (0.02 sec)

MySQL Revoke User

MySQL Giving Privileges To User

Give all privileges to 'luna'@'localhost':

mysql> GRANT ALL PRIVILEGES ON *.* TO 'luna'@'localhost' 

       WITH GRANTS OPTION;

Query OK, 0 rows affected (0.00 sec)

To show what the grant of a specified user, please type:

mysql> SHOW GRANTS FOR 'luna'@'localhost';

+------------------------------------------+
| Grants for luna@localhost                |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'luna'@'localhost' |
+------------------------------------------+
1 row in set (0.00 sec)

To give some grants, please type:

mysql> GRANTS SELECT, UPDATE, INSERT, DELETE, CREATE, DROP ON

       bankAccount.* TO 'luna'@'localhost';

Query OK, 0 rows affected (0.01 sec)

To give some grants for a table, type:

mysql> GRANTS SELECT, UPDATE, INSERT, DELETE, CREATE, DROP ON

       customer.data TO 'luna'@'localhost';

Query OK, 0 rows affected (0.01 sec)

               

MySQL Giving Privileges To User

MySQL - Basic User Administration

The most basic type is:

mysql> CREATE USER 'luna'@'localhost';

Query OK, 0 rows affected (0.03 sec)

When password is needed, type:

mysql> CREATE USER 'luna'@'localhost' IDENTIFIED BY 'password';

Query OK, 0 rows affected (0.00 sec)

To remove a user, type:

mysql> DROP USER 'luna'@'localhost';

Query OK, 0 rows affected (0.00 sec)

MySQL - Basic User Administration

MySQL Stored Procedure

This code will create a simple stored procedure:

mysql> DELIMITER //

mysql> CREATE PROCEDURE getData()

mysql> BEGIN

mysql> SELECT * FROM data;

mysql> END //

mysql> DELIMITER ;

To use it, type:
mysql> call getData();

Variable:
To declare a variable in stored procedure, the rule is:
DECLARE varName DATATYPE(size) DEFAULT defValue;
mysql> DECLARE x, y INT DEFAULT 0;

Then you assign the value to variable:
msyql> SET x = 8;

Complete example:

mysql> DECALRE total INT;

mysql> SELECT COUNT(*) INTO total FROM data;

Parameter:

There's 3 type of variable: IN, OUT, INOUT
To use parameter, use:

mysql> DELIMITER //

mysql> CREATE PROCEDURE getOneLine(IN namee CHAR(20))

mysql> BEGIN

mysql> SELECT * FROM data

mysql> WHERE name = namee;

mysql> END //

mysql> DELIMITER ;

Then call it:
mysql> call getOneLine('lady gaga');

Second example:
mysql> DELIMITER //

mysql> CREATE PROCEDURE getCity(IN nCity CHAR(20), OUT n INT)

mysql> BEGIN

mysql> SELECT COUNT(*) INTO n FROM data

mysql> WHERE city = nCity;

mysql> END //

mysql> DELIMITER ;

To call it, use:

mysql> call getCity('sydney',@total);

mysql> select @total as total_city;

MySQL Stored Procedure

MySQL - Table

To create a table, use:

mysql> CREATE TABLE data(

    -> id INT NOT NULL AUTO_INCREMENT KEY,

    -> name CHAR(20),

    -> city CHAR(20));

To lookup the structure of the table,use:

mysql> DESC data;    # argument is table name

To insert some data, type

mysql> INSERT INTO data

    -> values

    -> (null,'steve jobs','cupertino'),

    -> (null,'larry page','mountain view'),

    -> (null,'aura kasih','jakarta');

or

mysql> INSERT INTO data (name, city)

      -> values

    -> ('lady gaga','new york');

To lookup what the contents of the table, type:

mysql> SELECT * FROM data;     // argument is table name

or 

mysql> SELECT name, city FROM data;    // based on columns name

or

mysql> SELECT * FROM data ORDER BY id ASC;    // or DESC

or 

mysql> SELECT * FROM data WHERE id>2;     // then ORDER BY

To delete a content of data,type

mysql> DELETE FROM satu

    -> WHERE id=1;           // delete steve jobs rows

To update a content of data, type

mysql> UPDATE satu

    -> SET name='luna maya', city='denpasar'

    -> WHERE id=2;           // update larry page

MySQL - Table

MySQL - Basic

If you use xampp, type 

>> mysql_start.bat     // in command prompt

After the MySQL server running, type

>> mysql -u root

or

>> mysql -u root -p    // with password

After you are login, select create a database

mysql> create database satu;

then

mysql> use satu;

When you want to quit, type

mysql> quit

and shut down mysql_server

>> mysql_stop.bat

MySQL - Basic

Mysql Syntaxs

To declare and automatically assign a variable outside of a procedure use:
mysql> select 45 into @a;
mysql> select @a;

To give a title of a result use:
mysql> select 45 'nilai';

Mysql Syntaxs