如何将 MySQL 代码转换为 PDO 语句? [英] How to convert MySQL code into PDO statement?

问题描述

我需要将第一个 if 语句更改为 PDO 语句，但我不确定如何去做.请问有人可以帮忙吗?

I need to change the first if statement into a PDO statement but I'm not sure how to go about it. Please can someone help?

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

When users submit a form I want their email address to be pulled from the users table on the database into this page on the website, using the numbered $id they are assigned when they sign up.

$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";
  }
}

推荐答案

建立联系

首先，您需要将 mysqli 连接替换为 PDO 连接(或至少将 PDO 连接与 mysqli 一！)

Make a connection

Firstly you need to replace your mysqli connection with a PDO one (or at least add the PDO connection alongside the mysqli one!).

// 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 查询时，使用准备好的语句几乎总是更好.它不仅更安全(如果数据来自任何类型的用户生成的输入)，而且更易于阅读，并且更容易使用不同的值多次运行.

Updating your code

Prepared statements with mysqli and PDO

It's almost always better to use prepared statements when putting variable data into an SQL query. Not only is it safer (if the data comes from any sort of user generated input) but it also makes it easier to read, and easier to run multiple times with different values.

使用 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";
}

这篇关于如何将 MySQL 代码转换为 PDO 语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！