Database error: failed to registerが、解決できない

前提

ログインシステムを作成しています。
ユーザー情報をテーブルにINSERTする工程で問題が発生しています。

実現したいこと

usersテーブルにユーザー情報を格納したい
エラーを解消したい

発生している問題・エラーメッセージ

// signup.php
Database error: failed to register

該当のソースコード

PHP
1// authController.php
2
3<?php
4ini_set('display_errors', 1);
5
6session_start();
7require '../config/db.php';
8
9$errors = array();
10$username = '';
11$email = '';
12
13if (isset($_POST['signup-btn'])) {
14    $username = $_POST['username'];
15    $email = $_POST['email'];
16    $password_1 = $_POST['password_1'];
17    $password_2 = $_POST['password_2'];
18
19    if (empty($username)) {
20        $errors['username'] = "Username required";
21    }
22    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
23        $errors['email'] = "Email address invalid";
24    }
25    if (empty($email)) {
26        $errors['email'] = "Email required";
27    }
28    if (empty($password_1)) {
29        $errors['password'] = "Password required";
30    }
31    if ($password_1 !== $password_2) {
32        $errors['password'] = "The two password do not match";
33    }
34
35    $emailQuery = "SELECT * FROM users WHERE email = ? LIMIT 1";
36    $statement = $conn->prepare($emailQuery);
37    $statement->bind_param('s', $email);
38    $statement->execute();
39    $result = $statement->get_result();
40    $userCount = $result->num_rows;
41
42    $statement->close();
43
44    if ($userCount > 0) {
45        $errors['email'] = "Email already exists";
46    }
47
48    if (count($errors) === 0) {
49        $password = password_hash($password_1, PASSWORD_DEFAULT);
50        $token = bin2hex(random_bytes(50));
51        $verified = false;
52
53        $sql = "INSERT INTO users (username, email, verified, token, password) VALUES (?, ?, ?, ?, ?)";
54        $statement = $conn->prepare($sql);
55        $statement->bind_param('ssbss', $username, $email, $verified, $token, $password);
56        if ($statement->execute()) {
57            $user_id = $conn->insert_id;
58            $_SESSION['id'] = $user_id;
59            $_SESSION['username'] = $username;
60            $_SESSION['email'] = $email;
61            $_SESSION['verified'] = $verified;
62
63            $_SESSION['message'] = "You are now logged in!";
64            $_SESSION['alert-class'] = "alert-success";
65            header('location: index.php');
66            exit();
67        } else {
68            $errors['db_error'] = "Database error: failed to register";
69        }
70    }
71}

PHP
1// signup.php
2
3<?php
4require_once '../controllers/authController.php';
5
6?>
7
8<!DOCTYPE html>
9<html lang="en">
10
11<head>
12    <meta charset="UTF-8">
13    <meta http-equiv="X-UA-Compatible" content="IE=edge">
14    <meta name="viewport" content="width=device-width, initial-scale=1.0">
15    <title>Register</title>
16</head>
17
18<body>
19    <div>
20        <div>
21            <form action="signup.php" method="POST">
22                <h3>Register</h3>
23                <?php if (count($errors) > 0) : ?>
24                    <div>
25                        <?php foreach ($errors as $error) : ?>
26                            <li><?php echo $error; ?></li>
27                        <?php endforeach; ?>
28                    </div>
29                <?php endif; ?>
30                <div>
31                    <label for="username">Username</label>
32                    <input type="text" name="username" value="<?php echo $username; ?>">
33                </div>
34                <div>
35                    <label for="email">Email</label>
36                    <input type="email" name="email" value="<?php echo $email; ?>">
37                </div>
38                <div>
39                    <label for="password_1">Password</label>
40                    <input type="password" name="password_1">
41                </div>
42                <div>
43                    <label for="password_2">Confirm Password</label>
44                    <input type="password" name="password_2">
45                </div>
46                <div>
47                    <button type="submit" name="signup-btn">Sign Up</button>
48                </div>
49                <p>Already a member? <a href="login.php">Sign In</a></p>
50            </form>
51        </div>
52    </div>
53</body>
54
55</html>

PHP
1// index.php
2
3<!DOCTYPE html>
4<html lang="en">
5
6<head>
7    <meta charset="UTF-8">
8    <meta http-equiv="X-UA-Compatible" content="IE=edge">
9    <meta name="viewport" content="width=device-width, initial-scale=1.0">
10    <title>Homepage</title>
11</head>
12
13<body>
14    <div>
15        <div>
16            <div>
17                You are now logged in!
18            </div>
19
20            <h3>Welcome, Awa</h3>
21
22            <a href="#">logout</a>
23
24            <div>
25                You need to verify your account.
26                Sign in to your email account and click on the
27                verification link we just emailed you at
28                <strong>test@test.com</strong>
29            </div>
30        </div>
31
32        <button>I am verified!</button>
33    </div>
34</body>
35
36</html>

