PDO准备子查询 [英] PDO preparing with subqueries

问题描述

我对PDO还是很陌生，并且正在尝试将我的MySQLi程序结构化的php代码更改为面向对象的PDO结构.我只是在学习准备，执行，bindParam/bindValue之类的方法，以达到一定程度的成功.

I am quite new to PDO, and am trying to change my MySQLi procedurally structured php code to an Object Oriented PDO structure. I am just learning about preparing, executing, bindParam/bindValue and the like, to a degree of success.

我的问题是，当用户提交的值位于该查询的子查询中时，该如何准备查询?

My question is how do I prepare a query when the user submitted value is in a subquery of that query?

我有一个变量用作php中的子查询(其中$ playerOne，$ playerTwo是用户提交的值).

I have a variable used as a subquery in php (where $playerOne, $playerTwo are user submitted values).

$sqlPlayerOne = "(SELECT * FROM players WHERE Player_name = $playerOne)";
$sqlPlayerTwo = "(SELECT * FROM players WHERE Player_name = $playerTwo)";

此操作将获取这些玩家的所有记录.然后，举例来说，我可以比较他们之间进行了哪些比赛，例如

This it to get all records for these players. I can then, as an example, compare what games they played against each other e.g.

$sqlWith = "SELECT * FROM $sqlPlayerOne s1  
WHERE EXISTS (SELECT * FROM $sqlPlayerTwo s2 WHERE s1.Team_name = s2.Opposing_team)

注意:SELECT *仅用于使其在此处更具可读性.

Note: SELECT * is just used to make it more readable here.

这足以完成$pdoWith = $db->prepare($sqlWith)吗?还是我应该先准备$sqlPlayerOne，因为这具有用户提交的值?

Is it enough to do $pdoWith = $db->prepare($sqlWith) or should I be preparing the $sqlPlayerOne first, as this has the user submitted value?

我意识到我可以将子查询复制/粘贴到需要它的每个主查询中，但是如果不需要的话，我宁愿不这样做.

I realise I could just copy/paste the subquery inside every single main query that needed it, but if I don't have to I'd rather not.

很抱歉，缺乏清晰度.在更改之前，这是我代码的一部分，因为我不确定如何更改它.看来我只需要像@ J-C FOREST指出的那样做即可:

Sorry for the lack of clarity. This was a section of my code before I changed it, as i wasn't sure how I would have to change it. It seems I will just have to do it similar to how @J-C FOREST pointed out:

 $dsn = "mysql:host=localhost;dbname=database";
 $username = "user";
 $password = "pass";
 $db = new PDO($dsn, $username, $password);

 $stmt = $db->prepare("SELECT * FROM (SELECT * FROM players WHERE Player_name = :playerone) 
 s1 WHERE EXISTS (SELECT * FROM (SELECT * FROM players WHERE Player_name = :playertwo) s2 
 WHERE s1.Team_name = s2.Opposing_team)");

 $stmt->bindValue(':playerone', $playerOne);
 $stmt->bindValue(':playertwo, $playerTwo);
 $stmt->execute();

推荐答案

您需要将$ playerOne，$ playerTwo作为参数绑定到准备好的语句. http://php.net/manual/en/mysqli.prepare.php

You need to bind $playerOne, $playerTwo to your prepared statement as parameters. http://php.net/manual/en/mysqli.prepare.php

$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* create a prepared statement */
$stmt = $mysqli->prepare("SELECT * FROM (SELECT * FROM players WHERE Player_name = ?) s1  
WHERE EXISTS (SELECT * FROM (SELECT * FROM players WHERE Player_name = ?) s2 WHERE s1.Team_name = s2.Opposing_team)")

    /* bind parameters for markers */
    $stmt->bind_param("ss", $playerOne, $playerTwo);

    /* execute query */
    $stmt->execute();

这篇关于PDO准备子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！