PHP + MySQL

Preparation

Installation

$ mkdir php+mysql
$ sudo aptitude update
$ sudo aptitude install apach2 mysql-server php5 php5-mysql
## Add user to MySQL
$ mysql -u root -p
mysql> use mysql 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into user (host,user,password) value('%','nchuweb',password('phpmysql'));
Query OK, 1 row affected, 3 warnings (0.00 sec)
mysql> GRANT ALL ON *.* TO 'nchuweb'@localhost IDENTIFIED BY 'phpmysql' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
## Give some permissions in database for user
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE ON dbname.* TO 'killme'@localhost IDENTIFIED BY 'killpwd';
  

Database Design

## Create database
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| webtest            |
+--------------------+
5 rows in set (0.00 sec)
mysql> CREATE DATABASE dbname;
mysql> USE dbname
Database changed
## Create table
mysql> SHOW TABLES;
Empty set (0.00 sec)
mysql> CREATE TABLE tbname (Id smallint not null auto_increment, Account varchar(12) binary NOT NULL, Name varchar(12) NOT NULL, PRIMARY KEY (Id));
Query OK, 0 rows affected (0.07 sec)
mysql> SHOW TABLES;
+-------------------+
| Tables_in_webtest |
+-------------------+
| tbname            |
+-------------------+
1 row in set (0.00 sec)
mysql> DESCRIBE tbname;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| Id      | smallint(6) | NO   | PRI | NULL    | auto_increment |
| Account | varchar(12) | NO   |     | NULL    |                |
| Name    | varchar(12) | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> INSERT INTO tbname VALUES ('','MyAcc','MyName');
Query OK, 1 row affected, 1 warning (0.04 sec)
mysql> SELECT * FROM tbname;
+----+---------+--------+
| Id | Account | Name   |
+----+---------+--------+
|  1 | MyAcc   | MyName |
+----+---------+--------+
1 row in set (0.00 sec)
mysql> ALTER TABLE tbname rename mytable;
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW TABLES;
+-------------------+
| Tables_in_webtest |
+-------------------+
| mytable           |
+-------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE mytable ADD COLUMN Date datetime;
Query OK, 1 row affected (0.19 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> DESCRIBE mytable;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| Id      | smallint(6) | NO   | PRI | NULL    | auto_increment |
| Account | varchar(12) | NO   |     | NULL    |                |
| Name    | varchar(12) | NO   |     | NULL    |                |
| Date    | datetime    | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE mytable DROP COLUMN Name;
Query OK, 1 row affected (0.28 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> DESCRIBE mytable;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| Id      | smallint(6) | NO   | PRI | NULL    | auto_increment |
| Account | varchar(12) | NO   |     | NULL    |                |
| Date    | datetime    | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE mytable MODIFY COLUMN Date timestamp DEFAULT now();
Query OK, 1 row affected (0.19 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> DESCRIBE mytable;
+---------+-------------+------+-----+-------------------+----------------+
| Field   | Type        | Null | Key | Default           | Extra          |
+---------+-------------+------+-----+-------------------+----------------+
| Id      | smallint(6) | NO   | PRI | NULL              | auto_increment |
| Account | varchar(12) | NO   |     | NULL              |                |
| Date    | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
+---------+-------------+------+-----+-------------------+----------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM mytable;
+----+---------+---------------------+
| Id | Account | Date                |
+----+---------+---------------------+
|  1 | MyAcc   | 2012-05-29 10:15:24 |
+----+---------+---------------------+
1 row in set (0.00 sec)
  

Database Manager

$ sudo aptitude install phpmyadmin
┌────────────────────────────────┤ Configuring phpmyadmin ├────────────────────────────────┐ 
│ Please choose the web server that should be automatically configured to run phpMyAdmin.  │ 
│ Web server to reconfigure automatically:                                                 │ 
│    [*] apache2                                                                           │ 
│    [ ] lighttpd                                                                          │ 
│                                                                                          │ 
│                                          <Ok>                                            │ 
│                                                                                          │ 
└──────────────────────────────────────────────────────────────────────────────────────────┘ 
phpmyadmin page

Form

HTML Form

PHP form accept

<?php
if(count($_POST)>0){ 
  foreach($_POST as $name=>$value){
    echo $name."=".$value."<br />"; 
  } 
}
?>

Connect to MySQL Server

<?php
$linkdb=mysql_connect("localhost", "account", "password");
mysql_select_db("database"); 
?>

MySQL functions

  1. mysql_query("create table $x ($y);");
  2. mysql_query("insert into $x value ($y);");
  3. mysql_query("update $x set $y where $z;");
  4. mysql_query("select * from $x;");
  5. mysql_query("select * from $x;");
  6. mysql_query("drop table $x;");
  7. $sql1=mysql_query("select * from Fight where UID=$x;");
  8. mysql_fetch_row($sql1);

ChiSheng Su