PHP
1// db.php
2
3<?php
4require 'constants.php';
5
6$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
7
8if ($conn->connect_error) {
9    die("Database error:" . $conn->connect_error);
10}

MySQL
1mysql> show columns from users;
2+----------+--------------+------+-----+---------+-------+
3| Field    | Type         | Null | Key | Default | Extra |
4+----------+--------------+------+-----+---------+-------+
5| id       | int          | NO   | PRI | NULL    |       |
6| username | varchar(100) | YES  |     | NULL    |       |
7| email    | varchar(200) | YES  | UNI | NULL    |       |
8| verified | tinyint      | YES  |     | NULL    |       |
9| token    | varchar(100) | YES  |     | NULL    |       |
10| password | varchar(255) | YES  |     | NULL    |       |
11+----------+--------------+------+-----+---------+-------+

試したこと

authController.phpに、ini_set('display_errors', 1);を記述した。
DBのエラー情報が返ってこないのでどこに問題があるのか分からない。

DBのホスト情報などが記載されているファイルではエラーが起きていない事を確認済み。

補足情報（FW/ツールのバージョンなど）

MySQL PHP MAMP MacOS

行動規範の内容に同意します

回答2件

php
1$statement->bind_param('ssbss', $username, $email, $verified, $token, $password);

'ssbss'のbって、ドキュメントによるとblob型を与えるための指定になっています。
PHP: mysqli_stmt::bind_param - Manual

booleanだとしたら、stringのつもりで与えてもいい気がします。
（ふだんMySQL使っていないので確証は持てませんが。bは明らかに違います。）

投稿2022/06/08 09:56

編集2022/06/08 23:57

退会済みユーザー

総合スコア0

You

2022/06/08 10:08

$verifiedのbooleanのつもりです。

You

2022/06/09 08:34

参考になりました。ありがとうございます。

行動規範の内容に同意します

自己解決

PDOを使用した方法で解決できました。

db.phpのsetAttributeで、エラーを表示させることに成功したのが解決の要因になりました。

PHP
1// authController.php
2
3<?php
4session_start();
5require '../config/db.php';
6
7$errors = array();
8$username = '';
9$email = '';
10
11if (isset($_POST['signup-btn'])) {
12    $username = $_POST['username'];
13    $email = $_POST['email'];
14    $password_1 = $_POST['password_1'];
15    $password_2 = $_POST['password_2'];
16
17    if (empty($username)) {
18        $errors['username'] = "Username required";
19    }
20    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
21        $errors['email'] = "Email address invalid";
22    }
23    if (empty($email)) {
24        $errors['email'] = "Email required";
25    }
26    if (empty($password_1)) {
27        $errors['password'] = "Password required";
28    }
29    if ($password_1 !== $password_2) {
30        $errors['password'] = "The two password do not match";
31    }
32
33    try {
34        $db = getDb();
35        $query = $db->prepare("SELECT email FROM users WHERE email = ?");
36        $query->bindValue(1, $email);
37        $query->execute();
38
39        if ($query->rowCount() > 0) {
40            $errors['email'] = "Email address is already used";
41        }
42
43        if (count($errors) === 0) {
44            $password_1 = password_hash($password_1, PASSWORD_DEFAULT);
45            $token = bin2hex(random_bytes(32));
46            $verified = false;
47
48            $statement = $db->prepare("INSERT INTO users (username, email, verified, token, password) VALUES (:username, :email, :verified, :token, :password)");
49            $statement->bindValue(':username', $username);
50            $statement->bindValue(':email', $email);
51            $statement->bindValue(':verified', intval($verified));
52            $statement->bindValue(':token', $token);
53            $statement->bindValue(':password', $password_1);
54
55            if ($statement->execute()) {
56                $user_id = $db->$insert_id;
57                $_SESSION['id'] = $user_id;
58                $_SESSION['username'] = $username;
59                $_SESSION['email'] = $email;
60                $_SESSION['verified'] = $verified;
61
62                $_SESSION['message'] = "You are now logged in";
63                $_SESSION['alert-class'] = "alert-success";
64            } else {
65                $errors['db_error'] = "Database error: failed to register";
66            }
67            header('Location: index.php');
68        }
69    } catch (PDOException $e) {
70        die("Error: {$e->getMessage()}");
71    }
72}
73

PHP
1// db.php
2
3<?php
4
5function getDb(): PDO
6{
7    $dsn = 'mysql:dbname=*****;host=*****;charset=utf8mb4';
8    $user = '*****';
9    $password = '*****';
10
11    $db = new PDO($dsn, $user, $password);
12    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
13    return $db;
14}