PHP PDO用法

PDO含义:PHP data objects。

建立连接和基本Query

<?php
try {
    $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
    foreach($dbh->query('SELECT * from FOO') as $row) {
        print_r($row);
    }
    $dbh = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

使用事务

try {  
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $dbh->beginTransaction();
  $dbh->exec("insert into staff (id, first, last) values (23, 'Joe', 'Bloggs')");
  $dbh->exec("insert into salarychange (id, amount, changedate) 
      values (23, 50000, NOW())");
  $dbh->commit();

} catch (Exception $e) {
  $dbh->rollBack();
  echo "Failed: " . $e->getMessage();
}

预处理语句insert

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();

预处理语句query

<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
if ($stmt->execute(array($_GET['name']))) {
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
}

mysql having和where的区别

where不能筛选通过函数计算出来的列。

统计消费过100元以上的用户:

错误1

select userid, ordernum, sum(price) from orders group by (userid) where sum(price) > 100;

错误2(设置别名也不行)

select userid, ordernum, sum(price) as spent from orders group by (userid) where spent > 100;

正确:

select userid, ordernum, sum(price) from orders group by (userid) having sum(price) > 100;

docker-compose搭建apache结合多个php版本同时运行的环境

apache结合php有多种方式,如果需要让apache能同时使用多个php版本,经过自己的了解,应该只能使用apache with php-fpm模式运行。

首先创建两个php的service(service中与本文无关的参数已经去除,方便理解):

// docker-compose.yml
version: '3'

services:
    php56:
      container_name: php56
      build:
        context: php56
      expose:
        - "9000"
    php72:
      container_name: php72
      build:
        context: php72
      expose:
        - "9000"
   apache2:
      container_name: apache2
      build:
        context: ./apache2
      ports:
        - "80:80"
        - "443:443"
      depends_on:
        - php56
        - php72
      networks:
        default:
          aliases:
            - "a.com"
            - "b.com"

然后创建对应php容器的文件夹以及Dokcerfile,每个php一个容器,例如:

// php72/Dockerfile
FROM jakesoft/php:7.2-centos

USER root

EXPOSE 9000

CMD ["php-fpm", "-F"]

php56/Dokcerfile与上面的文件类似

但是需要注意一点的是php-fpm如果想监听非本机环境的请求时,需要修改容器中的/etc/php-fpm.d/www.conf

listen = 0.0.0.0:9000
;listen.allowed_clients = 127.0.0.1 // 这一行前面增加";"注释掉

(所有的php容器都需要做此修改)

现在去修改apache的站点配置:

a.com 使用php5.6

<VirtualHost *:80>
    DocumentRoot "/var/www/a"
    ServerName   a.com

    <FilesMatch .*$>
        SetHandler "proxy:fcgi://php56:9000"
    </FilesMatch>
</VirtualHost>

b.com 使用php7.2

<VirtualHost *:80>
    DocumentRoot "/var/www/b"
    ServerName   b.com

    <FilesMatch .*$>
        SetHandler "proxy:fcgi://php72:9000"
    </FilesMatch>
</VirtualHost>

启动:

docker-compose up --build apache2

大功告成!

编译安装php7.4

以前在我的centos7服务器上编译安装了php7.3,这一次先打算在自己的mac上试一试php7.4。

# 下载源码
wget https://www.php.net/distributions/php-7.4.1.tar.bz2

# 解压&切换到目录下
tar -jxvf php-7.4.1.tar.bz2 && cd php-7.4.1

# 执行configure
./configure --prefix=/usr/local/opt/php@7.4 --with-config-file-path=/usr/local/etc/php/7.4 --enable-fpm --with-fpm-user=_www --with-fpm-group=_www --enable-intl --enable-mbstring --enable-pcntl --enable-mysqlnd

报错:

checking for icu-uc >= 50.1 icu-io icu-i18n... no
configure: error: Package requirements (icu-uc >= 50.1 icu-io icu-i18n) were not met:

No package 'icu-uc' found
No package 'icu-io' found
No package 'icu-i18n' found

Consider adjusting the PKG_CONFIG_PATH environment variable if you
installed software in a non-standard prefix.

Alternatively, you may set the environment variables ICU_CFLAGS
and ICU_LIBS to avoid the need to call pkg-config.
See the pkg-config man page for more details.

# 执行
export PKG_CONFIG_PATH="/usr/local/opt/icu4c/lib/pkgconfig"

# 再次configurre
./configure --prefix=/usr/local/opt/php@7.4 --with-config-file-path=/usr/local/etc/php/7.4 --enable-fpm --with-fpm-user=_www --with-fpm-group=_www --enable-intl --enable-mbstring --enable-pcntl --enable-mysqlnd

报错:

configure: error: Package requirements (oniguruma) were not met:

No package 'oniguruma' found

Consider adjusting the PKG_CONFIG_PATH environment variable if you
installed software in a non-standard prefix.

Alternatively, you may set the environment variables ONIG_CFLAGS
and ONIG_LIBS to avoid the need to call pkg-config.
See the pkg-config man page for more details.

# 安装oniguruma

> 参考:http://macappstore.org/oniguruma/

brew install oniguruma

# 再次configurre
./configure --prefix=/usr/local/opt/php@7.4 --with-config-file-path=/usr/local/etc/php/7.4 --enable-fpm --with-fpm-user=_www --with-fpm-group=_www --enable-intl --enable-mbstring --enable-pcntl --enable-mysqlnd

## 最后
make && make install