php mysql cursor_PHP中的MySQL使用--基于PDO

一、准备活动

PHP Data Object 数据库访问抽象层 统一各种数据库访问接口

1.查看PHP的配置信息

调用一个函数即可输出一个界面。默认PDO是支持MySQL的

phpinfo();

如果不支持，在php.ini中打开选项即可

2.连接数据库

2.1：方式1 写死在代码里

|-- ---------------

$dsn = 'mysql:host=localhost;dbname=datatype';//数据源

$user = 'root';

$pwd = 'xxxxx';

$conn = new PDO($dsn, $user, $pwd);

var_dump($conn);//object(PDO)#1 (0) { }

复制代码

2.2：方式2 写一个文件决定数据库

---->[pdo/pdo_conn.php]------------------

$path = __DIR__.'\config.txt';

$dsn = 'uri:file://' . $path . '';//数据源

$user = 'root';

$pwd = 'xxxxx';

$conn = new PDO($dsn, $user, $pwd);

var_dump($conn);//object(PDO)#1 (0) { }

---->[pdo/config.txt]------------------

mysql:dbname=datatype;host=localhost

3.执行语句exec() 创建表

不支持查询操作，返回受影响的行数。数据表使用此文中的pic表：MySQL指南之SQL语句基础

try {

$dsn = 'mysql:host=localhost;dbname=datatype';//数据源

$user = 'root';

$pwd = 'toly';

$conn = new PDO($dsn, $user, $pwd);

//---------------------建表--------------------------

$sql_create_table = <<

CREATE TABLE IF NOT EXISTS php_pic(

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

pic_path VARCHAR(120) NOT NULL,

pic_length INT UNSIGNED DEFAULT 0,

pic_mime TINYINT UNSIGNED,

pic_width SMALLINT UNSIGNED,

pic_height SMALLINT UNSIGNED

);

EOT;

$len = $conn->exec($sql_create_table);

echo $len;//0

} catch (Exception $e) {

$e->getMessage();

}

mysql> SHOW TABLES;

+--------------------+

| Tables_in_datatype |

+--------------------+

| php_pic |

+--------------------+

mysql> DESC php_pic;

+------------+----------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+------------+----------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| pic_path | varchar(120) | NO | | NULL | |

| pic_length | int(10) unsigned | YES | | 0 | |

| pic_mime | tinyint(3) unsigned | YES | | NULL | |

| pic_width | smallint(5) unsigned | YES | | NULL | |

| pic_height | smallint(5) unsigned | YES | | NULL | |

+------------+----------------------+------+-----+---------+----------------+

二、增删改查

1.增加记录

//---------------------插入记录--------------------------

$sql_insert = <<

INSERT INTO pic(pic_path,pic_length,pic_mime,pic_width,pic_height) VALUES

('30000X20000.jpg',116342886,1,30000,20000),

('3000X2000.jpg',3404969,1,3000,2000),

('300X200.jpg',99097,1,300,200),

('30X20.jpg',10158,1,30,20),

('6dc9e8455c47d964e1a8a4ef04cf9477.jpg',236254,1,974,319);

EOT;

$len = $conn->exec($sql_insert);

echo $len;//5

---->[命令行]------------------

mysql> SELECT * FROM php_pic;

+----+--------------------------------------+------------+----------+-----------+------------+

| id | pic_path | pic_length | pic_mime | pic_width | pic_height |

+----+--------------------------------------+------------+----------+-----------+------------+

| 1 | 30000X20000.jpg | 116342886 | 1 | 30000 | 20000 |

| 2 | 3000X2000.jpg | 3404969 | 1 | 3000 | 2000 |

| 3 | 300X200.jpg | 99097 | 1 | 300 | 200 |

| 4 | 30X20.jpg | 10158 | 1 | 30 | 20 |

| 5 | 6dc9e8455c47d964e1a8a4ef04cf9477.jpg | 236254 | 1 | 974 | 319 |

+----+--------------------------------------+------------+----------+-----------+------------+

2.修改记录

//---------------------修改记录--------------------------

$sql_update = <<

UPDATE php_pic SET pic_height=10086,pic_width=2333

WHERE id =5;

EOT;

$len = $conn->exec($sql_update);//1

---->[命令行]------------------

mysql> SELECT * FROM php_pic;

+----+--------------------------------------+------------+----------+-----------+------------+

| id | pic_path | pic_length | pic_mime | pic_width | pic_height |

+----+--------------------------------------+------------+----------+-----------+------------+

| 1 | 30000X20000.jpg | 116342886 | 1 | 30000 | 20000 |

| 2 | 3000X2000.jpg | 3404969 | 1 | 3000 | 2000 |

| 3 | 300X200.jpg | 99097 | 1 | 300 | 200 |

| 4 | 30X20.jpg | 10158 | 1 | 30 | 20 |

| 5 | 6dc9e8455c47d964e1a8a4ef04cf9477.jpg | 236254 | 1 | 2333 | 10086 |

+----+--------------------------------------+------------+----------+-----------+------------+

3.删除记录

//---------------------删除记录--------------------------

$sql_delete = <<

DELETE FROM php_pic

WHERE pic_width> 2500;

EOT;

$len = $conn->exec($sql_delete);//2

echo $len;

---->[命令行]------------------

mysql> SELECT * FROM php_pic;

+----+--------------------------------------+------------+----------+-----------+------------+

| id | pic_path | pic_length | pic_mime | pic_width | pic_height |

+----+--------------------------------------+------------+----------+-----------+------------+

| 3 | 300X200.jpg | 99097 | 1 | 300 | 200 |

| 4 | 30X20.jpg | 10158 | 1 | 30 | 20 |

| 5 | 6dc9e8455c47d964e1a8a4ef04cf9477.jpg | 236254 | 1 | 2333 | 10086 |

+----+--------------------------------------+------------+----------+-----------+------------+

关于错误信息的获取

$sql_delete = <<

DELETE FROM php_picXXX

WHERE pic_width> 2500;

EOT;

$len = $conn->exec($sql_delete);//2

if ($len === false) {