如何将MySQL代码转换为PDO语句？

Question

我需要将第一个if语句改成PDO语句，但是我不确定该如何处理。请有经验的人帮忙一下？

当用户提交表单时，我想从数据库的用户表中提取他们的电子邮件地址到网站上的此页面，使用他们注册时分配给他们的编号$id。

$table = 'suggestions';
$id = (isset($_SESSION['u_id']) ? $_SESSION['u_id'] : null);

if ( NULL !== $id) {

  $sql = mysqli_query($conn, "SELECT email FROM users WHERE u_id='$id'");
  $fetch = mysqli_fetch_assoc($sql);
  $email = $fetch['email'];

}

$email;
$optionOne = '';
$optionTwo = '';
$suggestions = selectAll($table);


if (isset($_POST['new-suggestion'])) {
  global $conn;

  $id;
  $email;
  $optionOne = $_POST['optionOne'];
  $optionTwo = $_POST['optionTwo'];
  $sql = "INSERT INTO $table (user_id, email, option_1, option_2) VALUES (?, ?, ?, ?)";

  if (!empty($optionOne) && !empty($optionTwo)) {
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('ssss', $id, $email, $optionOne, $optionTwo);
    $stmt->execute();

  } else {
    echo "All options must be entered";
  }
}

- Alex0720

---

如何建立PDO连接已在手册中有所记录。请参考：https://www.php.net/manual/zh/pdo.construct.php - RiggsFolly

如果您使用了MySQLI_连接，则无法使用PDO命令。 - RiggsFolly

所以，如果你想将第一个查询改为PDO，你必须使用PDO连接进行连接，并更改所有的数据库访问代码以使用PDO。 - RiggsFolly

Answer 1

建立连接

首先，您需要将mysqli连接替换为PDO连接（或者至少在mysqli连接旁边添加PDO连接！）。

// Define database connection parameters
$db_host = "127.0.0.1";
$db_name = "name_of_database";
$db_user = "user_name";
$db_pass = "user_password";


// Create a connection to the MySQL database using PDO
$pdo = new pdo(
    "mysql:host={$db_host};dbname={$db_name}",
    $db_user,
    $db_pass,
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => FALSE
    ]
);

更新您的代码

使用mysqli和PDO的预处理语句

在将变量数据放入SQL查询时，几乎总是最好使用预处理语句。这不仅更安全（如果数据来自任何类型的用户生成输入），而且还使其更易于阅读，并且更容易多次使用不同的值运行。

mysqli的预处理查询：

$sql   = "SELECT column1, column2 FROM table WHERE column3 = ? AND column4 = ?";
$query = $mysqli->prepare($sql);
$query->bind_param("si", $string_condition, $int_condition);
$query->execute();
$query->store_result();
$query->bind_result($column1, $column2);
$query->fetch();

echo "Column1: {$column1}<br>";
echo "Column2: {$column2}";

使用PDO准备查询：

$sql   = "SELECT column1, column2 FROM table WHERE column3 = ? AND column4 = ?";
$query = $pdo->prepare($sql);
$query->execute([$string_condition, $int_condition]);
$row   = $query->fetchObject();
# $row = $query->fetch(); // Alternative to get indexed and/or associative array

echo "Column1: {$row->column1}<br>";
echo "Column2: {$row->column2}";

更新代码

// Using the NULL coalescing operator here is shorter than a ternary
$id = $_SESSION['u_id'] ?? NULL;

if($id) {
    $sql   = "SELECT email FROM users WHERE u_id = ?";
    $query = $pdo->prepare($sql);    // Prepare the query
    $query->execute([$id]);          // Bind the parameter and execute the query
    $email = $query->fetchColumn();  // Return the value from the database
}

// Putting "$email" on a line by itself does nothing for your code. The only
// thing it does is generate a "Notice" if it hasn't been defined earlier in
// the code. Best use:
//    - The ternary operator: $email = (isset($email)) ? $email : "";
//    - The NULL coalescing operator: $email = $email ?? "";
//    - OR initialize it earlier in code, before the first `if`, like: $email = "";
// N.B. Instead of "" you could use NULL or FALSE as well. Basically in this case 
//    anything that equates to BOOL(FALSE); so we can use them in `if` statements
//    so the following (2 commented lines and 1 uncommented) are effectively
//    interchangeable.
$email = $email ?? "";
# $email = $email ?? FALSE; 
# $email = $email ?? NULL;

// Presumably you will also want to change this function to PDO and prepared statements?
// Although it doesn't actually do anything in the code provided?
$suggestions = selectAll($table);  

// Same as with email, we're just going to use the NULL coalescing operator.
// Note: in this case you had used the third option from above - I've just
//   changed it so there is less bloat.
$optionOne     = $_POST['optionOne'] ?? "";
$optionTwo     = $_POST['optionTwo'] ?? "";
$newSuggestion = $_POST['new-suggestion'] ?? "";

// There's no point nesting `if` statements like this when there doesn't appear to be any
// additional code executed based on the out come of each statement? Just put it into one.
// We now don't need to use empty etc. because an empty, false, or null string all.
// equate to FALSE.
if($newSuggestion && $id && $email && $optionOne && $optionTwo) {
    // Not sure why you've made the the table name a variable UNLESS you have multiple tables
    // with exactly the same columns etc. and need to place in different ones at different
    // times. Which seems unlikely so I've just put the table name inline.
    $sql   = "INSERT INTO suggestions (user_id, email, option_1, option_2) VALUES (?, ?, ?, ?)";
    $query = $pdo->prepare($sql);
    $query->execute([$id, $email, $optionOne, $optionTwo]);
}
else{
    echo "All options must be entered";
}

没有注释

$id = $_SESSION['u_id'] ?? NULL;

if($id) {
    $sql   = "SELECT email FROM users WHERE u_id = ?";
    $query = $pdo->prepare($sql);
    $query->execute([$id]);
    $email = $query->fetchColumn();
}
$email       = $email ?? "";
$suggestions = selectAll($table);  

$optionOne     = $_POST['optionOne'] ?? "";
$optionTwo     = $_POST['optionTwo'] ?? "";
$newSuggestion = $_POST['new-suggestion'] ?? "";

if($newSuggestion && $id && $email && $optionOne && $optionTwo) {
    $sql   = "INSERT INTO suggestions (user_id, email, option_1, option_2) VALUES (?, ?, ?, ?)";
    $query = $pdo->prepare($sql);
    $query->execute([$id, $email, $optionOne, $optionTwo]);
}
else{
    echo "All options must be entered";
}

- Steven