PHP+MariaDBでDB連携(基本編)

PHP DB

PHPでDB連携をしてみます。

まず、MariaDBで下記の準備をします。

  • ユーザを作成(CREATE USER)
  • ユーザに全権限を付与(GRANT ALL PRIVILEGES)
  • データベースの作成(CREATE DATABASE)
  • テーブルの作成(CREATE TABLE)
  • レコードの登録(INSERT)
F:\Program\xampp\mysql\bin>mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 33
Server version: 10.1.38-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE USER 'user001'@'localhost' IDENTIFIED BY 'pass001';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'user001'@'localhost' IDENTIFIED BY 'pass001' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit
Bye

F:\Program\xampp\mysql\bin>mysql -u user001 -h localhost -p
Enter password: *******
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 34
Server version: 10.1.38-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE DB001;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE DB001;
Database changed
MariaDB [DB001]> CREATE TABLE TBL001(userid VARCHAR(64), password VARCHAR(64));
Query OK, 0 rows affected (0.16 sec)

MariaDB [DB001]> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1　　　　　　　|
+--------------+
1 row in set (0.00 sec)

MariaDB [DB001]> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [DB001]> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0　　　　　　 |
+--------------+
1 row in set (0.00 sec)

MariaDB [DB001]> INSERT INTO tbl001 VALUES('guest', 'guest');
Query OK, 1 row affected (0.00 sec)

MariaDB [DB001]> COMMIT;
Query OK, 0 rows affected (0.02 sec)

MariaDB [DB001]> SELECT * FROM tbl001;
+--------+----------+
| userid | password |
+--------+----------+
| guest　| guest　　 |
+--------+----------+
1 row in set (0.00 sec)

MariaDB [DB001]> exit
Bye

F:\Program\xampp\mysql\bin>

 

 続いて、DBにSELECT文を発行するサンプルを載せる。

<html>
<body>
<?php
$mysqli = new mysqli("localhost", "user001", "pass001", "db001");
$result = $mysqli->query("SELECT userid, password FROM tbl001");
$row = $result->fetch_assoc();
echo 'user=' . htmlentities($row['userid']) . '<br>';
echo 'pass=' . htmlentities($row['password']) . '<br>';
$result->free();
$mysqli->close();
?>
</body>
</html>

 

・ブラウザからこのPHPスクリプトにアクセスした実行結果

user=guest
pass=guest

 

 

---------------------------------------
 ■ITとことんのトップページ
 ┗■PHPのトップページ
  　 ┗■本ページ