PDOで2つのテーブルを結合しMySQLに接続したいがSELECT文が実行できない

Question

いつもお世話になっております。
MySQLに接続し2つのテーブルを結合し、SELECT文を実行したいのですがうまく表示されません。

MariaDB [paysub]> select * from customers;
+--------------------+-----------+-----------------+-------------------+---------------------+
| id | last_name | first_name | email | created_at |
+--------------------+-----------+-----------------+-------------------+---------------------+
| cus_G50Aw9QPAn5VjF | 田中 | かずお | [e-mail] | 2019-10-29 17:34:27 |
| cus_G50esjtjdXCVSX | 吉田 | たろう | [e-mail] | 2019-10-29 18:04:45 |
| cus_G50ImEgofoHIjr | 山田 | こうすけ | [e-mail] | 2019-10-29 17:42:48 |
| cus_G50VgUaw2V2CZo | 藤田 | ニコル | [e-mail] | 2019-10-29 17:56:22 |
+--------------------+-----------+-----------------+-------------------+---------------------+

transactions
MariaDB [paysub]> select * from transactions;
+--------------------+--------------------+-----------------+--------+----------+--------+---------------------+
| id | customer_id | subscription | amount | currency | status | create_at |
+--------------------+--------------------+-----------------+--------+----------+--------+---------------------+
| sub_G50eORaCOnTnJB | cus_G50esjtjdXCVSX | monthly | 15000 | jpy | active | 2019-10-29 18:04:45 |
| sub_G50IlV2zUFSPNh | cus_G50ImEgofoHIjr | monthly | 15000 | jpy | active | 2019-10-29 17:42:48 |
| sub_G50WH4OU89NMip | cus_G50VgUaw2V2CZo | monthly | 15000 | jpy | active | 2019-10-29 17:56:22 |
+--------------------+--------------------+-----------------+--------+----------+--------+---------------------+

$con = new PDO('mysql:host=localhost;dbname=paysub','root','');
  $email      = "[e-mail]";

  $user = $con->query("SELECT customers.email AS email, transactions.id AS id FROM customers INNER JOIN transactions ON customers.id = transactions.customer_id WHERE customers.email = $email");

  $user = $user->fetch(PDO::FETCH_OBJ);

  echo $user->id;
コード

customersテーブルと、transactionテーブルをinner joinで結合し、
emailと紐付いているtransactionテーブル内のidを取り出したいのですが、
上記のコードだとエラーが出てしまいます。

お手数おかけしますが、お分かりになる方お力添えください。
よろしくお願いいたします。

Answer 1

customers.email = $email

ここはNG

最低でも

customers.email = '$email'

としないと文字列を比較できない

普通はpdoのprepareで処理します

try{
  $dsn = 'mysql:host=localhost; dbname=hoge;charset=utf8;';
  $user = 'root';
  $password = 'hogehoge';
  $con = new PDO($dsn, $user,$password);
  $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
  $sql="SELECT customers.email AS email, transactions.id AS id FROM customers INNER JOIN transactions ON customers.id = transactions.customer_id WHERE customers.email = ?";
  $user = $pdo->prepare($sql);
  $user->execute([$email]);
  $rows=$stmt->fetchAll(PDO::FETCH_ASSOC);
  print_r($rows);
}catch(PDOException $e){
  die($e->getMessage());
}

リレーション関連

create table customers(
id varchar(30) primary key not null,
last_name varchar(10),
first_name varchar(10),
email varchar(50),
created_at datetime);
insert into customers values
('cus_G50Aw9QPAn5VjF','田中','かずお','[e-mail]','2019-10-29 17:34:27'),
('cus_G50esjtjdXCVSX','吉田','たろう','[e-mail]','2019-10-29 18:04:45'),
('cus_G50ImEgofoHIjr','山田','こうすけ','[e-mail]','2019-10-29 17:42:48'),
('cus_G50VgUaw2V2CZo','藤田','ニコル','[e-mail]','2019-10-29 17:56:22');

create table transactions(
id varchar(30) primary key not null,
customer_id varchar(30),
subscription varchar(10),
amount int,
currentcy varchar(10),
status varchar(10),
create_at datetime);
insert into transactions values
('sub_G50eORaCOnTnJB','cus_G50esjtjdXCVSX','monthly','15000','jpy','active','2019-10-29 18:04:45'),
('sub_G50IlV2zUFSPNh','cus_G50ImEgofoHIjr','monthly','15000','jpy','active','2019-10-29 17:42:48'),
('sub_G50WH4OU89NMip','cus_G50VgUaw2V2CZo','monthly','15000','jpy','active','2019-10-29 17:56:22');

• [e-mail]

SELECT customers.email AS email, transactions.id AS id FROM customers INNER JOIN transactions ON customers.id = transactions.customer_id WHERE customers.email = '[e-mail]';

※結果：ヒットなし

• [e-mail]

SELECT customers.email AS email, transactions.id AS id FROM customers INNER JOIN transactions ON customers.id = transactions.customer_id WHERE customers.email = '[e-mail]';

※結果

email	id
[e-mail]	sub_G50eORaCOnTnJB

Comments

[terapro]

大変丁寧に回答頂き誠にありがとうございました！
customers.email = '$email'　で動きました！

prepareはinsertとupdateでしか使っていなかったので
今後の参考にさせていただきます。

初歩的なミスにも関わらず、大量の補足をいただき大変参考になりました。
ありがとうございました！！